Need help with Distinct Count in subquery

[BWiddoes]

What I want is to summarize the salestran.[Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.

Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." I can get all the summaries and such, I just need help including the distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU. Please help :banghead:

SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];

 

[plog]

Divide and conquer. Don't try and achieve this all at once. Make a query to limit your data the specified date range and call it 'SKU_Totals_sub1'. Then use that query to build 2 other queries to get the Price/Cost totals ('SKU_Totals_sub2') and unique customers ('SKU_Totals_sub3). Then join those 2 queries to get the final results.

Bonus tip: that last query is going to require a LEFT JOIN.

 

[BWiddoes]

Hi Plog. I really appreciate you taking the time to reply. I completely understand your solution and know how to get to it, but I was hoping to be able to knock this out in one go. I have some complex operations I am building and I want to limit the amount of make tables and queries to query on separate queries as it eventually slows the reporting to a crawl.

I want to figure out how to get this done in one query. I can do the distinct count by itself using a subquery, but once I throw that into my main query it doesnt operate the way I am wanting.

Any continued support is much appreciated as well as new solutions, provided it is accomplished in one query.

Cheers

 

[plog]

Technically, you have 2 quereies in your solution. Every SELECT clause counts as a new query, jsut because you are trying to jam your SQL into one Access object doesn't make it a single query. Also, your SQL is pretty hard to follow--the subquery is in the SELECT clause of the main query, which is unconventional as well. Subqueries usually appear in a JOIN clause.

I advise you to follow my advice: Do this in multiple sub-queries. You can't get your method working, so how are you certain my way will be slower?

Also, once you do implement my method, you can jam all the SQL together once you get it working.

A:

SELECT FiscalYear AS FY, SUM(Sales) AS TotalSales FROM SalesTable GROUP BY FiscalYear;

B:

SELECT FiscalYear, SalesPerson, Sales, (Sales/TotalSales) AS PercentOfTotalSales FROM SalesTable
INNER JOIN A ON SalesTable.FiscalYear = A.FiscalYear;

The above represents my method--two different queries. But once you have that you can combine the 2 sets of SQL by copying A and pasting it where A occurs in B:

C:

SELECT FiscalYear, SalesPerson, Sales, (Sales/TotalSales) AS PercentOfTotalSales FROM SalesTable
INNER JOIN (SELECT FiscalYear AS FY, SUM(Sales) AS TotalSales FROM SalesTable GROUP BY FiscalYear) ON FY = FiscalYear;

 

[BWiddoes]

Hi Plog, your suggestion on the JOIN to subquery set me on the right track. I got it knocked out, jammed into one object.

Main select pulling fields from the two tables my subqueries create

SELECT cd.[SKU Code/Number], Count(cd.[Customer Code/Number]) AS [Customer Count], dc.[Cost Summary], dc.[Price Summary]

First subquery used to get the distinct SKU and Customer Code and create table named cd. Customer code then counted in the main select statement

FROM (SELECT DISTINCT [SKU Code/Number], [Customer Code/Number] FROM Salestran WHERE Salestran.[Transaction Date]>=#11/1/2014#) AS cd


Inner join into another subquery which also creates a table, this one named DC. I had to summarize the transaction data in the subquery instead of pulling individual line items. Trying to summarize in the main select caused my count and summary data to be off. Join tables on the SKU

INNER JOIN (SELECT Salestran.[SKU Code/Number], SUM(Salestran.[Extended Cost]) AS [Cost Summary], SUM(Salestran.[Extended Price]) AS [Price Summary] FROM Salestran WHERE Salestran.[Transaction Date]>=#11/1/2014# GROUP BY Salestran.[SKU Code/Number]) AS dc ON dc.[SKU Code/Number] = cd.[SKU Code/Number]

Group by SKU, Cost and Price summaries since the transaction data was summarized in a subquery

GROUP BY cd.[SKU Code/Number], dc.[Cost Summary], dc.[Price Summary];

Now in theory, I can go and start creating a bunch of LEFT joins to subqueries that distinct count other records in the tables I have here in the office. I hope this helps anyone trying to do the same or similar query.

Cheers and thanks Plog for the assist.