Syntax error, please help

[jinglin]

for my Access database(97. I have a field called ID, which has multiple values:

0-007
0-007
1-123
1-123
5-456
5-456

I need to get the total distinct counts of ID for my report(here I should get 3) so I run a SQL statment:

select count(distinct [ID])from myTable;

But all I got is a syntax error.

Please help!
Thanks!

 

[OnTheFly]

I am not sure how an where you are trying to get this information. If you simply want to display it when you run a query you will probably have to create a query that first pulls only the unique IDs and the use that query to do your count from.

ie.

"SELECT DISTINCT ID FROM MyTable" as a query named UniqueIDs

then "SELECT COUNT(ID) AS TotalUnique FROM UniqueIDs" will give you the actual count.

Hope this helps.

OnTheFly

 

[jinglin]

but how can I show two queries in a text box which has to be shown on the report. Is there one SQL statement to solve the problem?

Thanks!

 

[OnTheFly]

Sorry I was not clearer with my response. The way I suggested required that you save the first query and then reference it but you can do it like so also

SELECT Count(ID) AS TotalDistict
FROM (SELECT DISTINCT ID
FROM MyTable)

Hope this helps.

OnTheFly

 

[jinglin]

I did as you wrote me as following:

SELECT count([ID]) AS TotalIDs
FROM (select distinct [ID] from myTable);

but I got a syntax error saying: Syntax error in From clause.

Thanks!

 

[SuicidED]

First query
SELECT Table1.ID
FROM Table1
GROUP BY Table1.ID;

then Second query
SELECT Count(Query4.ID) AS CountOfID
FROM Query4;

 

[jinglin]

but how can I combine those two queries into one statement? Here, it doesn't work for me with two queries.
Thanks anyway.

 

[OnTheFly]

Try this. It may be that you were missing a space between TotalIDs and From in the first query.

SELECT count([ID]) AS TotalIDs FROM (select distinct [ID] from myTable)

I would copy it from here and paste it to the query Designer.




Hope this helps.

OnTheFly

 

[lespaul]

I don't think Access 97 lets you use a sub-query as the FROM clause.

jinglin -

two queries in a text box which has to be shown on the report

are you showing the actual text of the query in the report?

just because you are creating two queries to get the data, you only have to call/reference the second one to get the information you need.

Create the query:

SELECT DISTINCT ID AS ID FROM MyTable

Save it as CountOfDistinct

then create a second query:

SELECT COUNT(ID) FROM CountOfDistinct

Now all you have to do is call the second query, it will run the first query and return your answer.




Leslie

 

[AppStaff]

I believe your problem is your trying to both count and list from the same field. Add the Called ID twice. click on totals and add count in the second one. the first should say group by. This should give you your desired results. Copy the sql from the sql view if you need it.

 

[jinglin]

you're right, Leslie. Access 97 doesn't have such a functionality.
If I did as ontheFly told me in SQL server, it worked fine:

SELECT count([ID]) AS TotalIDs FROM (select distinct [ID] from myTable)

Thanks for your help.

 

[PHV]

Anyway the correct syntax in access is:
SELECT count([ID]) AS TotalIDs FROM (select distinct [ID] from myTable) [highlight]SomeAlias[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244