How do I select the oldest/newest record in Access query?

M

mgrashot

I am using a compound query, meaning the first query selects the main record
set (such as the specific insurance policies.) The second query includes the
first query but then links to a table of applications. These applications
can be modified throughout a policy period, so there are multiple "versions"
of fields in the applications table - each with its own date & time stamp.
If an application was modified, my query is returning both rows for that
policy (with the old and new version of the application field.) I would like
the query to only return the application record with the maxium date/time
stamp. Any help??
 
M

mgrashot

I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record for each.

Exactly where do I edit the query?
 
M

Michel Walsh

You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



mgrashot said:
I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record for
each.

Exactly where do I edit the query?

Michel Walsh said:
Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP
Click to expand...
Click to expand...
 
M

mgrashot

Q1 selects several policies (9,000)

I then want to append to each of those records the most recent version of
the AgencyNotation field (as determined by the ANotID) from the notation
table and the CommAddOtherInt field (as determined by the CAOIID) from the
other interest table.

Just trying clarify.

Thanks for your patience.


Michel Walsh said:
You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



mgrashot said:
I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record for
each.

Exactly where do I edit the query?

Michel Walsh said:
Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects the main
record
set (such as the specific insurance policies.) The second query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are multiple
"versions"
of fields in the applications table - each with its own date & time
stamp.
If an application was modified, my query is returning both rows for
that
policy (with the old and new version of the application field.) I
would
like
the query to only return the application record with the maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get the
right data.



Vanderghast, Access MVP


mgrashot said:
Q1 selects several policies (9,000)

I then want to append to each of those records the most recent version of
the AgencyNotation field (as determined by the ANotID) from the notation
table and the CommAddOtherInt field (as determined by the CAOIID) from the
other interest table.

Just trying clarify.

Thanks for your patience.


Michel Walsh said:
You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



mgrashot said:
I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects the main
record
set (such as the specific insurance policies.) The second query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are multiple
"versions"
of fields in the applications table - each with its own date & time
stamp.
If an application was modified, my query is returning both rows for
that
policy (with the old and new version of the application field.) I
would
like
the query to only return the application record with the maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

mgrashot

I think I'm so close. Please be patient and help me through the end of this.

Here is the second query - BX Apps B. (The first query selects the policies
based on dates and then the second query appends the other fields to those
selected policies.)

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CommAddOtherInt ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId)
LEFT JOIN dbo_AFW_CPrem ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId
ORDER BY [BX Apps A].PolNo;

Here is the third query per your instructions - BX Apps C . . .

SELECT PolID AS Expr1, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the fourth query per your instructions - BX Apps D . . .

SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID)
ORDER BY [BX Apps B].PolID;

The problem is that when I execute the fourth query, I get the following
error . . .
The specified field "[BX Apps B].PolID" could refer to more than one table
listed in the FROM clause of your SQL statement.

Any suggestions??





Michel Walsh said:
Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get the
right data.



Vanderghast, Access MVP


mgrashot said:
Q1 selects several policies (9,000)

I then want to append to each of those records the most recent version of
the AgencyNotation field (as determined by the ANotID) from the notation
table and the CommAddOtherInt field (as determined by the CAOIID) from the
other interest table.

Just trying clarify.

Thanks for your patience.


Michel Walsh said:
You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects the main
record
set (such as the specific insurance policies.) The second query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are multiple
"versions"
of fields in the applications table - each with its own date & time
stamp.
If an application was modified, my query is returning both rows for
that
policy (with the old and new version of the application field.) I
would
like
the query to only return the application record with the maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

You are using MS SQL Server as database engine? If so, remove the *, in
SELECT [BX Apps B].*, and list explicitly the fields you want, in that
latest query.


Vanderghast, Access MVP


mgrashot said:
I think I'm so close. Please be patient and help me through the end of
this.

Here is the second query - BX Apps B. (The first query selects the
policies
based on dates and then the second query appends the other fields to those
selected policies.)

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CommAddOtherInt ON [BX Apps A].PolId =
dbo_AFW_CommAddOtherInt.PolId)
LEFT JOIN dbo_AFW_CPrem ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId
ORDER BY [BX Apps A].PolNo;

Here is the third query per your instructions - BX Apps C . . .

SELECT PolID AS Expr1, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the fourth query per your instructions - BX Apps D . . .

SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID)
ORDER BY [BX Apps B].PolID;

The problem is that when I execute the fourth query, I get the following
error . . .
The specified field "[BX Apps B].PolID" could refer to more than one table
listed in the FROM clause of your SQL statement.

Any suggestions??





Michel Walsh said:
Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get the
right data.



Vanderghast, Access MVP


mgrashot said:
Q1 selects several policies (9,000)

