473,226 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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
Nov 13 '05 #1
3 3134
-----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
"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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

39
by: Scotter | last post by:
Okay I think my title line was worded misleadingly. So here goes again. I've got quite 20 identical MDB files running on an IIS5 server. From time to time I need to go into various tables and add...
36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
2
by: Dennis Gearon | last post by:
IPU, in place updates. -------------------------- 1/ Put a version numbers on each record represented by a byte/word sized number from a three version number list kept by the table header. This...
1
by: Jason Shohet | last post by:
We have an app that queries 5 tables into a dataset. Now, lets say 1 of those tables is about to change due to user input on a webpage. Right now, we are manually updating the db with an update...
6
by: frizzle | last post by:
Hi there, I'm going to build a simple forum in mySQL. I've thought about it for a while now, but still can't figure it out completely: If i have say 5 main categories, One has 5...
0
by: Miguel Dias Moura | last post by:
Hello, I am working on an Asp.Net 2.0 / SQL 2005 web site. I am using profile to save the users info on the database. For example, I have the following structure: Public Structure Name...
9
by: Dadio | last post by:
Hi! I have to take some strings from a file and put them in a record... The various strings in the file are written on this way: string1|string2|string3|string4|string5| This is the program...
21
by: Johan Tibell | last post by:
I would be grateful if someone had a minute or two to review my hash table implementation. It's not yet commented but hopefully it's short and idiomatic enough to be readable. Some of the code...
8
by: skumar434 | last post by:
i need to store the data from a data base in to structure .............the problem is like this ....suppose there is a data base which stores the sequence no and item type etc ...but i need only...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.