Day count calculation?

  • Thread starter Thread starter JayKay
  • Start date Start date
J

JayKay

I have a table with two fields, "PropertyAddress" and "SalesDate". The
"PropertyAddress" can be repeated, as the same property can sell
numerous times. There are over 250,000 records in the table. The first
field is defined as text, the second as date.
I want to add a third field which is the number of days between
"SalesDate" and "SalesDate" (and "SalesDate" and "SalesDate") for a
single "PropertyAddress".

Any ideas? Thanks, JayKay
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That calculated value should not be part of a table (goes against good
relational db design rules). Instead make it part of a query/View:

SELECT PropertyAddress, SalesDate,
(SELECT TOP 1 Datediff("d", SalesDate, A.SalesDate)
FROM Addresses WHERE PropertyAddress=A.PropertyAddress
AND SalesDate < A.SalesDate
ORDER BY SalesDate DESC) As [Duration (days)]
FROM Addresses As A
WHERE ... <criteria> ...

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQEFoEYechKqOuFEgEQLDSACgwpY6XYrkiIjq6+RiGcj2Jn9obYsAoLq2
dQq+vsvmwSrWosv4mtiWrUXh
=GvYk
-----END PGP SIGNATURE-----
 
I have a table with two fields, "PropertyAddress" and "SalesDate". The
"PropertyAddress" can be repeated, as the same property can sell
numerous times. There are over 250,000 records in the table. The first
field is defined as text, the second as date.
I want to add a third field which is the number of days between
"SalesDate" and "SalesDate" (and "SalesDate" and "SalesDate") for a
single "PropertyAddress".

Any ideas? Thanks, JayKay
Click to expand...

This was a fun one. For efficiency I'd put a nonunique Index on each
field (if there isn't one already).

Then create a Query by adding your table to the query grid and then
putting in a calculation: in a vacant Field cell type

DaysBetween: DateDiff("d", NZ(DMax("[SalesDate]", "[Sales]",
"PropertyAddress] = """ & [PropertyAddress] & """ AND [SalesDate] < #"
& [SalesDate[ & "#"), [SalesDate]), [SalesDate])

This will find the most recent sales date previous to the current
record's sales date (and if there is no such, i.e. for the first sale,
return the same salesdate), and calculate the days between. The days
between will be 0 for the first sale.
 
Back
Top