Need Advice on Table Structure for Part Kits

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

re: Need Advice on Table Structure for Part Kits

-----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:[color=blue]
> 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.[/color]

re: Need Advice on Table Structure for Part Kits

"ChadDiesel" <shaqattack1992-newsgroups@yahoo.com> wrote in
news:8yUbe.290$V01.16@newsread1.news.atl.earthlink .net:
[color=blue]
> 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
> --------------------------------------------------------------[/color]
-[color=blue]
> --------------------------------
> 11111111--------------------393.67----AAA1000----------[/color]
290-----[color=blue]
> ---------4/29/2005
> 22222222--------------------415.00----ABB2000----------[/color]
310-----[color=blue]
> ---------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
>[/color]
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.

re: Need Advice on Table Structure for Part Kits

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

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

"ChadDiesel" <shaqattack1992-newsgroups@yahoo.com> wrote in message
news:8yUbe.290$V01.16@newsread1.news.atl.earthlink .net...[color=blue]
> 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
>
>[/color]