Help with table design (1 Viewer)

[Dugantrain]

I have a database that I'm having a rough time with setting up the tables. The database is based off of a spreadsheet which has now been determined antiquated. Here's what is going on:
25 techs go out daily with a kit full of parts. When they get to a site, they use certain parts from the kit (there can be more than one of each part in a kit). They may also pick up parts and add it to the kit, but will not go over a pre-determined maximum. Each part type has a serial # which is unique only to that part (again, there can be several serial #'s for the same part type, i.e. if you have three printers in your kit, then you'll have three serial numbers). When a tech leaves a part at a site, a serial # needs to be deleted from the database; when he picks up a part, one needs to be added.
I have a table with tech information (25 records, unchanging, this was easy). I have a table for Parts (Part Type, Recommended # in Kit, Description). I still need to set up tables so that I can get a running summary of:
*How many of each part type was added to the kit that day
*How many of each part type was subtracted that day
*How many of each part type is currently in the kit
*How many of each part type the tech is short for the maximum.
*Serial numbers for each part type
Please, please Help!

 

[dynamictiger]

Try this approach. Group each related item onto one piece of paper. For example each Part would have a part number, a description, a cost. Each Kit would have a KitID, and a kit description. Each Kit could contain multiple parts to a preset maximum, etc.

 

[neileg]

Not all tables

You have two tables, one for techs, one for parts descriptions. These are going to be relatively static, except when you add or remove techs or part types.

I think you need a third table that has fields for:
Serial number
Part type (linked to description table)
Tech (linked to Tech table)
Date issued
Date used

You will add entries to this table as you receive parts into stock. The tech field will be null until the part is issued to a tech at which point you need to enter the tech and the date issued. When the tech uses the part, enter the date used.

The info that you want will then be derived from these tables by query, e.g.:

*How many of each part type was added to the kit that day
Construct a query that contains the part type, the tech and the date issued where this date is the day you want. Summarise this query in a report sorting by tech and part type.

You'll find it quite easy, once you forget how spreadsheets work. The problem with spreadsheets is that the way the data is held is closely linked to how it is displayed. In a database there is very little such linkage.