Complex Tally Problem

[Guest]

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.

 

[Guest]

I'd like to suggest to you to break up your problem in to smaller pieces. If
in these smaller pieces your having problems doing something specific repost.
This is how I'd design it. I'd have a style table and entry form where they
can choose there size scale(another table probably with form). for a new
style they enter the style name and size scale. You create a inventory table
with style (shirts 15) and the size (05) would be the PK. With a quantity
field and other info. All the stuff you want should be able to be done if it
is set up properly.

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.

 

[Guest]

I'll give things a go trying to break them down in to smaller pieces but I
have to try to keep the 'employee allocation' form design as close to the
current paper based system as possible. If I have any luck I will say what I
did - if not then I will state as far as I manage to get.

I'd like to suggest to you to break up your problem in to smaller pieces. If
in these smaller pieces your having problems doing something specific repost.
This is how I'd design it. I'd have a style table and entry form where they
can choose there size scale(another table probably with form). for a new
style they enter the style name and size scale. You create a inventory table
with style (shirts 15) and the size (05) would be the PK. With a quantity
field and other info. All the stuff you want should be able to be done if it
is set up properly.

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.

 

[John Nurick]

I'll give things a go trying to break them down in to smaller pieces but I
have to try to keep the 'employee allocation' form design as close to the
current paper based system as possible. If I have any luck I will say what I
did - if not then I will state as far as I manage to get.

It's seldom a good idea to let existing paper forms dictate the
structure of a database. The great thing about relational databases is
that they let you separate the data store from the user interface so you
can use a data structure that suits the data, while the forms and
reports can if necessary retain familiar styles.

Tables are for storing data, forms for displaying it. Any time you find
yourself writing "table/form" you are probably going the wrong way.

So the data structure will probably be something like this (* means the
field is, or is part of, the table's primary key):

tblEmployees
EmployeeID*
LastName
FirstName
other data about the employee
(ideally this table is linked from elsewhere in the company's system so
it's consistent with other corporate data)

tblGarmentTypes
GarmentTypeID*
GarmentName (e.g. "Shirt", "Blouse", "Silk Handkerchief for
President")
Description (e.g. "Blue cotton/viscose")
maybe other fields

tblGarmentSizes
GarmentTypeID* - foreign key into tblGarments
GarmentSize*
(this table has one record for every size in which each garment is
available)

tblEmployeesGarments
EmployeeID* - FK into tblEmployees
GarmentTypeID* - FK into tblGarments
GarmentSize* - FK into tblGarmentSizes
Quantity

With a structure like this all the reporting and tallying is a doddle.
Replicating the paper forms is somewhat more difficult but it's always
possible to achieve an effective user interface.