Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006

    Unanswered: Is it Possible to Combine Similar Data from Different Tables Without Using UNION?

    I've been away from programming for a few years, so I'm a bit rusty. I looked through the posts here and the only one I found that looked like it came close to my quesiton had 0 replies and was quite an old post. ("Bring together tables without Union Query" from 2004)

    Here's my Scenario:

    Three Tables:

    ListID | BankNumber

    AccountRefListID | Refnumber | Amount | Date

    BankAcountRefListID | Refnumber | Amount | Date

    The relationship is:
    Account.ListID = Check.AccountRefListID = BillPaymentCheck.BankAccountRefListID

    For every check in each table, I need to pull out the associated BankNumber from the Account table so:

    SELECT Account.BankNumber,
    "Bill PMNT" AS Expr1,

    FROM BillPaymentCheck

    LEFT JOIN Account ON BillPaymentCheck.BankAccountRefListID =

    And also:

    SELECT Account.BankNumber,
    FROM [Check] LEFT JOIN Account ON Check.AccountRefListID =

    My question is, how can I combine the two queries to get the output of both in a singe query?

    Currently, I'm doing this in MS Access 2003 and I'm stuffing the results of both queries into a third table and then building the report of off this.

    In the 'functional' version, the queries are Append queries and append to a table I hand crafted to accept their results. While this works, I'm still struggling with how I might package the process and make it easy to deliver as a solution to our customers ...

    Even if a UNION would work, I don't have access to it as the ODBC driver for QuickBooks does not support it.

    I'm hoping there is a trick I've missed and/or forgotten. Any help would be greatly appreciated.



  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Did you consider creating a VIEW instead of retrieving data all the time? View would be created only once and used in report when needed.

  3. #3
    Join Date
    Feb 2006

    A Good Suggestion Except That . . .

    I can't create views. I can't create Stored Procedures either. There is no access via QuickBooks QODBC driver to do either.

    Sorry, I should have mentioned that in the origional post.

    Thanks tho, for the suggestion.


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    no UNION?

    just carry on with your three step process, but replace your "hand-crafted" table creation by the following method

    query 1: Make-Table
    query 2: Append
    query 3: Select | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006

    Yup - No Union


    Thanks for the advice. I will try it here ion a bit.

    My next step is to try and figure out how to package this so it is generic to the end user, regardless of the QuickBooks Company Data File in use.

    Is there some way to take a MS Access DB with all its tables and queries and turn it into a stand alone app? I thought about using a switchboard, but I'd rather make it package-able and distributable, which probably means I'm going to have to go write it in VB code . . .

    Anyone have any tips/tricks/advise they feel like sharing?


Posting Permissions

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