Closect Date to KeyField

  • Thread starter Thread starter Darren
  • Start date Start date
D

Darren

I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a couple of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
FROM Permits;
 
You need the following tables:
TblProperty
PropertyID
Desc
Location
etc

TblOwner
OwnerID
FName
LName
etc

TblPropertyOwner
PropertyOwnerID
PropertyID
OwnerID
DateOfOwnership

TblPermit
PermitID
PropertyOwnerID
PermitNum
PermitDate
etc

To get the owner as of the date of the permit, create a query which includes
TblPermit, TblPropertyOwner and
TblOwner. Include the fields PermitDate and the calculated field
Owner:[FName] & " " & [LName]. Run this query to get the owners and dates
for all the permits. It will run in a flash for 7000 records.
 
This will however not give the desired result.

I want the query to return the list of permits, with the person who owned
the property as of the date of the permit, not all owners of the property.
Much more complicated.
My original post included a query that accomplishes this, but it is very
slow.

PC Datasheet said:
You need the following tables:
TblProperty
PropertyID
Desc
Location
etc

TblOwner
OwnerID
FName
LName
etc

TblPropertyOwner
PropertyOwnerID
PropertyID
OwnerID
DateOfOwnership

TblPermit
PermitID
PropertyOwnerID
PermitNum
PermitDate
etc

To get the owner as of the date of the permit, create a query which includes
TblPermit, TblPropertyOwner and
TblOwner. Include the fields PermitDate and the calculated field
Owner:[FName] & " " & [LName]. Run this query to get the owners and dates
for all the permits. It will run in a flash for 7000 records.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Darren said:
I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a couple of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
FROM Permits;
Click to expand...
Click to expand...
 
<< return the list of permits, with the person who owned the property as of
the date of the permit>>

That's exactly what the query will give you! Not all the owners!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Darren said:
This will however not give the desired result.

I want the query to return the list of permits, with the person who owned
the property as of the date of the permit, not all owners of the property.
Much more complicated.
My original post included a query that accomplishes this, but it is very
slow.

PC Datasheet said:
You need the following tables:
TblProperty
PropertyID
Desc
Location
etc

TblOwner
OwnerID
FName
LName
etc

TblPropertyOwner
PropertyOwnerID
PropertyID
OwnerID
DateOfOwnership

TblPermit
PermitID
PropertyOwnerID
PermitNum
PermitDate
etc

To get the owner as of the date of the permit, create a query which includes
TblPermit, TblPropertyOwner and
TblOwner. Include the fields PermitDate and the calculated field
Owner:[FName] & " " & [LName]. Run this query to get the owners and dates
for all the permits. It will run in a flash for 7000 records.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Darren said:
I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a
Click to expand...
couple
of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
FROM Permits;
Click to expand...
Click to expand...
Click to expand...
 
I guess you're not understanding, the dates will not match, so there is no
direct way to connect the permit date to the ownership date.

ex

Property Permit Property Owners
2/1/1997 Joe Smith
Date : 1/1/2000 ------------> 1/1/1999 Joe Blow
3/1/2001 Jim Smith

So I need the query to return that the owner on 1/1/2000 was Joe Blow
All I have is an Ownership Start Date, no End Date. Otherwise it would be
simple.


PC Datasheet said:
<< return the list of permits, with the person who owned the property as of
the date of the permit>>

That's exactly what the query will give you! Not all the owners!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Darren said:
This will however not give the desired result.

I want the query to return the list of permits, with the person who owned
the property as of the date of the permit, not all owners of the property.
Much more complicated.
My original post included a query that accomplishes this, but it is very
slow.

PC Datasheet said:
You need the following tables:
TblProperty
PropertyID
Desc
Location
etc

TblOwner
OwnerID
FName
LName
etc

TblPropertyOwner
PropertyOwnerID
PropertyID
OwnerID
DateOfOwnership

TblPermit
PermitID
PropertyOwnerID
PermitNum
PermitDate
etc

To get the owner as of the date of the permit, create a query which includes
TblPermit, TblPropertyOwner and
TblOwner. Include the fields PermitDate and the calculated field
Owner:[FName] & " " & [LName]. Run this query to get the owners and dates
for all the permits. It will run in a flash for 7000 records.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a couple
of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
FROM Permits;
Click to expand...
Click to expand...
Click to expand...
 
If you use the tables I suggested, the query WILL give you what you want.
The problem is that your tables are not designed correctly.

Darren said:
I guess you're not understanding, the dates will not match, so there is no
direct way to connect the permit date to the ownership date.

ex

Property Permit Property Owners
2/1/1997 Joe Smith
Date : 1/1/2000 ------------> 1/1/1999 Joe Blow
3/1/2001 Jim Smith

So I need the query to return that the owner on 1/1/2000 was Joe Blow
All I have is an Ownership Start Date, no End Date. Otherwise it would be
simple.


PC Datasheet said:
<< return the list of permits, with the person who owned the property as of
the date of the permit>>

That's exactly what the query will give you! Not all the owners!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Darren said:
This will however not give the desired result.

I want the query to return the list of permits, with the person who owned
the property as of the date of the permit, not all owners of the property.
Much more complicated.
My original post included a query that accomplishes this, but it is very
slow.

You need the following tables:
TblProperty
PropertyID
Desc
Location
etc

TblOwner
OwnerID
FName
LName
etc

TblPropertyOwner
PropertyOwnerID
PropertyID
OwnerID
DateOfOwnership

TblPermit
PermitID
PropertyOwnerID
PermitNum
PermitDate
etc

To get the owner as of the date of the permit, create a query which
includes
TblPermit, TblPropertyOwner and
TblOwner. Include the fields PermitDate and the calculated field
Owner:[FName] & " " & [LName]. Run this query to get the owners and dates
for all the permits. It will run in a flash for 7000 records.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a couple
of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
FROM Permits;
Click to expand...
Click to expand...
Click to expand...
 
Darren said:
I have a table that is a list of Properties, Owners and DateofOwnership
I have a table of Permits that only has a date to link to the Owners.
I must get the owner as of the date of the permit.

I was able to do this, but it is extremely slow (actually takes a couple of
minutes to return 7000 records). What would be a quicker way?

This is the query....

SELECT (SELECT [OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID) AND (P1.ODate)=
( SELECT max(oDate) FROM PropertyOwners WHERE
(((PropertyOwners.PropertyID)=P1.PropertyID) AND
((PropertyOwners.ODate)<=Permits.Idate)); )
) AS OwnerID, *
Click to expand...


Not sure about your tables and don't know if it will be
faster, but give something like this a try:

SELECT Permits.*,
(SELECT TOP 1 P1.[OwnerID]
FROM PropertyOwners P1
WHERE (P1.PropertyID=Permits.PropertyID)
AND (P1.ODate) >= Permits.Idate)
ORDER BY P1.ODate
) AS OwnerID
FROM Permits
 
Back
Top