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 3 3161
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQnAa3YechKq OuFEgEQL8bQCeIN sJziJD4GRTCKSXe iWkGUM826gAnj4U
ovsP24X0mA2MJie 536kZsw85
=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.
"ChadDiesel " <sh************ ***********@yah oo.com> wrote in
news:8y******** ******@newsread 1.news.atl.eart hlink.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.
Chad,
It's a BOM! No, not a BOMB! a BOM--Bill of Materials. MGFoster got it
right.
--
Alan Webb kn*******@SPAMh otmail.com
"It's not IT, it's IS"
"ChadDiesel " <sh************ ***********@yah oo.com> wrote in message
news:8y******** ******@newsread 1.news.atl.eart hlink.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 a field or two.
It would be great if there were an application out there that could either:
(a) sync all MDB designs (and/or data) designated to match one I've added
some fields/tables to
OR
(b) go into all designated MDBs and create new...
|
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 proposing a single column table with a field name
called vehicle_type and this will contain the vehicle type.
Sot it will be
|
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
would then only involve one more byte per record. This version number
list needs to be the VERY first item in the table header. The lowest
ordered byte of the first word of the version number list contains the
size of the words in bytes. The...
|
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 command (execute non-query)
in a web service. But now, we're not sure how to now update the table in
the ds. My thinking is requery that 1 table, grab my dataset from the
session & do sda.Fill(myDS, "tblEmployee") which will keep my existing ds,...
|
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 sub-categories.
Each sub category consists of individual threads.
Wich each has the possibility to be commented.
| |
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
Public First As String
Public Last As String
End Structure
|
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 that i have just made...what's wrong?
#include<stdio.h>
#include<conio.h>
#include<string.h>
|
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 (i.e. the
get_hash function) is borrowed from various snippets I found on the
net. Thee free function could probably need some love. I have been
thinking about having a second linked list of all entries so that the
cost of freeing is in proportion to...
|
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 the
sequence nos and it should be such that i can access it through the
structure .plz help me .
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |