Field result duplicates following join

[dje]

I hope someone can help me.

I have two queries:

USDBAL has a result of
Date Movement
1/1/08 123.45
2/1/08 234.56
3/1/08 345.67

The numbers represent the movement in balance for that day

From another calculation I have a query result

PURCHASES
Date Transaction
1/1/08 50.00
2/1/08 24.00
2/1/08 75.00

I want to join the two queries to show
Date Movement Transaction Total Move RunSum
1/1/08 123.45 50.00 173.45 173.45
2/1/08 234.56 24.00 258.56 432.01
2/1/08 75.00 75.00 507.01
3/1/08 345.56 0.00 345.56 852.57

i.e. I want the movement result to show once only per day but for two (or
more) transactions to show all results. The end aim is to have a calculation
of 234.56+24.00+Nil+75.00 to show the total movement for 2/1/08

At the moment the 234.56 is quoted twice affecting my result. i.e.

Date Movement Transaction
1/1/08 123.45 50.00
2/1/08 234.56 24.00
2/1/08 234.56 75.00
3/1/08 345.56 0.00

The date in each table is joined.

I have read elsewhere about DISTINCT but this appeared to have no effect.
Can anyone tell me how to overcome this before I give up on my project? I
hope that this is the last issue that is likely to defeat me on what has
proven to be over ambitious for an Access novice.

Thanks

Thanks

 

[dje]

I have worked out the answer via another posting by Allen Browne.

Forinformation I think you use the group by function on a query on the
transactions table i.e. group by date and sum the amounts which is then used
in the new query. i.e. only one amount per day which aligns with the date.

Thanks