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

    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:

    Account
    ListID | BankNumber

    Check
    AccountRefListID | Refnumber | Amount | Date

    BillPaymentCheck
    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,
    BillPaymentCheck.Amount,
    BillPaymentCheck.TxnDate

    FROM BillPaymentCheck

    LEFT JOIN Account ON BillPaymentCheck.BankAccountRefListID =
    Account.ListID;


    And also:

    SELECT Account.BankNumber,
    Check.RefNumber,
    Check.Amount,
    Check.TxnDate
    FROM [Check] LEFT JOIN Account ON Check.AccountRefListID =
    Account.ListID;

    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.

    Thanks,

    Daniel

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
    Posts
    15

    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.

    Daniel

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    15

    Yup - No Union

    r937,

    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?

    Thanks!

Posting Permissions

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