Forum Home
Press F1
Thread ID: 48257	2004-08-18 04:34:00	Access query Help	Nigel Thomson (629)	Press F1

Post ID	Timestamp	Content	User
262796	2004-08-18 04:34:00	I'm back this could end up being a bit long winded but here goes, I have created two tables from .csv files model_list consisting a list of models and their parts in the form.... Model_Number(contains the model num) Capacitor(contains part num) Resistor(contains part num) etc The fields are all text fields, (model numbers & part numbers are alpha-numeric) the model number is the primary key, simply because there can be only one of each model in the model_list. price_list consisting of all the parts and their price and mark-up in the form parts_number, price, markup, etc parts number is obviously the primary key here The Problem I can't seem to link the tables successfuly, well I can link them, but it just seems wrong to me in the model_list table there are 8 fields (obviously model number) but also 7 other fields, capacitors, resistors etc which have part numbers in them, how do I link these fields to the price_list [part_number] field having 7 fields linking to this one seems a bit extreme, but I just can't seem to figure it out thanks	Nigel Thomson (629)
262797	2004-08-18 07:48:00	Hi Nigel Set up set up set up. Design design design. These are the most important parts of any database. A model is a collection of parts. A part has a number of attributes (including price). Suggested structure : Parts Table part_num (primary key) part_desc part_price part_other_bits (as you see fit) Models Table model_num (primary key) model_desc model_other_bits (ditto) A model is then "constructed" by adding parts. This is done through an intermediary table which I shall call "model_parts" Model_Parts Table model_num (dual primary key) part_num (dual primary key) part_quantity (default to 1 - this is the quantity of parts used in the model) Set up the first 2 fields as a dual primary key. This creates a many-to-many relationship between the models and parts - one model can have multiple parts (and multiples thereof using a quantity > 1) and one part can be in multiple models. This is the easiest way to set up a many-to-many relationship which is what you are trying to do. Link model_num in models table as a one-to-many relationship with model_num in the model_parts table and link part_num in the parts table to part_num in the model_parts table. Do this under Tools, Relationships. Please ask if you want to know about that. Create a form or query to set up the models (i.e. enter the model details into the model_table), create another form or query to handle parts (entering parts stuff into the parts table) and then a third form to "build" or "construct" models by adding part numbers to models. You can do this with drop down boxes by first selecting a model and then selecting a part, to add another part to the model, you select the model again (on a new line) and then select another part and so on. there are ways of combining all 3 onto one form but let's start with the basics first and we can worry about the fancy bits later. Any forms, queries or reports or going to make use of this intermediary table and given it is a many-to-many relationship, handle it with caution! HTH Andrew	andrew93 (249)
262798	2004-08-20 03:56:00	The problem is that, i am having to scan in pages from a hard copy book in the grid style, so i was unable to set the database as mentioned above, although I now may be able to do something similar (Boss reconsidering things) new problem Ok I have set up the model parts table as above Model_Parts Table model_num (dual primary key) part_num (dual primary key) I had to kludge it cutting and pasting from the existing models table I have found that there are parts that are missing from the Parts Table but are included in the Model_Parts Table, now I can create a query that joins the two tables and shows me all the part numbers in the Model_Parts Table and the prices from the Parts Table for those that exist in the Parts Table. ooops price_list = Parts List SELECT MPT.[MODEL NUMBER], MPT.[Part Number], MPT.Description, price_list.PRICE, price_list.[Markup Factor], price_list.[Selling Price] FROM price_list RIGHT JOIN MPT ON price_list.[PART NUMBER] = MPT.[Part Number] ORDER BY price_list.[Selling Price], MPT.[Part Number]; But what I want to do, is find all of those part numbers that exist only in the Model_Parts Table and have no corresponding entry in the parts table and display these, I have tried using "where <> etc" and I keep getting no records returned, (if I try "!=" I get errors)	Nigel Thomson (629)
262799	2004-08-20 04:07:00	Hi Nigel, if you go to the query design screen (the pictorial version rather than the script version that you provided, maybe make a new query) and double click on the line linking the 2 tables, you can set the "Join Properties" (or it is also available under View, Join Properties) to "Include all records from 'model_parts' and only those records from 'parts' that are equal", click Ok. If you add part_number from both tables to your query, then in the criteria section of the query (under part_num from the parts table only) enter "Is Null" (without the quotes, no full stop, no nothing except for Is Null). This will then pick up all records from model_parts that do not have a corresponding entry in parts. HTH Andrew	andrew93 (249)
262800	2004-08-20 04:22:00	cool that works perfectly is there a way to show each part numer only once, I have removed model number from the query, to facilitate this, for example if 4 different models use part "abc" I am getting "abc" listed for times I know it can be done, but for the life me I can't remember the term used and I should have indexed my old Tech notes a bit better thanks heaps Nigel	Nigel Thomson (629)
262801	2004-08-20 04:30:00	Try query design, View, Totals - leave as "group by" - if that doesn't work change "group by" for the model_parts half to "Last".	andrew93 (249)
262802	2004-08-20 04:36:00	> The problem is that, i am having to scan in pages > from a hard copy book in the grid style, so i was > unable to set the database as mentioned above, If you are scanning the records in then you could store them in an upload table and then transfer them across to the real table (with the correct setup and all the other fields you need) by using an append query - making sure that you set your keys correctly so that duplicate entries are not put into the table twice. Might take a bit of mucking around but will be safer and easier in the long run.	andrew93 (249)
262803	2004-08-20 05:24:00	That sounds like a good idea, I'm going to go and sit down and read up one of my old course books, on queries Thanks for the help	Nigel Thomson (629)
1