The Last Record??

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

Guest

Employees send in reports every month,

I want to create a query that will show whether or not they have sent the
report for that month!
When they send the report in the reports are distinguished by the the fields
office and month.

What would be the best way to go about this?

Open to any suggestions


Thanks
 
I think a sorting option would be sufficient for this. Sort by Month and then
Sort by Office. Depending on your recordset you could set the Sorting to Desc.
 
That option is not feasible for my situation,

I want to able to lookdown at 50 employees and see who has sent there report
in for that month.

By therefore highlighting the missing reports

thanks
 
Assuming you have a table of offices and a table of reports, you should be
able to do this using a subquery in the where clause.

SELECT Offices.Office
FROM Offices
WHERE Office Not IN (
(SELECT Reports.Office
FROM Reports
WHERE Reports.Month = SomeValue)

WARNING: NOT IN is very slow with large datasets.

If you care to post more details on your table and field structures there
are more efficient ways to do this for LARGE data sets.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Two query solution.


Now use that saved query (qReportsReceived)

SELECT Offices.Office
FROM Offices LEFT JOIN qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null

If your field and table names have no spaces or other "Special" characters
you can oftencombine that into one query. As long as the subquery in the
from clause does NOT require any square brackets [] in it you should be able
to use.

SELECT Offices.Office
FROM Offices LEFT JOIN (
SELECT Reports.Office
FROM Reports
WHERE Reports.Month = SomeValue) as qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Whoops a little cut and paste error.

Two query solution. Make a query that identifies all those that HAVE sent
in the report and save ut as qReportsReceived
Something like:
SELECT Reports.Office
FROM Reports
WHERE Reports.Month = SomeValue

Now use that saved query (qReportsReceived) and you Offices table

SELECT Offices.Office
FROM Offices LEFT JOIN qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null

If your field and table names have no spaces or other "Special" characters
you can oftencombine that into one query. As long as the subquery in the
from clause does NOT require any square brackets [] in it you should be able
to use.

SELECT Offices.Office
FROM Offices LEFT JOIN (
SELECT Reports.Office
FROM Reports
WHERE Reports.Month = SomeValue) as qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null


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

John Spencer said:
Two query solution.


Now use that saved query (qReportsReceived)

SELECT Offices.Office
FROM Offices LEFT JOIN qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null

If your field and table names have no spaces or other "Special" characters
you can oftencombine that into one query. As long as the subquery in the
from clause does NOT require any square brackets [] in it you should be
able to use.

SELECT Offices.Office
FROM Offices LEFT JOIN (
SELECT Reports.Office
FROM Reports
WHERE Reports.Month = SomeValue) as qReportsReceived
ON Offices.Office = qReportsReceived.Office
WHERE qReportsReceived.Office is Null

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

SGTPEP said:
I will be working with a large dataset!

How do i improve this?
Click to expand...
Click to expand...
 
Back
Top