Show if record count more than

  • Thread starter Thread starter louweb
  • Start date Start date
L

louweb

I have a database recording staff absences from meetings. My query shows the
staff name, meeting date, meeting type and reason for absence. My query
allows for the user to enter a date to only show absences from that date on.
My report based on that query shows every absence from that date grouped by
staff member and counts the total absences for that staff member. I need to
only show staff member and details when there have been 3 or more absences.
Does anyone know if I do this through my query or through my report (where I
have a count per person) and how do I do it?
 
There are a couple of ways to approach this.

One approach is to use a subquery to filter the absences to those who have
had more than 3. This example assumes your table is named tblAbsence, and
has fields named AbsenceID, StaffID, and MeetingDate:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
WHERE [MeetingDate] >= [FromWhen];

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html
 
Hi Allen,

Thanks very much for taking the time to work out the SQL for me. I haven't
used SQL before, but I put it into my query as a sub query through the Zoom
function on the next field. I get an error: "The expression you entered
contained invalid syntax. You may have entered an operand without an
operator" Does anything obviously jump out at you?

Thanks very much,
Louise

Allen Browne said:
There are a couple of ways to approach this.

One approach is to use a subquery to filter the absences to those who have
had more than 3. This example assumes your table is named tblAbsence, and
has fields named AbsenceID, StaffID, and MeetingDate:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
WHERE [MeetingDate] >= [FromWhen];

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

louweb said:
I have a database recording staff absences from meetings. My query shows
the
staff name, meeting date, meeting type and reason for absence. My query
allows for the user to enter a date to only show absences from that date
on.
My report based on that query shows every absence from that date grouped
by
staff member and counts the total absences for that staff member. I need
to
only show staff member and details when there have been 3 or more
absences.
Does anyone know if I do this through my query or through my report (where
I
have a count per person) and how do I do it?
Click to expand...
Click to expand...
 
Sorry: 2 WHERE clauses. The 2nd one should be AND:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
AND [MeetingDate] >= [FromWhen];

In the link I posted for subqueries, there's a little side-box explaining
the basic query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

louweb said:
Hi Allen,

Thanks very much for taking the time to work out the SQL for me. I
haven't
used SQL before, but I put it into my query as a sub query through the
Zoom
function on the next field. I get an error: "The expression you entered
contained invalid syntax. You may have entered an operand without an
operator" Does anything obviously jump out at you?

Thanks very much,
Louise

Allen Browne said:
There are a couple of ways to approach this.

One approach is to use a subquery to filter the absences to those who
have
had more than 3. This example assumes your table is named tblAbsence, and
has fields named AbsenceID, StaffID, and MeetingDate:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
WHERE [MeetingDate] >= [FromWhen];

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

louweb said:
I have a database recording staff absences from meetings. My query
shows
the
staff name, meeting date, meeting type and reason for absence. My
query
allows for the user to enter a date to only show absences from that
date
on.
My report based on that query shows every absence from that date
grouped
by
staff member and counts the total absences for that staff member. I
need
to
only show staff member and details when there have been 3 or more
absences.
Does anyone know if I do this through my query or through my report
(where
I
have a count per person) and how do I do it?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all your help, Allen. Unfortunately I think SQL is beyond my
ability, so I'll have to give this query a miss.
Thanks again for your time,
Louise

Allen Browne said:
Sorry: 2 WHERE clauses. The 2nd one should be AND:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
AND [MeetingDate] >= [FromWhen];

In the link I posted for subqueries, there's a little side-box explaining
the basic query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

louweb said:
Hi Allen,

Thanks very much for taking the time to work out the SQL for me. I
haven't
used SQL before, but I put it into my query as a sub query through the
Zoom
function on the next field. I get an error: "The expression you entered
contained invalid syntax. You may have entered an operand without an
operator" Does anything obviously jump out at you?

Thanks very much,
Louise

Allen Browne said:
There are a couple of ways to approach this.

One approach is to use a subquery to filter the absences to those who
have
had more than 3. This example assumes your table is named tblAbsence, and
has fields named AbsenceID, StaffID, and MeetingDate:

PARAMETERS [FromWhen] DateTime;
SELECT [AbsenceID], [StaffID], [MeetingDate]
FROM tblAbsence
WHERE (SELECT Count([AbsenceID]) AS HowMany
FROM tblAbsence AS Dupe
WHERE Dupe.[StaffID] = tblAbsence.[StaffID]
AND Dupe.[MeetingDate] >= [FromWhen]) > 3
WHERE [MeetingDate] >= [FromWhen];

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

I have a database recording staff absences from meetings. My query
shows
the
staff name, meeting date, meeting type and reason for absence. My
query
allows for the user to enter a date to only show absences from that
date
on.
My report based on that query shows every absence from that date
grouped
by
staff member and counts the total absences for that staff member. I
need
to
only show staff member and details when there have been 3 or more
absences.
Does anyone know if I do this through my query or through my report
(where
I
have a count per person) and how do I do it?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top