Help needed with database design

Hi

Heres hoping someone can help me with a problem.

I need to design a small database to hold stock parts.
This part is well within my abilities and is quite simple.
eg
Part_no
Location
description
Price

etc.

The problem I have is that I need to be able to create some master parts 
which will make a kit composed of individual parts.

The users need to be able to choose a master part which will return a list 
of the component parts and details such as price etc. On the same order the 
individual parts may also be ordered in addition to the master part.
Also some of the individual parts will appear in more than one master part 
group.

Any ideas on how to design tha tables to make this easy.

Many thanks in advance.

Brian 


0
B
11/27/2007 12:34:16 PM
access 16762 articles. 3 followers. Follow

3 Replies
619 Views

Similar Articles

[PageSpeed] 40

"B Garner" <brian@leach-ltd.co.deleteme.uk> wrote in message
news:c9U2j.6442$B97.3070@newsfe7-win.ntli.net...
> Hi
>
> Heres hoping someone can help me with a problem.
>
> I need to design a small database to hold stock parts.
> This part is well within my abilities and is quite simple.
> eg
> Part_no
> Location
> description
> Price
>
> etc.
>
> The problem I have is that I need to be able to create some master parts
> which will make a kit composed of individual parts.
>
> The users need to be able to choose a master part which will return a list
> of the component parts and details such as price etc. On the same order
the
> individual parts may also be ordered in addition to the master part.
> Also some of the individual parts will appear in more than one master part
> group.
>
> Any ideas on how to design tha tables to make this easy.
>
> Many thanks in advance.
>
> Brian
>
>
You are describing a case of the Bill of Materials problem.

One way to do what you describe is to build a second table,  Components,
with fields

Master_Part_No
Component_Part_No
Quantity

To indicate that the part "Wheel" occurs 4 times in the master part "Auto",
you would put the part_no for auto in the Master_Part_No field,  the part_no
for Wheel in the Component_Part_no field,  and the number 4 in the quantity
field.

(Assuming that all four wheels are the same).

To create the lists you describe you would join the master parts table twice
to this table.  In one join,  you would use Master_parts_no as a foreign key
to Part_no.  In the other join, you would use Component_part_no as a foreign
key.



0
David
11/27/2007 1:16:46 PM
I would do this in 2 tables ( although there are valid reasons to consider 
doing it in 3 tables ).

The first being the "product table" which would contain both your individual 
parts and your master parts.

