query help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to do a count in a table where the ID field has many entries per
ID. I undersatnd that I need to use DISTINCT in my query, but its not working
properly. I just want to return the count, nothing else. Can someone help?

SELECT DISTINCT Count(dbo_Data.ID) AS CountOfID
FROM dbo_Data
GROUP BY dbo_Data.DateTime
HAVING (((dbo_Data.DateTime)=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)=[Forms]![frmCountReports]![txtenddate]));
 
Does this work for you? (I haven't had time to test it...)

SELECT Count([dbo_Data].[ID]) AS [CountOfID]
FROM [dbo_Data]
Where (((dbo_Data.DateTime)>=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)<[Forms]![frmCountReports]![txtenddate]));
 
The results are not distinct. It should return 400, but instead I get over
130000. Did I place DISTINCT in the wrong spot?

John Ortt said:
Does this work for you? (I haven't had time to test it...)

SELECT Count([dbo_Data].[ID]) AS [CountOfID]
FROM [dbo_Data]
Where (((dbo_Data.DateTime)>=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)<[Forms]![frmCountReports]![txtenddate]));



Brian said:
I'm trying to do a count in a table where the ID field has many entries per
ID. I undersatnd that I need to use DISTINCT in my query, but its not working
properly. I just want to return the count, nothing else. Can someone help?

SELECT DISTINCT Count(dbo_Data.ID) AS CountOfID
FROM dbo_Data
GROUP BY dbo_Data.DateTime
HAVING (((dbo_Data.DateTime)=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)=[Forms]![frmCountReports]![txtenddate]));
Click to expand...
Click to expand...
 
Sorry, I understand you now Brian

I will have a play and see if I can get it to work

Brian said:
The results are not distinct. It should return 400, but instead I get over
130000. Did I place DISTINCT in the wrong spot?

John Ortt said:
Does this work for you? (I haven't had time to test it...)

SELECT Count([dbo_Data].[ID]) AS [CountOfID]
FROM [dbo_Data]
Where (((dbo_Data.DateTime)>=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)<[Forms]![frmCountReports]![txtenddate]));



Brian said:
I'm trying to do a count in a table where the ID field has many
Click to expand...
entries
per
ID. I undersatnd that I need to use DISTINCT in my query, but its not working
properly. I just want to return the count, nothing else. Can someone help?

SELECT DISTINCT Count(dbo_Data.ID) AS CountOfID
FROM dbo_Data
GROUP BY dbo_Data.DateTime
HAVING (((dbo_Data.DateTime)=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)=[Forms]![frmCountReports]![txtenddate]));
Click to expand...
Click to expand...
Click to expand...
 
The only way I can seem to do it is with two separate queries.

One to group all the ID's and then another one which counts the Grouped ID's
from the first query...

Was there a particular reason you wanted to do it in a single step?


John Ortt said:
Sorry, I understand you now Brian

I will have a play and see if I can get it to work

Brian said:
The results are not distinct. It should return 400, but instead I get over
130000. Did I place DISTINCT in the wrong spot?

John Ortt said:
Does this work for you? (I haven't had time to test it...)

SELECT Count([dbo_Data].[ID]) AS [CountOfID]
FROM [dbo_Data]
Where (((dbo_Data.DateTime)>=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)<[Forms]![frmCountReports]![txtenddate]));



I'm trying to do a count in a table where the ID field has many entries
per
ID. I undersatnd that I need to use DISTINCT in my query, but its not
working
properly. I just want to return the count, nothing else. Can someone help?

SELECT DISTINCT Count(dbo_Data.ID) AS CountOfID
FROM dbo_Data
GROUP BY dbo_Data.DateTime
HAVING
Click to expand...
Click to expand...
(((dbo_Data.DateTime)=[Forms]![frmCountReports]![txtstartdate]
And
(dbo_Data.DateTime)=[Forms]![frmCountReports]![txtenddate]));
Click to expand...
Click to expand...
Click to expand...
 
One step or two, no matter to me.

John Ortt said:
The only way I can seem to do it is with two separate queries.

One to group all the ID's and then another one which counts the Grouped ID's
from the first query...

Was there a particular reason you wanted to do it in a single step?


John Ortt said:
Sorry, I understand you now Brian

I will have a play and see if I can get it to work

Brian said:
The results are not distinct. It should return 400, but instead I get over
130000. Did I place DISTINCT in the wrong spot?

:

Does this work for you? (I haven't had time to test it...)

SELECT Count([dbo_Data].[ID]) AS [CountOfID]
FROM [dbo_Data]
Where (((dbo_Data.DateTime)>=[Forms]![frmCountReports]![txtstartdate] And
(dbo_Data.DateTime)<[Forms]![frmCountReports]![txtenddate]));



I'm trying to do a count in a table where the ID field has many entries
per
ID. I undersatnd that I need to use DISTINCT in my query, but its not
working
properly. I just want to return the count, nothing else. Can someone help?

SELECT DISTINCT Count(dbo_Data.ID) AS CountOfID
FROM dbo_Data
GROUP BY dbo_Data.DateTime
HAVING
Click to expand...
(((dbo_Data.DateTime)=[Forms]![frmCountReports]![txtstartdate]
And
(dbo_Data.DateTime)=[Forms]![frmCountReports]![txtenddate]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top