Problem counting

[Leslie Isaacs]

Hello All

I have a table called [locums sessions] which includes the fields 'locdate'
and 'locname' and key (autonumber) field 'locserial'.
'locname' is the name of a locum doctor, and 'locdate' is the date on which
that doctor worked a session.

I want a query that will return ALL the records corresponding to the doctors
who are working one or more session today - i.e. where 'locdate' = Date().
e.g. if there is a record where 'locname' = "Dr Green" and 'locdate' =
today's date, I want the query results to include ALL the records where
'locname' = "Dr Green". But if there are no records for "Dr Green" with
today's date, I don't want the querty results to include any of his records.

I have tried the following query (which is called [qry_count_locums]) ...

SELECT [locums sessions].locdate, [locums sessions].locname, DCount("[locums
sessions]![locserial]","[locums sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]") AS Expr1
FROM [locums sessions]
WHERE (((DCount("[locums sessions]![locserial]","[locums sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]"))>0));

.... but this gives me the message (twice) "The expression you entered as a
query parameter prodused this error 'Microsoft Office Access can't find the
name qry_count_locums!locname you entered in the expression".

I'm sure this can be done - but can't seem to get it!

Hope someone can help.
Many thanks
Les

 

[Andy Hull]

Hi Leslie

The following should give you what you want...

SELECT locdate, locname, count(*)
FROM [locums sessions]
WHERE locdate = date()
GROUP BY locdate, locname;

Your use of DCOUNT makes me wonder if you are trying to do something further
than the above so post back if you need more info.

Regards

Andy Hull

 

[John Spencer]

The following should fix your DCount function.

