Database design

[supa_sumo]

Hi, i got back to working on databases again for the first time in years and ive got confused on the offset.I went to set about desigining my tables and cant figure out how to do it.most of its simple stuff however this one bit stumps me.

Im designing a database to hold stock information in work we manufacture parts for rally cars and when a customer rings up i need to have the price at hand.I can store the prices etc of individual parts,however some people request kits which would have there own stock number and all the same relevant data i.e. Item Name,Type,Cost,Number in stock,Selling Price.......

These kits would consist of several items from the stock table.and when a kit is sold it will be inserted into the Sold Items Table together with the other individual parts a Customer may order.

Any help would be gratefully recieved.

 

[Guus2005]

Please state your question

 

[rsmonkey]

well you dont really make it clear what you want.. but I have one word for you too look up: Concatenation.

The process of adding strings together & you could also aggregate functions to add prices up aswell etc.. blah blah!

 

[Guus2005]

You thought you were done just mentioning the word: "Concatenation" but what's that got to do with the price ánd speed of a laden swallow? coming from the east?

Well??

 

[rsmonkey]

well the impression i got from his post is that he has a Db which contains all these parts & prices etc.. but he sed that some people request kits so these kits could be made by concatenation of strings. alternately the way he has explained it he could simply have a sort of shopping basket front end that calculates price.. I never sed anything about me being done more pointing him in the right direction being an old Access user I thought he would preferably like a refresh rather than long winded explanations!

 

[Guus2005]

you're quite right!
nuf sed!

 

[neileg]

You thought you were done just mentioning the word: "Concatenation" but what's that got to do with the price ánd speed of a laden swallow? coming from the east?

Well??

African or European swallow?

 

[supa_sumo]

I looked into what you pointed me towards very breifly.but that didnt seem to be the easiest way to do it, i may be wrong but i thought that there would be a more simple way.

Stock ID Item name Type # in stock Cost Sell Price Picture location
5 Clubman Link rods 1 5 £15.00 £18.00
6 Rose joints 5/8 1 6 £23.00 £0.00
8 3/8-5/8 RJ spacer 1 100 £0.20 £0.25
9 Diamond Brackets 1 12 £12.00 £13.00
10 Ford Link Rod Bush 1 30 £1.20 £1.30
11 4 link Kit Clubman 1 0 £130.00 £180.00

Say that is part of my stock list:-
what i will want when i finish is to click on 4 link Kit clubman and have a subform displaying what is in the kit which is

4x link rods
4xRose Joins
8x 3/8-5/8 RJ spacers
2xDiamond Brackets
4xFord Link Rod Bush

I wondered if it was possible to make a table with the headings

KitID (primary key)
Stock ID (linking to stock id)
Containing Stock ID (also linking to stock ID)
Quantity

Im not sure if this is even possible as i cant get my head around the Relationships.hope this helps u understand what im trying to do.

 

[neileg]

I seem to recall bills of material (BOM) being covered in these forums before. That's the same concept as your kitting list so it's worth doing a search.

 

[Guus2005]

African or European swallow?

European ofcourse...

 

[supa_sumo]

Makes so much sence now,BOM really solves the problem makes it so easy to find the data i require. If anyone else has a similar problem this is the link to what helped me understand http://www.mvps.org/access/queries/qry0023.htm Thanks for your help

 

[neileg]

Happy to help.