I work at a music department at a high school and have an Access file that I created for our yearly outfit order. Each class has 2 tables - women and mens. I am trying to figure out if there is a way that access can count my total number of sizes for me. Example - I have 6 different tables for womens dresses, I would like to get a report on how many size 2 dresses there are. Can anyone help me do this?

Thanks
Wendy

You definetly should be able to retrieve this info but it sounds like you have you tables setup incorreclty.

How would you suggest that I set up my tables?

Its hard to say because I don't know what info you are storing.

The following presupposes that your school has items of clothing that are assigned (loaned/rented/sold) to students depending on which classes the student is taking. You need to track the inventory you have on-hand, the sizes of students in the upcoming class (by year presumably), and determine how many items of each size you need to order...?...if this is correct, then your task is non-trivial and you probably need to develop an inventory tracking database.


Instead of several tables for dress sizes think one table for 'outfits', another for specific items of 'clothing', another for 'classes', another for 'students', and junction tables to link students to classes, and students to clothing:

Eg. tblOutfit
OutfitID(Autonumber; PK)
OutfitType (Male or Female)
OutfitName
etc

tblClasses
ClassID (Autonumber; PK)
ClassName
ClassStartDate
ClassEndDate
ClassMaximumStudents
ClassTeacher
etc

tblClassOutfit
ClassOutfitID (Autonumber; PK)
OutfitID (FK)
ClassID (FK)

tblStudent
StudentID (Autonumber; PK)
StudentSurname
StudentFirstName
etc

tblStudentClasses
StudentClassID (Autonumber; PK)
StudentID (FK)
ClassID (FK)

tblClothingItem
ClothingItemID (Autonumber; PK)
OutfitID (FK)
ClothingItemSize


tblStudentClothingTransactions
StudentClothingID (Autonumber; PK)
ClothingItemID (FK)
StudentID (FK)
StudentClothingAssignmentDate
TransactionQuantity


FK stands for Foreign Key, and PK stands for Primary Key.

Each PK links to the same-named Foreign Key in the relationships view of your db. The junction tables are necessary because potentially the same student could be taking more than one class, and the same class probably has more than one student. Likewise, a student might be assigned more than one item of clothing, and one item of clothing might be assigned to more than one student (over time). An outfit can be comprised of several items of clothing, or at least several sizes of clothing, and one item of clothing might be common to more than one outfit. You also need to have a table to track purchases/sales etc for clothing by size.

Do a search on the topic of database table normalization and do some reading on the topic to get a feel for how tables should be set-up depending on the real world rules you are faced with. Secondly, do a search on inventory databases and learn how to go about tracking inventory items. Essentially it boils down to summing up a series of 'transactions' (sum of purchases - sum of sales = stock on hand). In the example above, use positive numbers in the transaction quantity field to represent purchases/returns, and negative numbers to represent sales/loans etc. You might also need a table for stocktakes etc.

As Keith mentioned, it's very hard to advise you without a better understanding of exactly what you want to store and how you plan to use it. Only you know the real-world rules of the road that will dictate what your table structure should be. The above is a very quick and dirty response that is probably going to need a lot of refinement to suit your needs.

Hope this helps.