Comparing two queries

[mrz via AccessMonster.com]

Hello All,

I need to compare to queries. The two queries have a field that is common
(SS#).

Query # 1

SS# -- Last Name ? Exp Code ? Gross Amount

Query # 2

SS# -- Last Name ? Exp Code ? Expense Amount

My objective is to get a report that would show me for example John Doe
with a total for his Gross Amount and a total for his Expense Amount. But I
only need to see John Doe once

Can this be done? Please let me know.

Thanks,
MRZ

 

[CJ]

Basically what you need to do is create a report based on the two queries.
Group the report by SS#. In the SS# group footer add a text box that is the
sum of the ExpCode and another that is the sum of GrossAmount.

The report wizard will make this really easy for you. When you get to the
wizard step that asks about sorting, click on the Summary Options button.
You will be able to total any numeric values there.

Hope that Helps
CJ

 

[mrz via AccessMonster.com]

Thanks CJ for posting...
Question:
Do I need to create a relationship first between the two queries?

To my understanding the wizard does not allow me to select two queries at
the same time.

Even if it did, how would i know which Social Security to pick as the
header for the grouping. This has always been a problem for me. I get to a
point that exporting the data to excel is faster.

Please advice me, thanks again
MRZ

 

[CJ]

Do not create a relationship between the two queries. Relationships are
created between tables, not queries.

When you are in the wizard, select all of the fields that you want from the
first query, then BEFORE you click on next, select the other query from the
dropdown and choose the fields from that query. Your SS# number should be
the same data in both queries, so you do not need to select it from the
second query.

As long as the two SS# fields contain the same data, Access will create a
join between the two queries in the SQL statement behind the report. If "Jim
Smith" is SS#5555 in the first query and also SS#5555 in the second query,
the grouping will work out with no problems.

CJ

 

[John Spencer (MVP)]

Perhaps it can be done. It depends on your data.

SELECT Q1.LastName, Q1.ExpCode, [Gross Amount], [Expense Amount]
FROM Q1 LEFT JOIN Q2
ON Q1.LastName = Q2.LastName AND Q1.ExpCode = Q2.ExpCode

In Access, a query can be reused in another query as if it were a table. If all
you want is a total, then perhaps something like:

SELECT Q1.LastName,
Sum([Gross Amount]) as TotalGross,
Sum([Expense Amount]) as TotalExpense
FROM Q1 LEFT JOIN Q2

 

[mrz via AccessMonster.com]

Hey CJ

I appreciate your posting. Let us assume that the SS# in both queries are
not quite the same. That is why I need to do this. Both queries contain
almost similar data. Some of the SS# should equal, others will just show at
the bottom. The result of the report from these two queries should surface
the following:

Query # 1

SS# -- Last Name ? Exp Code ? Gross Amount

Query # 2

SS# -- Last Name ? Exp Code ? Expense Amount

Report should show:

The sum of the Gross Amount in query # 1 per SS#
minus the sum of the Expensse Amount in query # 2 per ss#
equals the diference of money in both queries.

I hope this can help you help me! CJ I appreciate your thoughts again.

Thanks,
MRZ

 

[mrz via AccessMonster.com]

Hey John Spencer

Please see my previous posting to CJ. I will also like your input.
Thanks in advance!

MRZ

 

[John Spencer (MVP)]

SELECT Q1.[SS#], Q1.[LastName],
Sum([Gross Amount]) as TotalGross,
Sum([Expense Amount]) as TotalExpense
FROM [Query # 1] as Q1 LEFT JOIN [Query # 2] as Q2
ON Q1.[SS#] = Q2.[SS#]
Group By Q1.[LastName]
HAVING Abs(Sum([Gross Amount]) - Sum([Expense Amount])) > .0001

If your money amounts are actually using a currency type field or an integer
(single or long) then you can simplify the Having clause to read

HAVING Sum([Gross Amount]) - Sum([Expense Amount]) <> 0