Beginner Needs Help With Setting Up Part Tables

chaddiesel

Registered User.
Local time
Today, 04:54
Joined
Mar 30, 2005
Messages
24
Hello everyone. I'm pretty new to access and need some advice on table structure for a new project I've been given.

One of our customers sends us an Excel spreadsheet each week containing their order. Currently, someone formats the spreadsheet, prints it out, and manually picks out the products we need to ship. I want to import this into an Access table. Basically, the sheet with some data examples looks something like this:

CONTRACT_NUMBER----PRICE----COMPONENT----LOCATION----SHIP_DATE
----------------------------------------------------------------
11111111-----------393.67---AAA1000------290---------4/29/2005
22222222-----------415.00---ABB2000------310---------5/1/2005


There are usually 30-40 records on this list. The COMPONENT field is actually a part kit. We have tables in a Excel sheet that list the parts in the kit as well as the quantity. For example.

Component: AAA1000

will contain

Part#---------Quantity
----------------------
123-----------2
456-----------17
789-----------11
111-----------57
908-----------36

--------------------------------------

Component: ABB2000

will contain

Part#---------Quantity
----------------------
123-----------80
777-----------29
345-----------3
906-----------14



What I basically want to do is run a report based on a query where each page has a contract number (one for each record) with matching ship date and location information and a list of parts needed on that kit.

For example, the first page would be:

--------------------------------------------

Contract #:---11111111
Location:-----310
Ship Date:----5/1/2005
Kit-----------AAA1000

Part#---------Quantity
----------------------
123-----------2
456-----------17
789-----------11
111-----------57
908-----------36

--------------------------------------------

and the second page would be:

--------------------------------------------

Contract #:---22222222
Location:-----290
Ship Date:----4/29/2005
Kit-----------ABB2000

Part#---------Quantity
----------------------
123-----------80
777-----------29
345-----------3
906-----------14

--------------------------------------------

I'm a little confused by the COMPONENT field linking to more than one part. In the little experience I've had with Access, one record was always linked to just one other record in another table. Also, I want the Contract Number, Location, Ship Date, and Kit # show up once at the top and the complete kit show up below.

I would appreciate any suggestions as to hot to set up my tables. We have about 30 different kits. Should I have a separate table for each or a huge table? Also how will I make relationships between the main table and the part table/tables? Am I going to have to have the kit # as a field in the part table and list it for every part/quantity record? I want to just list the description information (location, ship date, etc...) only once while listing all the parts and quantities for the kit.

I've only been using Access for a short time, so this might be a really simple question. I just need to be pointed in the right direction, so I can set the system up the right way. Any information would be greatly appreciated.

Thank You,

Chad
 
Search this forum for keyword "Inventory" and do some reading. Then perhaps you can find some answers.

Another topic that probably would be good for you would be "Normalization" and there, a Google-Search would only turn up about a gazillion hits. Pick a few treatises from reputable-sounding universities. MIT, USC, Carleton, UGa, UTex, etc. or... the University of Southern North Dakota at Hoople might provide some PDQ answers. (Sorry, inside joke on that last...)

You will also need to set up several tables in parent/child relationships, so read up on relationships and many-to-one (or one-to-many) relationships. Access Help is good for the relationship descriptions.

You will need to consider what a table really means in this context. A table is a business entity that is part of your business model.

Therefore, you need a table that identifies KITS.
A child of that table might identify PARTS that appear in any kits
A linking table might provide lists of Parts that appear in specific kits.
Now, a really adventuresome designer would quickly recognize that if you can sell a part OR a whole kit, a kit has to be a PART and have its own part number. Which means the linking table links to itself.

You will have a Purchase Order table.
A purchase order will have a child table to identify line items (which might include either kits or parts...)

I don't want to take this too far because all I'll do is confuse you. Do the reading first. There is no substitute for it.

We have about 30 different kits. Should I have a separate table for each

One answer I'll give you straight up BEFORE you do any reading: You should not have separate tables per kit. You can have a single table with a field in that table identifying the kit.
 

Users who are viewing this thread

Back
Top Bottom