Union Query when no data (2003 all service packs)

Topic

New Reply

I have a Union Query consolidating Data from 3 tables, which then feeds a Cross Tab Query which feeds a Report:

SELECT “EncounterSalesExported” AS Operation,MYOBSellAcct AS Acct,Day,EncounterSales as Total,AccountName from EncounterSalesbyAcctNo
UNION Select
“MYOBImportedSales” AS Operation,AccountNumber AS Acct,[Date by Day] As Day,TotalMYOBSale AS Total,AccountName from MYOBSalesbyAcctNo
UNION select
“MYOBReceipts” As Operation,AccountNumber As Acct, DAte as Day,TotalReceived as Total, AccountName from MYOBReceiptsbyAcctSummary;

This works wonderfully well when there is data for each of the 3 queries. However on most occassions there will be no data for “MYOBReceipts” and the cross tab then falls over . I am happy for this item to end up as zeros if there is no data, but dont know how to accomplish this
TIA
Steve

Reply | Quote

Replies

In what sense does it fall over (please be specific)? If one of the queries doesn’t return any records, it simply doesn’t contribute anything to the union query, at least, that’s what happens for me.

Reply | Quote

Sorry Hans – I should have been more specific
I have two accounting systems, Encounter and MYOB. At the end of each day, Encounter exports data on sales and purchases in a form that can be imported by MYOB and I need to ensure these balance or explain any differences. I am working on the Sales side only now.
One of the source of differences is that data can be directly entered into MYOB, by passing Encounter and resulting in a different Profit and Loss than that generated by Encounter. The union query consolidates data from the 3 different sources (1 Encounter Export, 2 MYOB Imports from Encounter and 3 direct input into MYOB) into one table giving each source as a specific name (Operation) eg Encounter Exports vs MYOBImport vs MYOBReceipts. This then gives me the ability to have a cross tab report that shows columns for each source and enables me to manipulate the data for comparisons – there should be no difference between Encounter Exports and MYOB Imports, therefore any difference in the P&L should be explained by MYOB direct entries- ie MYOB Receipts
In my problem case, as there were no MYOBReceipts (direct entries into MYOB) for this date, there is no column in the CrossTab query for this date. This column therefore gets Gets reset to Expr1 (The crosstab is used for later calculations ). When the final query tries to add that non existent number to the MYOBImported number, I get “The Microsoft Jet Database does not recognize ‘AllTransactionsbyAcct_Crosstab.MYOBReceipts’as a valid field name or expression.
Hope this is clearer. Main intent is to take data from 3 sources and match them, identifying where there are differences
Steve

Reply | Quote

Try setting the Column Headings property of the crosstab query to

“EncounterSalesExported”;”MYOBImportedSales”;”MYOBReceipts”

Reply | Quote

Got there – Thanks again for your great assistance
Steve

Reply | Quote