+ Reply to Thread
Results 1 to 4 of 4

tricky SQL problem using VBA Excel to read Access File

  1. #1

    tricky SQL problem using VBA Excel to read Access File

    I'm having trouble with a somewhat complex SQL query I'm running from
    Excel VBA to query an Access database. I'm trying to get as part of
    the results the number of unique AccountNos served by each AuditorID
    that meet all of the criteria, but instead the count is coming up as
    the number of transactions (more than 1 per Account can be common).

    The sql command is (broken up for readability here):

    SELECT [Meas].[AuditorID], Count([HUacct].[AccountNo]) AS [Nunitlight],
    Sum([Meas].[Qty]) AS [Nlight] FROM tblMeasureInstall Meas

    INNER JOIN

    (SELECT DISTINCTROW [TMI].[AccountNo]
    FROM tblMeasureInstall TMI
    WHERE ( [TMI].[InstallDate]<>NULL AND ([TMI].[InstallDate] BETWEEN
    #07/01/2005# AND #12/31/2005#) And ([TMI].[Canceled]<>True) AND
    ([TMI].[MeasureID] in (62,63,77)) And ([TMI].[MeasureGroupID]=6) )
    GROUP BY [TMI].[AccountNo] ) HUacct

    ON [Meas].[AccountNo] = [HUacct].[AccountNo]

    WHERE ( [Meas].[Canceled]<>True AND [Meas].[MeasureGroupID]=3 ) GROUP
    BY [AuditorID];

    What I'm trying to get for the second column using
    Count([HUacct].[AccountNo])
    is a count of the number of unique AccountNos found in the table HUacct
    (created by the nested second SELECT command) that are matched into
    tblMeasureInstall. The HUacct table is grouped by AccountNo and has
    DISTINCTROW, so it should be one record per AccountNo (which a separate
    query confirms). But somehow after the join, my COUNT() comes up with
    the number of qualifying transactions for all accounts of a given
    AuditorID. The other results look OK. I've tried changing the JOIN
    type from INNER to OUTER and RIGHT and LEFT and haven't gotten
    anywhere. I'd appreciate any SQL-guru who could help.


  2. #2
    AA2e72E
    Guest

    RE: tricky SQL problem using VBA Excel to read Access File

    Without the data it is somewhat difficult to see what is going on; try your
    SQL after

    1. replacing INNER JOIN by a comma
    2. removing the ON clause and including its condition in the WHERE clause
    instead.

  3. #3

    Re: tricky SQL problem using VBA Excel to read Access File

    Thanks for the advice, but I already tried that. I managed to resolve
    this on my own by adding another layer of nesting. To restate the
    problem, there are items, orders and, customers. I am selecting
    orders that contain a certain item# in a certain date range (the inner
    most select) and then trying to count (among these orders) the number
    of unique orders by customer that have a different item#. My query
    could successsfully sum the the quantity and cost of this different
    item# for each customer but couldn't give me a unique count of orders
    that included the item -- instead the count counted the number of items
    meeting the criteria, not unique orders.

    I solved it by changing the above query to sum up the quantity, cost
    etc. by order (not by customer) and then nest all of it within another
    select statement that summed those order subtotals by customer, which
    could then properly count the number of orders. Whew!


  4. #4

    Re: tricky SQL problem using VBA Excel to read Access File

    Thanks for the advice, but I already tried that. I managed to resolve
    this on my own by adding another layer of nesting. To restate the
    problem, there are items, orders and, customers. I am selecting
    orders that contain a certain item# in a certain date range (the inner
    most select) and then trying to count (among these orders) the number
    of unique orders by customer that have a different item#. My query
    could successsfully sum the the quantity and cost of this different
    item# for each customer but couldn't give me a unique count of orders
    that included the item -- instead the count counted the number of items
    meeting the criteria, not unique orders.

    I solved it by changing the above query to sum up the quantity, cost
    etc. by order (not by customer) and then nest all of it within another
    select statement that summed those order subtotals by customer, which
    could then properly count the number of orders. Whew!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1