SELECT [locums sessions].locdate
, [locums sessions].locname
, DCount("*","[locums sessions]","locdate=date() and locname=""" & [locname]
& """) AS Expr1
FROM [locums sessions]
WHERE DCount("*","[locums sessions]","locdate=date() and locname=""" &
[locname] & """) > 0

I think the following will be more efficient.
SELECT LocDate, LocName
FROM [Locums Sessions]
WHERE LocName in
(SELECT T.LocName
FROM [Locums Sessions] as T
WHERE T.LocDate = Date())

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Leslie Isaacs]

Hello Andy

Thanks for your reply.
Your query gives me the number of sessions (records) for each doctor today.
What I want is ALL the sessions (records) for ANY doctor who has a session
(record) with a 'locdate' value of today.

Hope that explains it better!

Thanks again
Les

Hi Leslie

The following should give you what you want...

SELECT locdate, locname, count(*)
FROM [locums sessions]
WHERE locdate = date()
GROUP BY locdate, locname;

Your use of DCOUNT makes me wonder if you are trying to do something
further
than the above so post back if you need more info.

Regards

Andy Hull

Hello All

I have a table called [locums sessions] which includes the fields
'locdate'
and 'locname' and key (autonumber) field 'locserial'.
'locname' is the name of a locum doctor, and 'locdate' is the date on
which
that doctor worked a session.

I want a query that will return ALL the records corresponding to the
doctors
who are working one or more session today - i.e. where 'locdate' =
Date().
e.g. if there is a record where 'locname' = "Dr Green" and 'locdate' =
today's date, I want the query results to include ALL the records where
'locname' = "Dr Green". But if there are no records for "Dr Green" with
today's date, I don't want the querty results to include any of his
records.

I have tried the following query (which is called [qry_count_locums]) ...

SELECT [locums sessions].locdate, [locums sessions].locname,
DCount("[locums
sessions]![locserial]","[locums sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]") AS Expr1
FROM [locums sessions]
WHERE (((DCount("[locums sessions]![locserial]","[locums
sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]"))>0));

.... but this gives me the message (twice) "The expression you entered as
a
query parameter prodused this error 'Microsoft Office Access can't find
the
name qry_count_locums!locname you entered in the expression".

I'm sure this can be done - but can't seem to get it!

Hope someone can help.
Many thanks
Les

 

[Leslie Isaacs]

John

Many thanks for that.
In fact I needed to add a " just before the end bracket of each of the two
DCount expressions, but even then that query ran very slowly: your second
suggestion runs instantly!! How does it work?

Many thanks once again
Les

The following should fix your DCount function.

SELECT [locums sessions].locdate
, [locums sessions].locname
, DCount("*","[locums sessions]","locdate=date() and locname=""" &
[locname] & """) AS Expr1
FROM [locums sessions]
WHERE DCount("*","[locums sessions]","locdate=date() and locname=""" &
[locname] & """) > 0

I think the following will be more efficient.
SELECT LocDate, LocName
FROM [Locums Sessions]
WHERE LocName in
(SELECT T.LocName
FROM [Locums Sessions] as T
WHERE T.LocDate = Date())

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello All

I have a table called [locums sessions] which includes the fields
'locdate' and 'locname' and key (autonumber) field 'locserial'.
'locname' is the name of a locum doctor, and 'locdate' is the date on
which that doctor worked a session.

I want a query that will return ALL the records corresponding to the
doctors who are working one or more session today - i.e. where 'locdate'
= Date().
e.g. if there is a record where 'locname' = "Dr Green" and 'locdate' =
today's date, I want the query results to include ALL the records where
'locname' = "Dr Green". But if there are no records for "Dr Green" with
today's date, I don't want the querty results to include any of his
records.

I have tried the following query (which is called [qry_count_locums]) ...

SELECT [locums sessions].locdate, [locums sessions].locname,
DCount("[locums sessions]![locserial]","[locums sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]") AS Expr1
FROM [locums sessions]
WHERE (((DCount("[locums sessions]![locserial]","[locums
sessions]","[locums sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]"))>0));

... but this gives me the message (twice) "The expression you entered as
a query parameter prodused this error 'Microsoft Office Access can't find
the name qry_count_locums!locname you entered in the expression".

I'm sure this can be done - but can't seem to get it!

Hope someone can help.
Many thanks
Les

 

[John Spencer]

Your query using the DCount had to run the DCount function against the table
one time for every record that exists in the table. Since DCount function
runs a query to get the return value, you were running a LOT of queries to
get your answer.

The alternative I posted, runs two queries. First it runs the subquery
which returns a list of LocName that meet the criteria.
(SELECT T.LocName
FROM [Locums Sessions] as T
WHERE T.LocDate = Date())

Then the main query where it uses the list built by the sub query above to
get all records with a matching value.

SELECT LocDate, LocName
FROM [Locums Sessions]
WHERE LocName in (<< LIST OF Records GENERATED with names>>)

If the subquery returned Green, Jones, and Brown then you could think of
the final query as looking like

SELECT LocDate, LocName
FROM [Locums Sessions]
WHERE LocName = "Green" Or Locname = "Brown" Or LocName = "Jones"

That is my simplified idea of the concept of what happens.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John

Many thanks for that.
In fact I needed to add a " just before the end bracket of each of the two
DCount expressions, but even then that query ran very slowly: your second
suggestion runs instantly!! How does it work?

Many thanks once again
Les

The following should fix your DCount function.

SELECT [locums sessions].locdate
, [locums sessions].locname
, DCount("*","[locums sessions]","locdate=date() and locname=""" &
[locname] & """) AS Expr1
FROM [locums sessions]
WHERE DCount("*","[locums sessions]","locdate=date() and locname=""" &
[locname] & """) > 0

I think the following will be more efficient.
SELECT LocDate, LocName
FROM [Locums Sessions]
WHERE LocName in
(SELECT T.LocName
FROM [Locums Sessions] as T
WHERE T.LocDate = Date())

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello All

I have a table called [locums sessions] which includes the fields
'locdate' and 'locname' and key (autonumber) field 'locserial'.
'locname' is the name of a locum doctor, and 'locdate' is the date on
which that doctor worked a session.

I want a query that will return ALL the records corresponding to the
doctors who are working one or more session today - i.e. where 'locdate'
= Date().
e.g. if there is a record where 'locname' = "Dr Green" and 'locdate' =
today's date, I want the query results to include ALL the records where
'locname' = "Dr Green". But if there are no records for "Dr Green" with
today's date, I don't want the querty results to include any of his
records.

I have tried the following query (which is called [qry_count_locums])
...

SELECT [locums sessions].locdate, [locums sessions].locname,
DCount("[locums sessions]![locserial]","[locums sessions]","[locums
sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]") AS Expr1
FROM [locums sessions]
WHERE (((DCount("[locums sessions]![locserial]","[locums
sessions]","[locums sessions]![locdate]=date() and [locums
sessions]![locname]=[qry_count_locums]![locname]"))>0));

... but this gives me the message (twice) "The expression you entered as
a query parameter prodused this error 'Microsoft Office Access can't
find the name qry_count_locums!locname you entered in the expression".

I'm sure this can be done - but can't seem to get it!

Hope someone can help.
Many thanks
Les