Query for calculation (1 Viewer)

[mounty76]

Hi All,

I need to make a query that will subtract the results from one query against the results of another, issue I have is that the results aren't equal....

Query1 (Last month cash ROB)

Cur Amount

EURO 500.00
USD 1000.00
GBP 50.00

Query2 (Cash used this month)

Cur Amount

EURO 100.00

Note I've only used euros this month and none of the other currencies

I need Query3 (Current Cash) to look like this:

Cur Amount

EURO 400.00
USD 1000.00
GBP 50.00

I tried to use an IIf statement in the query to say if currency from Query1 = Currency from Query2, query1.amount-query2.amount, query1.amount........but it is coming back with errors.....I think Query2 needs to have zero values for currencies that haven't been used....but not sure....some guidance on the right way to go about this would be amazing. FYI The currency is linked to a Currency Table so I can add more as needed

 

[plog]

The database term for what you need is a FULL OUTER JOIN. That means all results from both tables. However, Access SQL does not support FULL OUTER JOINs, so you must hack your way into one. There's 2 ways to do that:

The hard way involves UNION query and we can go into that if you don't have the necessary datasource for the second way:

The easy way is if you have a datasrouce with all the currency you want to report on. Do you have such a datasource? You can't use either query because there's no guarantee that there will be all currencies.

Do you have a table that lists all the currencies you want to report on? What is its name?

 

[mounty76]

Yes I have a table called 'tblCurrency' which has all the currencies on, the actual expenses are on 'tblExpense' (Query2 takes the results from this table - so cash totals for this month) and the End of month ROBs are on a table called EOMCount, (Query1) I use a query to extract the latest (last months) data from

 

[plog]

So you build a new query, bring in tblCurrency, Query1 and Query2. Link Query1 to tblCurrency and Query2 to tblCurrency, but not each other. Change both joins to show all data from tblCurrency. Then in the bottom section bring in Cur from tblCurrency and build a calculated field like so:

MonthDifference: Nz(Query1.Amount) - Nz(Query2.Amount)

That will give you the results you want.

 

[mounty76]

Thank you very much, I'll try this later today/tonight, unfortunately don't have the time at the moment, I'll keep you posted. Thanks again

 

[mounty76]

Works great, thanks very much