The second table is a "part to master part" table.  This table would contain 
fields  Part_no & Master_Part_no, the values for both fields would come from 
the Part_no field from the "product table."  You would also need to add 
fields for any other information that is specific to the way the part and 
master part interact.. such as quantity. Then for each part you need to add a 
record ( consisting of its part_no, the part_no of the master part + whatever 
other fields you've included ) for each master part it belongs to.  Thus, if 
an individual part is included in 4 master parts there would be 4 records for 
it in this table.  

I would also add a record for each part with itself as both the master and 
individual part.  This would allow you to ( probably ) use the same process 
regardless of a product being an individual part or master part.

"B Garner" wrote:

> Hi
> 
> Heres hoping someone can help me with a problem.
> 
> I need to design a small database to hold stock parts.
> This part is well within my abilities and is quite simple.
> eg
> Part_no
> Location
> description
> Price
> 
> etc.
> 
> The problem I have is that I need to be able to create some master parts 
> which will make a kit composed of individual parts.
> 
> The users need to be able to choose a master part which will return a list 
> of the component parts and details such as price etc. On the same order the 
> individual parts may also be ordered in addition to the master part.
> Also some of the individual parts will appear in more than one master part 
> group.
> 
> Any ideas on how to design tha tables to make this easy.
> 
> Many thanks in advance.
> 
> Brian 
> 
> 
> 
0
Utf
11/27/2007 2:23:02 PM
Hi David

Thanks for the help.

I will have to try this and see if it produces the results I need.

What I want is a stock application where I can order any part individually.

Eg "spark plug" and the list shows the item and quantity I have ordered.
I also need to be able to order "service" which would list 4 spark plugs 4 
leads and a filter etc.
I would hope this to be detailed on the order list by component.
This would then create a picking list to collect the individual parts to 
make the order.

Is this possible with your example.

Thanks

Brian




"David Cressey" <cressey73@verizon.net> wrote in message 
news:2NU2j.14371$Mg1.5540@trndny03...
>
> "B Garner" <brian@leach-ltd.co.deleteme.uk> wrote in message
> news:c9U2j.6442$B97.3070@newsfe7-win.ntli.net...
>> Hi
>>
>> Heres hoping someone can help me with a problem.
>>
>> I need to design a small database to hold stock parts.
>> This part is well within my abilities and is quite simple.
>> eg
>> Part_no
>> Location
>> description
>> Price
>>
>> etc.
>>
>> The problem I have is that I need to be able to create some master parts
>> which will make a kit composed of individual parts.
>>
>> The users need to be able to choose a master part which will return a 
>> list
>> of the component parts and details such as price etc. On the same order
> the
>> individual parts may also be ordered in addition to the master part.
>> Also some of the individual parts will appear in more than one master 
>> part
>> group.
>>
>> Any ideas on how to design tha tables to make this easy.
>>
>> Many thanks in advance.
>>
>> Brian
>>
>>
> You are describing a case of the Bill of Materials problem.
>
> One way to do what you describe is to build a second table,  Components,
> with fields
>
> Master_Part_No
> Component_Part_No
> Quantity
>
> To indicate that the part "Wheel" occurs 4 times in the master part 
> "Auto",
> you would put the part_no for auto in the Master_Part_No field,  the 
> part_no
> for Wheel in the Component_Part_no field,  and the number 4 in the 
> quantity
> field.
>
> (Assuming that all four wheels are the same).
>
> To create the lists you describe you would join the master parts table 
> twice
> to this table.  In one join,  you would use Master_parts_no as a foreign 
> key
> to Part_no.  In the other join, you would use Component_part_no as a 
> foreign
> key.
>
>
> 


0
B
11/27/2007 2:57:53 PM
Reply:

Similar Artilces:

year week table help
Hi All, Given: year = 2009 week = 3 How would I build a temp table of year/week values 27 weeks back. for the example above: 2009, 3 2009, 2 2009, 1 2008, 52 2008, 51 .... (Also, doesn't some years have 53 weeks?) thanks, rodchar About the 53/54 week issue is no longer an issue. 2008, 52 will work fiine. "rodchar" wrote: > Hi All, > > Given: > year = 2009 > week = 3 > > How would I build a temp table of year/week values 27 weeks back. > > for the example above: > > 2009, 3 > 2009, 2 > 2009, 1...

Search THIS Database
I am having a hard time finding questions I previously posted...is there a way to search for questions posted by a particular user? Also, is there a way to sort the search results based on the most recent to oldest? What are you using to read these posts? (I use Outlook Express, so how I search may not work for you.) Have you tried using on-line search tools ...? "PosseJohn" <PosseJohn@discussions.microsoft.com> wrote in message news:3ED9C2F5-A72B-4FAA-B7A3-599322D3B93C@microsoft.com... >I am having a hard time finding questions I previously posted...is ...

Help: Error removing exchange 2000
El error es el siguiente: Setup failed while Removing Active Directory objects for SMTP Service (error 0x80005000: An invalid ADSI pathname was passed.) . 260378 XADM: How to Manually Remove an Exchange 2000 Installation http://support.microsoft.com/?id=260378 Stewart Noe Microsoft Exchange Support This posting is provided "AS IS" with no warranties, and confers no rights. ...

Help with copy macro
Hi I am trying to use the macro recorder in Excel 2003 to create a macro that will copy selected data from one spreadsheet into another spreadsheet. The copy to spreadsheet is an Excel database and I need the macro to find the first blank row in the database and then do the copy. My code so far is as follows: Sub Copy_Feed_data() ' ' ' Keyboard Shortcut: Ctrl+m ' Sheets("FEED").Select Range("A2:M3").Select Selection.Copy Windows("ASCI PRICE DATABASE.xls").Activate ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScr...

Help needed deleteing undeliverable read receipts
I have 3 undeliverable read receipts stuck in outlook 2003 and as a consequence the send action of send /receive tries to send these three read receipts but it is caught in a loop and cannot send them, as a result I cannot send any message. I have tried to uninstall and reinstall Outlook but to no effect. Using exactly the same pop settings in OE I can send and receive mail so what could be the problem? I have been using Outlook for 3 years and have never had this problem before. T5 <noanswer@hotmail.com> wrote: > I have 3 undeliverable read receipts stuck in outlook 2003 ...

large database file size after new table addition
I have added a table to my database that is causing the database size to grow tremendously. There are a total of 807,000 records of zip codes along with corresponding latitude, longitude, city and state names. I have set Unicode Compression to YES by all text fields. What other settings or actions will help reduce database file size? On Thu, 15 Nov 2007 15:51:00 -0800, Susan wrote: > I have added a table to my database that is causing the database size to grow > tremendously. There are a total of 807,000 records of zip codes along with > corresponding latitude, longitude, ci...

helpful free add ons
http://www.asap-utilities.com/ ...

Need MS POS Export help
------------------------------------- I have inherited an MS POS system installed in 2005. This is not Dynamics but its predecessor. I want to move onto another POS system, but I need to export all the items, customers and vendors from the current system. This is my first POS system experience and I do not see an EXPORT in the file menu so that I could simply dump the files to flat files. I do see an export function on reports, but I don't see a report that gives the complete information about the files I need. I see that reports are built from XML but I don't see an easy way t...

averages/if formulas-need help ASAP!!
I have two columns: one has initials of person completing the task, next to it is the percentage of work that is equal to. At the bottom, I am hoping to have two tables depicting the average % of work and total % of work for each person. I've tried =average(e3:e23 (if(d3:23="dj")) Basically I have no idea how to create a correspondence between the name and % of work...HELP! I'm so confused I can't even really explain my problem :) >I've tried =average(e3:e23 (if(d3:23="dj")) That's pretty close! Try it like this... Array ente...

Need help with SQL
SELECT DISTINCTROW OUTLOOK_APPROVEt1.To, OUTLOOK_APPROVEt1.Subject, OUTLOOK_APPROVEt1.Received, OUTLOOK_APPROVEt1.Contents, OUTLOOK_APPROVEt1.CountOfImportance INTO OUTLOOK_APPROVEt2 FROM OUTLOOK_APPROVEt1 WHERE Received >= date_sub(curdate(), interval 1 month) and Received <= date_sub(curdate(), interval 1 day) WITH OWNERACCESS OPTION; I am having trouble with the Where portion. I would like for this query to reveal all the emails over the past physical month. Example February 1st I would like to be able to collect the data for all of January. Any help would be greatly...

Shortcut key help
Hello Does anyone know shortcut key for - deleting an entire row (not clearing the contents of the cells) and - switching between sheets in a workbook. (like alt+tab that we use of switching between windows) Thanks in advance. Trying2Learn Deleting row- Select the entire row and press Ctrl-minus Sheet switching- Ctrl-PgUp and PgDn. -- Jim "Trying2Learn" <Trying2Learn@discussions.microsoft.com> wrote in message news:34DD7503-BE94-4241-B6A4-34300EF2ED75@microsoft.com... | Hello | Does anyone know shortcut key for | | - deleting an entire row (not clearing the cont...

Query sorting help
I am in need of assistance with a Query. I have a query that assembles a daily production schedule. The shedule consists of many fields however for the sake of this question i wish to sort the query by a series of four fields. the first fiedld in the query is "DueDate" then by field "press1" then by "Press2" then Press3". The data in the field Due date is the actual date due. The data in "press1" will only be "6c" or nothing. The data in "Press2" will be one of three Strings: "25", "29", or "2...

Help with query criteria
I am creating an inventory database. Inventory is taken 3 times per day based on shift start. I want to be able to create a parameter query and then a report that will show the inventory taken at the start of each shift. There are nine areas that are inventoried each shift. I need to be able to query by date and time. I want the times to be broken down by shift name (i.e. Days, Afternoons, Nights). The form that feeds the table where the query is run has a txtDate field and txtTime field with default values of =Date() and =Time() so that when the data is saved to the Inventory table the ...

EXCEL HELP (ROWS & DATA)
I have a table in Word that has many rows and cells with text in it. I have a column of text in excel. I would like to get in excel the information from the work document of just the text that is found in my excel column. I tried vlookup but the document in word may have a sentence in it and vlook look at a whole cell not part of it. Thanks for the help very much. Example: word docuement has "table four is next to table three" in excel I have a column ...

Balance showing as #####
I have a simple set up - date - debit - credit - balance. My total has now gone to over 10,000 and it is showing up as ######## - how do I correct this in very simple terms please as someone helped me in setting this up and I am not too familiar with Excel 2003. Thanks.. "Terri" <terri@mylanusa.com> wrote in message news:e3XCGLAdJHA.1188@TK2MSFTNGP05.phx.gbl... >I have a simple set up - date - debit - credit - balance. My total has now >gone to over 10,000 and it is showing up as ######## - how do I correct >this in very simple terms please as someone helped ...

LIMITATION ON ACCESS 2003 DATABASE
Could anyone tell me whether there is a limit to the number of records which can be stored in a table in an Access 2003 database. I have an Access dbase with approximately 8463 records already and growing day by day. I find that sometimes all records can't be read (I run a document management system from the database whereby I lookup data and documents previously created). After getting the error I rebuild and compact the database and all runs fine for a while again. Then it doesn't read all the records again for some or other reason. I repeat the process and all fine. Is there a limit...

help Cant find Database Properties Window
I've doouble clcked on the entities, ive right clicked too and Ive looked thru every menu item and help but am unable to return to the properties window--ive already used it alot in this document already as i have 15 defined entitiies and about 18 relatonshisps. any ideas...im goin crazy oh yeah, its visio 2003 trial edition -- - ds ...

Probability Graph
Hello, I am trying to create a probability graph. On the x-axis, I would like to have days (365 days). The Y-axis would represent a volume of liquid (from 0.4 to 32.0L). This water is shipped to a location every 6 days. The water comes either in 0.4 L, 23.5 L or 32.0 L per shipment. How would you construct a graph that would visually show this knowing also that you can only have two consecutive shipments of the same quantity (ie. Shipment 1=0.4, shipment 2=0.4 but shipment 3 could not equal 0.4). Thanks so much for your help Have you tried using the data analysis component of Exc...

Is there a way to track changes made in an Access 2002 database?
Can a log be created that shows a name was changed on a certain date at a certain time by a certain user, etc.? -- Maureen Maureen <Maureen@discussions.microsoft.com> wrote: >Can a log be created that shows a name was changed on a certain date at a >certain time by a certain user, etc.? There's a simple example at ACC2000: How to Create an Audit Trail of Record Changes in a Form http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197592 Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html The article addresses edits, inserts, a...

HELP: how to locate objects on userform from VBA code
Hi All, How do I locate objects on userform based on the VBA code? For eg: I have some events like StudentYes_Click and StudentNo_Click in the VBAProject and there is some code in those events. I tried Debug --> Compile but didnt get any compile errors. So I am assuming I have those two radio buttons somewhere on the userform, But I am not able to find them. Is there a way to resolve this? how do I find those two radio buttons? Thanks in advance In the Visual Basic Editor (VBE), open the form. Select F4 to view the Propeties Window. Left-click anywhere within the form....

Help about typedef
please look at the following : typedef BOOL (CALLBACK * GETSTRCB) (PCTSTR,PVOID) ; using VC++6.0 compile ,errors display. error C2065: 'PCTSTR' : undeclared identifier error C2275: 'PVOID' : illegal use of this type as an expression c:\program files\microsoft visual studio\vc98\include\winnt.h(106) : see declaration of 'PVOID' error C2165: 'left-side modifier' : cannot modify pointers to data error C2071: 'GETSTRCB' : illegal storage class what can i do with it? "Yang Zibin" <iceviewer@163.com> wrote in message news:%23Jc9OT...

Outlook 2002 problems. Please help
I have just swapped from AOL to Outlook so am new to it. I keep getting two messages. Error 1402 Setup cannot open the registry Key HKEY_CURRENT_USER\Software\Microsoft\Shared tools\Proofing Tools\Custom Dictionaries. followed by Cannot start Microsoft Outlook MAPI32.DLL is corrupt or the wrong version. This could bave been caused by installing other messaging software. Please install Outlook. I have reinstalled Office a number of times, removed all traces of AOL. the problem resolves itself temporarily if I reboot. Since Outlook uses Word it may be pertinent to say that when I tr...

Need help with a project sheet, need date to show/not show based o
Hello, I have the following formula in cell S2 =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) This takes a date in cell B2 and adds 31 days to it and displays it. What I need to do is to NOT have cell S2 show any date if anything is in cell D2. I'm not very adept at Vis Basic if that what it will take to do it. Thanks Max Hi, No need for code =IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) Mike "Max" wrote: > Hello, > > I have the following formula in cell ...

Need to hide the error
Hi ... I guess this is always going to be a problem fo me. So sorry for asking for help like this many times. I have this formula here: =SUMPRODUCT(ROUND(F8*4,0)/4) that keeps giving me this "#VALUR!" error when nothing is being entered, of course this can be very annoying when presented and I want to know if anyone can help me, and actualy explain to me how it was done. Just a little info since I really want to tackle this problem on my own next time. Thanks again in advance. I really appreciate it. Koji Hi first you don't need SUMPRODUCT for this formula. The followin...

Will GP project management do what I need?
We had all project accounts right in the G/L, worked when we were small and could get all invoices in to the project WIP accounts of each project -Each project account had three categories- direct costs, reimbursable costs and Time. Now we have grown. And with Sarbanes Oxley etc etc, my present accounting system has to be upgraded. Does GP project Management help in entering all project costs when invoices from vendors are recived- by type of direct costs,type of reimbursable and integrate this the A/P Module? Also can I enter billing entries and then get an aging report of unbilled it...