Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query when no data (2003 all service packs)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Union Query when no data (2003 all service packs)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query when no data (2003 all service packs)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Union Query when no data (2003 all service packs)

    Try setting the Column Headings property of the crosstab query to

    "EncounterSalesExported";"MYOBImportedSales";"MYOB Receipts"

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query when no data (2003 all service packs)

    Got there - Thanks again for your great assistance
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •