velohead66

So simple - if you know how !!

I have 2 tables, each with 2 columns as follows;
Table R = Agent Reference Number / Value
Table S = Agent Reference Number / Value

The sources of the tables are from different places and contain duplicate (but valid) agent numbers, with different values. Within any ONE table, I want to treat two duplicates agents values as one.

Then, I want to produce a query, that lists 3 things as follows;
Agent Reference Number / Value from R / Value from S

Problem is, table R may have more/less/same number of agents then S, and ditto for table S (to table R).

ie an agent may exist on R but not on S, and at the same time, an agent may appear on S but not on R.
Also any agent in S or R, may appear twice, which is valid.


The result of my query is that BOTH values columns sum to either the sum of table R OR table S, but NOT R and S respectfully.
So something is wrong.

Column 2 should sum to the same as table R, and column 3 should sum to the same as table S.

HELP !!

PS File is only 688 kb so I could e-mail.

BruceR

Quick fix:

Try using a Union query such as:

SELECT
Agent_Reference_Number,
Value AS Value_R,
0 AS Value_S

FROM Table_R

UNION

SELECT ALL
Agent_Reference_Number,
0 AS Value_R,
Value AS Value_S

FROM Table_S;

You will then need to use another query to Group the results i.e.

SELECT
Agent_Reference_Number,
SUM(Value_S) AS S,
SUM(Value_R) AS R

FROM thequeryabove
GROUP BY Agent_Reference_Number


I'm sure I know a neat way of doing this in one query but its 00:22 AM !!! and I sure you just want a quick fix

Bruce

BruceR

Here's the single query that [seems] to do what you require

Edited to say: whoops causes Access to crash...


[Edited by BruceR - 12/10/2003 12:43:00 AM]

velohead66

A nice long fix is fine by me,
as it's now 16.31 and time for me to spend 60 mins on the highway.
[edit-read:I'm going home now]

[Edited by velohead66 - 12/11/2003 6:38:33 AM]