SQL Count argument problem (Access 2K3)

Topic

New Reply

Hello All!

I’m having a bit of a problem figuring out how to write a SQL statement to achieve a count of records. Below is the base SQL statement:

SELECT tblMain.Date
FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
GROUP BY tblMain.JobNumber, tblMain.Date
HAVING (((tblMain.JobNumber)=[Job Number]));

This query returns 50 records. How can I write a SQL statement that will return ’50’?
I’ve tried nesting a Subquery:

SELECT Count(tblMain.Date) FROM ……..
…WHERE tblMain.Date IN (SELECT……)

This returns ‘1301’ (obviously a little larger number than expected). Can anyone offer some advice?

Thanks so much!

Reply | Quote

Replies

Store the first query (the one that returns 50 records) as, say, qryDates.
Then create a query based on qryDates that returns the count:

SELECT Count(*) AS NumberOfDates FROM qryDates

Reply | Quote

Thanks for the tip!

The problem is, I’m accessing this through ASP.net, which doesn’t seem to recognize saved queries. It only likes to access tables directly, best I’ve been able to tell.

Any way to word this as one?

Thanks again.

Reply | Quote

Does this work?

SELECT COUNT(*) AS NumberOfDates FROM (SELECT tblMain.Date
FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
GROUP BY tblMain.JobNumber, tblMain.Date
HAVING (((tblMain.JobNumber)=[Job Number])))

Reply | Quote

It works perfectly Hans!!!

50 records: it runs like a gem.

Thank you sir!

Reply | Quote