Combining 2 tables

[Guest]

I have an equipment database and a vehicle database. I have a unique field
"types" taht exists in both tables. I'm trying to sumarize "query" them into
one query/table to run reports from. How do I summarize the 2 tables and have
the unit that share the same type show up independantly. Lets say one table
has 50 records and the other has 75. Instead of getting 125 records I get 115
because some of the units in seperate tables have the same type.

 

[Allen Browne]

Sounds like you either have some vehicles that have no Type, or some Types
that are not use in the Vehicles table.

You need to find out which it is, and then use an outer join in the query.

Details in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[kingston via AccessMonster.com]

A union query will automatically remove duplicate records:

SELECT * FROM Table
UNION SELECT * FROM Table2;

The number of fields from both tables need to be the same. However,
duplicates are determined by an entire record, not by select fields. Could
you provide more information about the table structure if this does not work
for you? What is the logic behind the selection of a record from one table
and not the other? If you can come up with this logic, you can use the
results of the union query and exclude the records that you don't want with a
subquery. For example, if you can develop a list of IDs from one table
(select IDs from Table2 where Type matches Table1), you can use NOT IN to
remove those records from the union query.

 

[Guest]

Let me try to simplify. The Equipment database is maintained seperately
because it is less complex than the vehicles and some of the equipment can
actually be mounted on vehicles.

The "types" field is used to identify the vehicle or equipment type and both
the vehicle and equipment table use the same "types" table to choose from.
One of the types is "auger rig" I have auger rigs that are vehicles and some
that are equipment

I wish tp combine these table for the purpose of reporting etc. I wish to
show every record from both tables.

Vehicle fields Unit, type, description
Equipment fields Unit, Type, description

 

[kingston via AccessMonster.com]

If you want to show every record from both tables, the union query will work.
However, adding 50 records and 75 records will always result in 125 records
unless there are duplicates of the entire record. If you want to show all
records regardless of duplicates, use UNION ALL.

Let me try to simplify. The Equipment database is maintained seperately
because it is less complex than the vehicles and some of the equipment can
actually be mounted on vehicles.

The "types" field is used to identify the vehicle or equipment type and both
the vehicle and equipment table use the same "types" table to choose from.
One of the types is "auger rig" I have auger rigs that are vehicles and some
that are equipment

I wish tp combine these table for the purpose of reporting etc. I wish to
show every record from both tables.

Vehicle fields Unit, type, description
Equipment fields Unit, Type, description

A union query will automatically remove duplicate records:

[quoted text clipped - 17 lines]