How to SQL this

[Joska Paszli]

Dear reader
I have 1 table with several data, example
-----------------------------

tblData
Acol1   Acol2   Acol3   
hey      A        1.5
hey      A        4.5
hey      A        4.5
jo       C        1.5
jo       C        6
jo       C        6 
hey      B        2
hey      B        5
hey      B        8
hey      B        2
hey      B        5

------------------------------
I want an output like this where based on the key combination of Acol1 AND Acol2 the unique/distinct values of Acol3 is counted, Acol4counts the number of times Acol1 and Acol2 is present AFTER Acol3 having all unique values. SO how to SQL this?

Acol1   Acol2   Acol3   Acol4
hey      A        1.5     2
hey      A        4.5     2
jo       C        1.5     2
jo       C        6       2
hey      B        2       3
hey      B        5       3
hey      B        8       3

Anyone have any idea? I made already some complex sql statements for vb2010 but somehow this i cant accomplish... i want to achieve the above thing into one SQL statement for use in a vb2010 programm
thx for your attention

[June7]

query1
SELECT DISTINCT tblData.Acol1, tblData.Acol2, tblData.Acol3 FROM tblData;

Options:

1. Use that query as the source for a report using Grouping & Sorting features. Create a group on expression that concatenates Acol1 and Acol2 and a textbox in group footer with expression: Count("*"). The report will allow display of detail records as well as summary calcs.

2. multiple queries
query2
SELECT Query1.Acol1, Query1.Acol2, Count(Query1.Acol3) AS CountOfAcol3 FROM Query1 GROUP BY Query1.Acol1, Query1.Acol2;
query3
SELECT Query1.Acol1, Query1.Acol2, Query1.Acol3, Query2.CountOfAcol3
FROM Query2 INNER JOIN Query1 ON (Query2.Acol2 = Query1.Acol2) AND (Query2.Acol1 = Query1.Acol1);
all-in-one
SELECT Query1.Acol1, Query1.Acol2, Query1.Acol3, Query2.CountOfAcol3
FROM (SELECT Query1.Acol1, Query1.Acol2, Count(Query1.Acol3) AS CountOfAcol3 FROM Query1 GROUP BY Query1.Acol1, Query1.Acol2) AS
Query2 INNER JOIN (SELECT DISTINCT tblData.Acol1, tblData.Acol2, tblData.Acol3 FROM tblData) As Query1 ON (Query2.Acol2 = Query1.Acol2) AND (Query2.Acol1 = Query1.Acol1);

[Joska Paszli]

mmm looks complicated, too complicated for me, this is what works on SqlServer2012 bit appearantly not on access2007

SELECT
A.Name,
A.Value,
(select count(1)from
(
SELECT Name,Value,count(1) number FROM ByteTypeTest
whereName= A.Name
groupbyName,Value
)as B
groupbyName
)Count
FROM ByteTypeTest A
groupby A.Name,A.Value
order by A.Name

[orange]

Have you tried the format you showed in #3 with the table you mention in post #1?

[Joska Paszli]

yup closest i got (at least i think) was this

I skipped the 3rd count statement cuz i have no clue how to implement it


cmd.CommandText = "SELECT A.Postcode, A.Hoogte, (select count(tblBestemming2WNP.hoogte) from " _
& "(SELECT Postcode,Hoogte,count(tblBestemming2WNP.hoogte) FROM tblBestemming2WNP " _
& "where Postcode = A.Postcode " _
& "group by Postcode,Hoogte) as B " _
& "group by Postcode) as Teller " _
& "FROM tblBestemming2WNP A " _
& "group by A.Postcode, A.Hoogte order by A.Postcode, A.Hoogte;"

[June7]

The SQL I provided works with the sample data. If you had provided your actual table structure, my posted SQL would have used that.

Why don't you start by building the 3 queries I suggested? Use the Access query builder. Then if you want the all-in-one, use SQL View to copy/paste the SQL of nested queries. That's how I build complex SQL statements I want to use in VBA.

Try copy/paste the all-in-one query into SQL view of Access query builder and substitute your actual table and field names. If the results are what you want, then copy/paste the SQL into VBA.