I then want to append to each of those records the most recent version
of
the AgencyNotation field (as determined by the ANotID) from the
notation
table and the CommAddOtherInt field (as determined by the CAOIID) from
the
other interest table.

Just trying clarify.

Thanks for your patience.


:

You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should
be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record
for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects the
main
record
set (such as the specific insurance policies.) The second query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are multiple
"versions"
of fields in the applications table - each with its own date &
time
stamp.
If an application was modified, my query is returning both rows
for
that
policy (with the old and new version of the application field.)
I
would
like
the query to only return the application record with the maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

mgrashot

I'm actually using Access but just viewed it in SQL view.

Michel Walsh said:
You are using MS SQL Server as database engine? If so, remove the *, in
SELECT [BX Apps B].*, and list explicitly the fields you want, in that
latest query.


Vanderghast, Access MVP


mgrashot said:
I think I'm so close. Please be patient and help me through the end of
this.

Here is the second query - BX Apps B. (The first query selects the
policies
based on dates and then the second query appends the other fields to those
selected policies.)

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CommAddOtherInt ON [BX Apps A].PolId =
dbo_AFW_CommAddOtherInt.PolId)
LEFT JOIN dbo_AFW_CPrem ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId
ORDER BY [BX Apps A].PolNo;

Here is the third query per your instructions - BX Apps C . . .

SELECT PolID AS Expr1, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the fourth query per your instructions - BX Apps D . . .

SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID)
ORDER BY [BX Apps B].PolID;

The problem is that when I execute the fourth query, I get the following
error . . .
The specified field "[BX Apps B].PolID" could refer to more than one table
listed in the FROM clause of your SQL statement.

Any suggestions??





Michel Walsh said:
Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get the
right data.



Vanderghast, Access MVP


Q1 selects several policies (9,000)

I then want to append to each of those records the most recent version
of
the AgencyNotation field (as determined by the ANotID) from the
notation
table and the CommAddOtherInt field (as determined by the CAOIID) from
the
other interest table.

Just trying clarify.

Thanks for your patience.


:

You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it should
be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest record
for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects the
main
record
set (such as the specific insurance policies.) The second query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are multiple
"versions"
of fields in the applications table - each with its own date &
time
stamp.
If an application was modified, my query is returning both rows
for
that
policy (with the old and new version of the application field.)
I
would
like
the query to only return the application record with the maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Then, it may be possible that the query [BX Apps B] lists the field PolID
twice? Should list that field just once. Note that in the query you posted,
I don't see it *at all* (in the SELECT clause):


SELECT DISTINCT [BX Apps A].CustNo,
[BX Apps A].LastName,
[BX Apps A].PolTypeLOB,
[BX Apps A].PolNo,
[BX Apps A].PolEffDate,
[BX Apps A].PolExpDate,
[BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description AS Notation,
dbo_AFW_CPrem.vLimit1,
dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2,
dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2,
dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State,
dbo_AFW_CommAddOtherInt.Zip
FROM ...


so the error message sounds strange to me since [BX Apps B] does not have
any PolID field (as per the query you posted, or I am wrong about which
query is "[BX Apps B]" ).


Vanderghast, Access MVP


mgrashot said:
I'm actually using Access but just viewed it in SQL view.

Michel Walsh said:
You are using MS SQL Server as database engine? If so, remove the *, in
SELECT [BX Apps B].*, and list explicitly the fields you want, in that
latest query.


Vanderghast, Access MVP


mgrashot said:
I think I'm so close. Please be patient and help me through the end of
this.

Here is the second query - BX Apps B. (The first query selects the
policies
based on dates and then the second query appends the other fields to
those
selected policies.)

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CommAddOtherInt ON [BX Apps A].PolId =
dbo_AFW_CommAddOtherInt.PolId)
LEFT JOIN dbo_AFW_CPrem ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId
ORDER BY [BX Apps A].PolNo;

Here is the third query per your instructions - BX Apps C . . .

SELECT PolID AS Expr1, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the fourth query per your instructions - BX Apps D . . .

SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID)
ORDER BY [BX Apps B].PolID;

The problem is that when I execute the fourth query, I get the
following
error . . .
The specified field "[BX Apps B].PolID" could refer to more than one
table
listed in the FROM clause of your SQL statement.

Any suggestions??





:

Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get
the
right data.



Vanderghast, Access MVP


Q1 selects several policies (9,000)

I then want to append to each of those records the most recent
version
of
the AgencyNotation field (as determined by the ANotID) from the
notation
table and the CommAddOtherInt field (as determined by the CAOIID)
from
the
other interest table.

Just trying clarify.

Thanks for your patience.


:

