PDA

View Full Version : Access query Help



Nigel Thomson
18-08-2004, 03:34 PM
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

andrew93
18-08-2004, 06:48 PM
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

Nigel Thomson
20-08-2004, 02:56 PM
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)

andrew93
20-08-2004, 03:07 PM
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

Nigel Thomson
20-08-2004, 03:22 PM
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

andrew93
20-08-2004, 03:30 PM
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
20-08-2004, 03:36 PM
> 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.

Nigel Thomson
20-08-2004, 04:24 PM
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