query question

[jscriptor09]

Hi



I have a table that looks like this:
Store ---- Dept -----Item
A ZZ Apple
A ZZ Orange
A YY Apple
A KK Apple
B ZZ Apple
B WW Orange
B WW Pear
B WW Pear
..

I am trying to write a query to return how many items (count) in each Store/Dept.
So, for Atore A, DEPT ZZ, the Query should return 2
For Store b, Dept WWW, the query should return 3

I am stuck on getting the desired count. I grouped by Store and Dept but can't figure out the count part as mentioned above.


Any SQL statement help or hint on how to write this would be greatly appreciated.

thanks

[Parsonshere]

SELECT tblItem.fldStore, tblItem.fldDept, Count(tblItem.fldItem) AS fldCnt
FROM tblItem
GROUP BY tblItem.fldStore, tblItem.fldDept;

[June7]

Or build a report using Grouping & Sorting with aggregate calc in group footer.

[jscriptor09]

Hi

thanks for the reply. I appreciate it.

I tried this approach
SELECT tblItem.fldStore, tblItem.fldDept, Count(tblItem.fldItem) AS fldCnt
FROM tblItem
GROUP BY tblItem.fldStore, tblItem.fldDept;

and it does return the count. I just need to tweek to get "Distinct" Item. So for this example

Store ---- Dept -----Item
A ZZ Apple
A ZZ Orange
A YY Apple
A KK Apple
B ZZ Apple
B WW Orange
B WW Pear
B WW Pear

If I query for Item in Store B, Dept WW, the query should return 2 item categories.
B ww Orange
and
B ww Pear

Thanks for the help.

[June7]

Join the aggregate query to the table and use DISTINCT keyword or do another GROUP BY.

Orange and Pear will show the same count value.

[jscriptor09]

Hi

Thanks ..can you please provide an example sql statement setup.

thanks

[June7]

Just use the query designer. Drag the table and the query into the query builder, set a join link on Store and Dept. Drag fields to the grid. Choose DISTINCT from the query properties dialog or select TOTALS from the ribbon. Basic Access query building.

[jscriptor09]

I did that already ... in a bit of different flavor. But I am looking for a single sql statement that I can stick in a VBA code. Here is what I did usinf the 2 steps approach:

Query #1 : grouped by three fields
Query #2 : used Query #1 and Did a Count on the item field.

This provided the values right.

However, I am looking for the single SQL statement construct that I can put in a VBA logic. It is a select from a select construct that I am not getting right.

Thanks

[June7]

Now open queries in SQL View. Copy paste the SQL of Query1 into Query2 so the result is like:


.... (sql for query 1 without semi-colon here) AS Query1 ...


That's how I build nested queries.


Show your SQL statements for analysis.

[Parsonshere]

Not sure I understand, but look like to me this:
SELECT tblItem.fldStore, tblItem.fldDept, tblItem.fldItem, Count(tblItem.fldItem) AS fldCnt
FROM tblItem
GROUP BY tblItem.fldStore, tblItem.fldDept, tblItem.fldItem;

[June7]

That is an SQL statement but is not a nested construct which would look like:

SELECT <fields> FROM (SELECT <fields> FROM table/query ...) AS Query1 ...;