Query Question

[Guest]

I have 2 tables that were created by importing from 2 spreadsheets in excel.
Table 1 lists procedures/locations/equ1/equ2/equ3/equ4/equ5
Table 2 contains the crosswalk of what the equ represents by number and
description. For example table 1:
Procedure location equ1 equ2 equ3 equ4 equ5
xyz nyc 34
xyz nyc 34 212
xyz brx 22 78 98

There are 12 vets, 5 locations, 400 pieces of equipment. We don't care about
who is performing these procedures only what they are performing and with
what. So many vets can use the same piece of equipment at the same location.
In the table and spreadsheet that would be listed as many times as a
different vet used the same piece of equipment at the same location. There
are no primary keys to the tables

I tried to write 1 query pulling all the info but could not get it to work so
I wrote 5 different queries because there are a total of 5 pieces of
equipment per procedure the vet could use. Each query is pulling the
procedure/equ/location/equ# (from table2)/description(from table2) So query 1:

Procedure EQU1 EQU#(table2) Description(table2) location
xyz 34 34 CableDraw
nyc
xyz 34 34 CableDraw
nyc
xyz 22 22 Blood
brx

What I would like to do is have one query that pulls all 5 pieces of
equipment (if listed) per procedure per location removing any duplicates. So
if 2 vets used the same piece of equipment at the same location for the same
procedure it would only be listed once, with the description of the
equipment. The main thing is what equipment is used for each procedure at
each location.

Or somehow join the 5 queries and remove any duplicates pieces of equipment
per procedure

Help Please!!

 

[Guest]

I managed to remove the dups from each of the 5 seperate queries now need to
figure how how to pull it all into one

 

[John Vinson]

I have 2 tables that were created by importing from 2 spreadsheets in excel.
Table 1 lists procedures/locations/equ1/equ2/equ3/equ4/equ5
Table 2 contains the crosswalk of what the equ represents by number and
description. For example table 1:
Procedure location equ1 equ2 equ3 equ4 equ5
xyz nyc 34
xyz nyc 34 212
xyz brx 22 78 98

Ok... that's a good spreadsheet. It's a TERRIBLE table - in fact, with
400 values of equ, it's not even a LEGAL table (you're limited to 255
fields).

There are 12 vets, 5 locations, 400 pieces of equipment. We don't care about
who is performing these procedures only what they are performing and with
what. So many vets can use the same piece of equipment at the same location.
In the table and spreadsheet that would be listed as many times as a
different vet used the same piece of equipment at the same location. There
are no primary keys to the tables

A normalized design would be a "tall thin" table with fields
Procedure, Location, and Equ, with five *ROWS* if five different
pieces of equipment were used.

I tried to write 1 query pulling all the info but could not get it to work so
I wrote 5 different queries because there are a total of 5 pieces of
equipment per procedure the vet could use. Each query is pulling the
procedure/equ/location/equ# (from table2)/description(from table2) So query 1:

Procedure EQU1 EQU#(table2) Description(table2) location
xyz 34 34 CableDraw
nyc
xyz 34 34 CableDraw
nyc
xyz 22 22 Blood
brx

What I would like to do is have one query that pulls all 5 pieces of
equipment (if listed) per procedure per location removing any duplicates. So
if 2 vets used the same piece of equipment at the same location for the same
procedure it would only be listed once, with the description of the
equipment. The main thing is what equipment is used for each procedure at
each location.

A "Normalizing Union Query" will do this. From your Table1 try

SELECT Procedure, Location, Equ1 AS Equ
FROM Table1 WHERE Equ1 IS NOT NULL
UNION
SELECT Procedure, Location, Equ2
FROM Table1 WHERE Equ2 IS NOT NULL
UNION
<etc for all values of Equ)

For your 500-column spreadsheet you'll have to have *at least* two,
maybe more, of these UNION queries.

Base Append queries on each of them to populate a tall-thin table.

This table will remove all dups; you can then do a very simple join of
the tall-thin table to the Description table to pick up the
descriptions. Make it a Totals query grouping by Location to see which
equipment was at the same location.


John W. Vinson[MVP]

 

[John Spencer (MVP)]

Take a look at using a UNION query which can combine the 5 queries you have into
one and remove duplicate rows.

SELECT *
FROM QUERYONE
UNION
SELECT *
FROM QUERYTWO
....

I would probably try to normalize the data structure using a union query.

Something like the following. UNION queries cannot be created using the query
grid, but must be entered by typing. A quick shortcut would be to build the
first query and then switch to SQL view. COPY the SQL, type UNION at the end,
and paste the SQL. Now modify EQU1 to EQU2 in the pasted code. Repeat as
needed for the other EQU# columns.

SELECT Procedure, Location, equ1, Description
FROM YourTable Inner Join EQUTable
On YourTable.Equ1 = EquTable.Equ
UNION
SELECT Procedure, Location, equ2, Description
FROM YourTable Inner Join EQUTable
On YourTable.Equ2 = EquTable.Equ
UNION
SELECT Procedure, Location, equ1, Description
FROM YourTable Inner Join EQUTable
On YourTable.Equ1 = EquTable.Equ
UNION
....