Help With Query for Two Tables with Different Structures

[ridgerunner]

I am working in Access 2003 (soon to be 2007).

The first query I built sums the scores for six categories. This pulls data
together from several different tables and seems to work fine.
QUERY 1
InspID StoreNo DM Insp Date DMCatID DM Score Sum
171 1 07-Feb-08 1 12
171 1 07-Feb-08 2 13
171 1 07-Feb-08 3 18
171 1 07-Feb-08 4 12
171 1 07-Feb-08 5 5
171 1 07-Feb-08 6 0
127 2 28-Aug-08 1 25
127 2 28-Aug-08 2 16
127 2 28-Aug-08 3 18
127 2 28-Aug-08 4 19
127 2 28-Aug-08 5 5
127 2 28-Aug-08 6 5
194 2 28-Sep-08 1 25
194 2 28-Sep-08 2 15
194 2 28-Sep-08 3 19
194 2 28-Sep-08 4 19
194 2 28-Sep-08 5 5
194 2 28-Sep-08 6 5


I need to build a query from another table that has many fields for each
record that need to be compared to the total scores in the first query. I
plant to build a third query to pull everything together.
For example:
Store 1
InspDate 01/01/08
PctSafe 98
SortandStraigtenFrontPct 95
SortandStraigtenBackPct 85
SweepFrontPct 95
SweepBackPct 89
Etc (there are quite a few).

I cannot determine how to do this. Does the second query need to be a
cross-tab query? I am not too familiar with them and that may be why I am
having difficulty. It seems simple to do the comparisons until I sit down
and try to get it to work.

Your help is greatly appreciated.

ridgerunner

 

[KARL DEWEY]

Does the second query need to be a cross-tab query?
You did not post the structure of the second table nor sample data.

Without the second query I can not relate your third output to the first
query.

 

[ridgerunner]

It is at the very bottom. I am sorry I did make it clear. I just tried to
set up cross-tab query, but I could not make that work.

You did not post the structure of the second table nor sample data.

Without the second query I can not relate your third output to the first
query.

For example:

Table 2
Fields Data
Store: 1
InspDate: 01/01/08
PctSafe: 98
SortandStraigtenFrontPct: 95
SortandStraigtenBackPct: 85
SweepFrontPct: 95
SweepBackPct: 89
Etc (there are quite a few more fields).

 

[ridgerunner]

I have a bad habit of leaving the negatives out of sentences as I did below,
when I meant to say "did NOT make it clear". Anyway, I was able to
successfully match the records by setting up the query with a series of IIF
statements.