Dupplicate Data

[mrbernh]

I am trying to create a report that will return a format for a time frame. My
program is for a landscaping business that needs to know who's lawn needs to
be cut. Since this a weekly task I need to create a list that can be given to
a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When
I run the querry and view the report it shows all visits rather then last
time they were cut. I used a expression of
<Date()-[Forms]![Work to be Completed]![DaySinceLast]

How do I eliminate the everything but the last day?

 

[John Spencer]

Hard to say without having some idea of your table structure.

The basic idea would be to construct a query that returns the latest service
date per customer and then run your query against that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Frank H]

Your expression : Date()-[Forms]![Work to be Completed]![DaySinceLast],
refers to a control on a form, which inidicates that you are doing a
calculated
control on a form, and expecting it to have some effect on a query.

If you want to affect the records selected for a report, you need to apply a
criteria on the query that is the recordset for the report, which is a
separate recordset from that of the form.

Making a lot of assumptions...
if you are trying to use the above expression as the criteria in your query,
you're probably having trouble because it will use only the single value of
the expression which derives from the form's active record at the instant
you run the report.

What would probably work better would be to create a calculated field in the
query you will use as the report recordset. The calculated field would
calculate the days since last service. Then you can put a range criteria on
that field.

Example calculated field:
DaysSinceLastServiceate()-[FieldnameOfDateOfLastService]
This should result in a number.

Example criteria for the calculated field:
Between 4 and 7

Tip: Do not try to put a criteria on a calculated field, which is itself
dependent upon some other calculated field.

 

[KARL DEWEY]

Try this --
SELECT [ClientID], Max([VisitDate] AS Last_Visit, (Date() - Max([VisitDate])
AS Days_Ago
FROM YourTable
GROUP BY [ClientID], (Date() - Max([VisitDate])
HAVING Date() - Max([VisitDate] >3
ORDER BY (Date() - Max([VisitDate]), [ClientID];

You may not need (Date() - Max([VisitDate]) in the GROUP BY.