You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it
should
be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX
Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX
Apps
A].PolExpDate, [BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest
record
for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects
the
main
record
set (such as the specific insurance policies.) The second
query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are
multiple
"versions"
of fields in the applications table - each with its own date &
time
stamp.
If an application was modified, my query is returning both
rows
for
that
policy (with the old and new version of the application
field.)
I
would
like
the query to only return the application record with the
maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

mgrashot

I think the problem was that I was not pulling the PolID, COAIID, ANotID, or
CPREMID fields in Query B.

Now I am.

I get the following error when executing Query D. I also get it when trying
to execute Query C, so it appears to be related to Query C.

"You tried to execute a query that does not include the specified expression
'PolID' as part of an aggregate function".



Here is the language for Query C . . .
SELECT PolID, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID, Max(CPREMID)
AS mCPREMID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the language for Query D . . .
SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID OR [BX Apps B].CPREMID=[BX Apps C].mCPREMID)
ORDER BY [BX Apps B].PolID;







Michel Walsh said:
Then, it may be possible that the query [BX Apps B] lists the field PolID
twice? Should list that field just once. Note that in the query you posted,
I don't see it *at all* (in the SELECT clause):


SELECT DISTINCT [BX Apps A].CustNo,
[BX Apps A].LastName,
[BX Apps A].PolTypeLOB,
[BX Apps A].PolNo,
[BX Apps A].PolEffDate,
[BX Apps A].PolExpDate,
[BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description AS Notation,
dbo_AFW_CPrem.vLimit1,
dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2,
dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2,
dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State,
dbo_AFW_CommAddOtherInt.Zip
FROM ...


so the error message sounds strange to me since [BX Apps B] does not have
any PolID field (as per the query you posted, or I am wrong about which
query is "[BX Apps B]" ).


Vanderghast, Access MVP


mgrashot said:
I'm actually using Access but just viewed it in SQL view.

Michel Walsh said:
You are using MS SQL Server as database engine? If so, remove the *, in
SELECT [BX Apps B].*, and list explicitly the fields you want, in that
latest query.


Vanderghast, Access MVP


I think I'm so close. Please be patient and help me through the end of
this.

Here is the second query - BX Apps B. (The first query selects the
policies
based on dates and then the second query appends the other fields to
those
selected policies.)

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX Apps
A].PolExpDate, [BX Apps A].ParentCo, dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CommAddOtherInt ON [BX Apps A].PolId =
dbo_AFW_CommAddOtherInt.PolId)
LEFT JOIN dbo_AFW_CPrem ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId
ORDER BY [BX Apps A].PolNo;

Here is the third query per your instructions - BX Apps C . . .

SELECT PolID AS Expr1, Max(ANotID) AS mANotID, Max(CAOIID) AS mCAOIID
FROM [BX Apps B]
GROUP BY [BX Apps B];

Here is the fourth query per your instructions - BX Apps D . . .

SELECT [BX Apps B].*
FROM [BX Apps B] INNER JOIN [BX Apps C] ON ([BX Apps B].PolID=[BX Apps
C].PolID) AND ([BX Apps B].ANotID=[BX Apps C].mANotID OR [BX Apps
B].CAOIID=[BX Apps C].mCAOIID)
ORDER BY [BX Apps B].PolID;

The problem is that when I execute the fourth query, I get the
following
error . . .
The specified field "[BX Apps B].PolID" could refer to more than one
table
listed in the FROM clause of your SQL statement.

Any suggestions??





:

Make a first query:

SELECT policy, MAX(ANotID) AS mANotID, MAX(CAOIID) AS mCAOIID
FROM q1
GROUP BY policy


save it (say it is q2) then, a final query:


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.policy = q2.policy
AND (q1.ANotID = q2.mANotID OR q1.CAOIID = q2.mCAOIID)


to which you can add

ORDER BY q1.policy


to 'regroup' the records, on output, but that is not necessary to get
the
right data.



Vanderghast, Access MVP


Q1 selects several policies (9,000)

I then want to append to each of those records the most recent
version
of
the AgencyNotation field (as determined by the ANotID) from the
notation
table and the CommAddOtherInt field (as determined by the CAOIID)
from
the
other interest table.

Just trying clarify.

Thanks for your patience.


:

You just want TWO records? If the actual query is called q1, then


SELECT *
FROM q1
WHERE ANotID = (SELECT MAX(ANotID) FROM q1)
OR CAOIId = (SELECT MAX(CAOIID) FROM q1)



(Unless I missed it, ANotID field is not in the SELECT part, it
should
be
added to the list of fields the SELECT clause returns).


Vanderghast, Access MVP



I'm confused (and I guess pretty basic).
In SQL view, this is my query . . .

