"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.
