compare field across records

[Devon S]

I am setting up a database to track service visits to customers. I need to
write a query to compute days between each visit. Since each visit is a
seporate record in the table, I am lost as to how to compute this data!

(Background - This will be used to generate a report for customers that went
too long between visits. This is for a service company, and customers pay for
visits each 7, 10 or 14 days so we need to make sure customers are visited.)

 

[MGFoster]

I am setting up a database to track service visits to customers. I need to
write a query to compute days between each visit. Since each visit is a
seporate record in the table, I am lost as to how to compute this data!

(Background - This will be used to generate a report for customers that went
too long between visits. This is for a service company, and customers pay for
visits each 7, 10 or 14 days so we need to make sure customers are visited.)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT CustomerID, VisitDate As LastVisit,
DMax("VisitDate","Visits","VisitDate < " & V.VisitDate & " AND
CustomerID = " & V.CustomerID) As PrevVisit
FROM Visits As V
WHERE <criteria>

But, w/o knowing the design of your table(s) I'm not sure.

Basically, you need to refer to the previous visit (VisitDate < current
VisitDate) in a separate subquery (in the above example I used a Domain
Aggregate function).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxS8xYechKqOuFEgEQKSIgCgrKUv68jN0vW5RfZ85U9HYe6kCkUAn39T
pFpWCTSumQQa6cnrIRjPmEyB
=UsNP
-----END PGP SIGNATURE-----

 

[Devon S]

Thanks. That looks like it will work great.

I am sure I should know this... but I can't get it to work. Access seems to
be having a problem with the Criteria part of the DMax. I modified the table
to be Visits with field names that match yours and I even copied and pasted
your SQL but the PrevVisit is "#Error". I tried it with only the date
portion of the criteria and got null. with only the CustomerID I got another
#Error. I tried it without the & and extra " (which I didn't understand...
but such is Access) and it told me it couldn't find the field, which must be
why they were there...?

Please let me know where I have gone wrong. THANKS!

 

[MGFoster]

You'll have to post the designs (column names, PKs & data types) of your
table(s) that you are using in the query; otherwise, I can only guess.
Also, post the SQL of the query you are trying to run. The important
things to know is the ID of the customer and the visit dates.