SELECT DISTINCT [BX Apps A].CustNo, [BX Apps A].LastName, [BX
Apps
A].PolTypeLOB, [BX Apps A].PolNo, [BX Apps A].PolEffDate, [BX
Apps
A].PolExpDate, [BX Apps A].ParentCo,
dbo_AFW_AgencyNotation.Description
AS
Notation, dbo_AFW_CPrem.vLimit1, dbo_AFW_CommAddOtherInt.Name1,
dbo_AFW_CommAddOtherInt.Name2, dbo_AFW_CommAddOtherInt.Addr1,
dbo_AFW_CommAddOtherInt.Addr2, dbo_AFW_CommAddOtherInt.City,
dbo_AFW_CommAddOtherInt.State, dbo_AFW_CommAddOtherInt.Zip,
dbo_AFW_CPrem.CPremId, dbo_AFW_CommAddOtherInt.CAOIId
FROM ((dbo_AFW_AgencyNotation RIGHT JOIN [BX Apps A] ON
dbo_AFW_AgencyNotation.ANotId = [BX Apps A].ANotId) LEFT JOIN
dbo_AFW_CPrem
ON [BX Apps A].PolId = dbo_AFW_CPrem.PolId) LEFT JOIN
dbo_AFW_CommAddOtherInt
ON [BX Apps A].PolId = dbo_AFW_CommAddOtherInt.PolId;


ANotID is the date/time stamp for the AgencyNotation field
CAOIId is the date/time stamp for the CommAddOtherInt field.

I would like to return each of those fields with the latest
record
for
each.

Exactly where do I edit the query?

:

Use any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm as example.


Vanderghast, Access MVP


I am using a compound query, meaning the first query selects
the
main
record
set (such as the specific insurance policies.) The second
query
includes
the
first query but then links to a table of applications. These
applications
can be modified throughout a policy period, so there are
multiple
"versions"
of fields in the applications table - each with its own date &
time
stamp.
If an application was modified, my query is returning both
rows
for
that
policy (with the old and new version of the application
field.)
I
would
like
the query to only return the application record with the
maxium
date/time
stamp. Any help??
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Query C should be:


SELECT PolID,
Max(ANotID) AS mANotID,
Max(CAOIID) AS mCAOIID,
Max(CPREMID) AS mCPREMID
FROM [BX Apps B]
GROUP BY [BX Apps B].PollID

instead of the actual


SELECT PolID,
Max(ANotID) AS mANotID,
Max(CAOIID) AS mCAOIID,
Max(CPREMID) AS mCPREMID
FROM [BX Apps B]
GROUP BY [BX Apps B]



(the difference is in the GROUP BY clause)


Vanderghast, Access MVP
 
M

mgrashot

Oh so close.

Query C now works fine. Perfect.

However, Query D is pulling more records than it should (8,981 vs. 8,842 in
Query C). It is pulling in rows (probably from Query B) that do not appear
in Query C.

Any suggestions as to how to limit the selection output for Query D by the
selection from Query C instead of Query B?

Thanks a lot - almost there!
 
M

Michel Walsh

If mANotID and mCAOIID occurs in two different records, each of the two
record is returned. I suspect that this occurred 39 times (for 39 PolID), so
you got 39 extra records. If that is the case, you have to decide which
record is to be kept, the one where its ANotID is equal to mANotID or the
record where its CAOIID equals to mCAOIID.
(note that your query also uses mCPREMID, so it is possible that 3 records
are pulled out, one record for each different max).

Vanderghast, Access MVP


mgrashot said:
Oh so close.

Query C now works fine. Perfect.

However, Query D is pulling more records than it should (8,981 vs. 8,842
in
Query C). It is pulling in rows (probably from Query B) that do not
appear
in Query C.

Any suggestions as to how to limit the selection output for Query D by the
selection from Query C instead of Query B?

Thanks a lot - almost there!




Michel Walsh said:
Query C should be:


SELECT PolID,
Max(ANotID) AS mANotID,
Max(CAOIID) AS mCAOIID,
Max(CPREMID) AS mCPREMID
FROM [BX Apps B]
GROUP BY [BX Apps B].PollID

instead of the actual


SELECT PolID,
Max(ANotID) AS mANotID,
Max(CAOIID) AS mCAOIID,
Max(CPREMID) AS mCPREMID
FROM [BX Apps B]
GROUP BY [BX Apps B]



(the difference is in the GROUP BY clause)


Vanderghast, Access MVP
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with returning the newest record in a query 3
More help with returning the newest record in a query 6
Access 2007 - check for existing record 1
Query-how to keep one record with the oldest payment date 1
How to choose only the first record from a query 4
In a query, show master records even if a child record doesn't exi 2
EMERGENCY! How can I update one table from another in a select que 5
query sort on date 2

Top