J
JABarrett
Hi, I'll try to describe this problem as best I can to give you an
understanding of what i'm looking for.
I have created an access database for my company that will be used to keep
stock control of the company uniforms. I have created 4 tables and 4 forms (1
per table) for the data. Three of the tables simply have text fields and as
such i have no problems with. The problem i am having is with the table/form
that is used to track the items of clothing that a member of staff has. The
Table/Form has main fields for tracking such as Staff Name, Department, Etc.
- it also has Text fields for the quantities of the particular items of
clothing (e.g. Trousers, Blouses, Shirts) which are laid out one below the
other and to the right of each item of clothing it has a row of tick boxes
(one per size of the particular item) which each correspond to their own
individual Yes/No fields in the table. For example there is a text field
called "Shirts Quantity" in the table followed by a Yes/No field called
"Shirts 15" which is followed by another Yes/No field called "Shirts 15-5".
What I am looking to be able to do is create a Form/Query/Report (any of the
previous options) which will be able to create a tally from the data on that
table. I want it to be able to look at the "shirts quantity" field first to
determine how many of the item it is looking at - then it needs to look at
the Yes/No fields to determine which size that person has. It then needs to
be able to take this data, and totalled with all the records in that table,
write the total amounts of each item and size in to a seperate table which
contains only fields relating to the size and in Text format - E.G. "Shirts
15" followed by "Shirts 15-5" and so on.
If i may demonstrate:
Existing Form
----------------
Name: Joe Bloggs
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 3 ] : [ ] 15 [ ] 15.5 [*] 16 [ ] 16.5
Trousers : [ 3 ] : [ ] 32 Reg [ ] 34 Reg [*] 36 Reg
-------------------------------------------------------------------
Name: John Jiggins
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 2 ] : [ ] 15 [*] 15.5 [ ] 16 [ ] 16.5
Trousers : [ 2 ] : [*] 32 Reg [ ] 34 Reg [ ] 36 Reg
-------------------------------------------------------------------
Name: Jim Johnson
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 4 ] : [ ] 15 [ ] 15.5 [ ] 16 [*] 16.5
Trousers : [ 4 ] : [ ] 32 Reg [ ] 34 Reg [*] 36 Reg
Using that data the system should be able to tally up in to a new Table as
follows:
Shirts 15 :
Shirts 15-5 : 2
Shirts 16 : 3
Shirts 16-5 : 4
Trousers 32 Reg : 2
Trousers 34 Reg :
Trousers 36 Reg : 7
I hope somebody will be able to help me with this problem, wiether it be via
straight VB Code in a form or through creation of a module - I'm willing to
look in to any method that may allow such a task.
Thank you to anybody who has any ideas.
understanding of what i'm looking for.
I have created an access database for my company that will be used to keep
stock control of the company uniforms. I have created 4 tables and 4 forms (1
per table) for the data. Three of the tables simply have text fields and as
such i have no problems with. The problem i am having is with the table/form
that is used to track the items of clothing that a member of staff has. The
Table/Form has main fields for tracking such as Staff Name, Department, Etc.
- it also has Text fields for the quantities of the particular items of
clothing (e.g. Trousers, Blouses, Shirts) which are laid out one below the
other and to the right of each item of clothing it has a row of tick boxes
(one per size of the particular item) which each correspond to their own
individual Yes/No fields in the table. For example there is a text field
called "Shirts Quantity" in the table followed by a Yes/No field called
"Shirts 15" which is followed by another Yes/No field called "Shirts 15-5".
What I am looking to be able to do is create a Form/Query/Report (any of the
previous options) which will be able to create a tally from the data on that
table. I want it to be able to look at the "shirts quantity" field first to
determine how many of the item it is looking at - then it needs to look at
the Yes/No fields to determine which size that person has. It then needs to
be able to take this data, and totalled with all the records in that table,
write the total amounts of each item and size in to a seperate table which
contains only fields relating to the size and in Text format - E.G. "Shirts
15" followed by "Shirts 15-5" and so on.
If i may demonstrate:
Existing Form
----------------
Name: Joe Bloggs
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 3 ] : [ ] 15 [ ] 15.5 [*] 16 [ ] 16.5
Trousers : [ 3 ] : [ ] 32 Reg [ ] 34 Reg [*] 36 Reg
-------------------------------------------------------------------
Name: John Jiggins
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 2 ] : [ ] 15 [*] 15.5 [ ] 16 [ ] 16.5
Trousers : [ 2 ] : [*] 32 Reg [ ] 34 Reg [ ] 36 Reg
-------------------------------------------------------------------
Name: Jim Johnson
Department: Maintenance
ITEM QUANTITY SIZE
Shirts : [ 4 ] : [ ] 15 [ ] 15.5 [ ] 16 [*] 16.5
Trousers : [ 4 ] : [ ] 32 Reg [ ] 34 Reg [*] 36 Reg
Using that data the system should be able to tally up in to a new Table as
follows:
Shirts 15 :
Shirts 15-5 : 2
Shirts 16 : 3
Shirts 16-5 : 4
Trousers 32 Reg : 2
Trousers 34 Reg :
Trousers 36 Reg : 7
I hope somebody will be able to help me with this problem, wiether it be via
straight VB Code in a form or through creation of a module - I'm willing to
look in to any method that may allow such a task.
Thank you to anybody who has any ideas.