Need Advice on Table Structure for Part Kits

ChadDiesel

Hello everyone. I 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 assume that this is a one to many
relationship, but maybe not. The component field in the main table can
repeat, so there might be several different contracts that each need the
same kit. I can't make the component field a primary key because it
repeats.

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. Will I need a third table
for linking?

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

Nov 13 '05 #1

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd have one table:

Orders:
Contract_Number,
Price,
Component,
Location,
Ship_date

And a 2nd table that holds the parts that make up a component:

ComponentParts:
Component, -- a reference to the Order table Component
Part_no,
Part_count -- the Quantity, part_count is more descriptive

Then in the report have a subreport that holds the ComponentParts. Link
on the Component value to the main report. The main report would have
the Orders info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAa3YechKqOuFEgEQL8bQCeINsJziJD4GRTCKSXeiWkGU M826gAnj4U
ovsP24X0mA2MJie536kZsw85
=OxtQ
-----END PGP SIGNATURE-----

ChadDiesel wrote:

Hello everyone. I 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 assume that this is a one to many
relationship, but maybe not. The component field in the main table can
repeat, so there might be several different contracts that each need the
same kit. I can't make the component field a primary key because it
repeats.

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. Will I need a third table
for linking?

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.

Nov 13 '05 #2

Bob Quintal

"ChadDiesel" <sh***********************@yahoo.com> wrote in
news:8y**************@newsread1.news.atl.earthlink .net:

Hello everyone. I 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

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

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

The industry standard structure for a Bill of material is two
tables as follows

tblAssy
-------
ParentItemNo (part 1 of Primary Key
ChildItemNo part 2 of primary Key)
Qty

Put all of your kits into the one table.
tblItems
--------
itemNo (Primary Key)
Description
UnitOfMeasure
Cost

You may have supplemental fieldss in either table

Unless you have parts that are Components, which can be a
complex structure of the same relationship calling itself
recursively, this is all you need.

To this you need to to add your contract table for the week, or
make it a permanent table with an additional column to indicate
OrderFilled.
Now create a details report with the information from the
tblAssy and the tblParts, with the join between ChildItemNo and
ItemNo. You need to include ParentItemNo, but you can set its
visible property to No. This will become a sub-report in the
next report that you will create.

This is the main report, and contains the information from your
Excel spreadsheet. Underneath the inforemation insert the sub-
report and set the Parent link fields to Component and the child
link fields to ParentItemNo. You can set a page break after the
sub-report so the next record in your Contract table starts on a
new page.
--
Bob Quintal

PA is y I've altered my email address.

Nov 13 '05 #3

Alan Webb

Chad,
It's a BOM! No, not a BOMB! a BOM--Bill of Materials. MGFoster got it
right.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"ChadDiesel" <sh***********************@yahoo.com> wrote in message
news:8y**************@newsread1.news.atl.earthlink .net...

Hello everyone. I 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 assume that this is a one to many
relationship, but maybe not. The component field in the main table can
repeat, so there might be several different contracts that each need the
same kit. I can't make the component field a primary key because it
repeats.

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. Will I need a
third table for linking?

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

Nov 13 '05 #4