queries

  • Thread starter Thread starter hogan
  • Start date Start date
H

hogan

I have two tables in a parts database called "new parts" & "used parts". The
primary keys are "used part number" & "new part number". How do I create a
query so I can have both used and new parts listed together. If you need
additional info please let me know.
 
Let me add a few things. The tables have the same field names other than the
primary key. I have two seperate tables because my primary keys are
autonumbers with the format of "DFN"000 for new parts and "DFU"000 for used
parts.
 
You are suffering the first symptom of inaccurate relational database design.
The correct answer is combine both tables and add a field to identify new
and used. Then you wont have this problem.

You could solve the immediate problem with a Union query, but Union queries
are not updatable, so you can't use it for a form record source unless you
want it to be read only.

But, as I said before, you need to redesign your database. This is only the
first of many problems you will have using two tables than have the same data.
 
I have two tables in a parts database called "new parts" & "used parts". The
primary keys are "used part number" & "new part number". How do I create a
query so I can have both used and new parts listed together. If you need
additional info please let me know.
Click to expand...

Your table design IS WRONG.

I'd go with *one* table, with a Yes/No field for Used (or New).

A Query selecting just new (used) parts will be efficient if there is a
nonunique index on this field, and you won't have this problem. You also
should not be storing text in your autonumber field... you're actually not
doing so now, since the Format property of the field just controls the
display, not what's actually stored.

With your current design you will need a UNION query to string the two tables
together. See the online help for UNION, it's pretty clear; post back if you
have problems.
 
John,

Thank you for the info. Is there a way to have a unique part number for new
or used. I am referring to my DFN and DFU in my autonumber.

Thank you.
 
Thank you for the info. Is there a way to have a unique part number for new
or used. I am referring to my DFN and DFU in my autonumber.
Click to expand...

If you're using the Format property of the field to display the DFN and DFU
value, then you are NOT storing a unique number. The Format isn't stored in
the table and does not differentiate the records.

To get a unique part number you can use an Autonumber, or you can use a Long
Integer and assign its value in VBA code. With the latter you can use either a
single sequence so that "used" and "new" part numbers will never overlap, or
(not a good idea IMO) use two separate series. If you do the latter then you
will need to use a two-field joint primary key comprising the yes/no
"New/Used" field and the Long Integer.
 
Back
Top