Retrieving first record and all other records within range

[scopelenz]

Hello, I have this rather tricky design which baffles me.

The structures relevant to this question are as follows:

policy_list: policyID, policyname, region
policy_details: pdetailsID, pID (linked to policyID), pnotes, pdate

In essence, for every policy introduced, there're several revisions made in the long run. Policy_list tracks all the policies and their relevant information, while policy_details tracks the relevant revisions (as well as the details of the policy when it was first introduced).

In this query, I need to retrieve any policy revisions that take place within a user-provided date range, and also display the details of the policy when it was FIRST introduced. This 'first policy' need not be within the range.

I have tried a variety of query designs, and I narrowed it to a UNION SELECT, but because the report which utilises this query displays many other fields not in the same table, I will need to do a UNION SELECT of all the other fields as well. In this case, I will get an 'aggregate function' error when I try to do a Min(pdate) and Min(pdetailsID) to retrieve the 'first policy'.

Any ideas on how this can be done? I can't share my database files with all of you due to the confidentiality of this project, but I will definitely appreciate some input. Thanks a lot!

 

[jzwp22]

I'm not sure why you want Min(pdetailsID), you just want to use the min(pdate) and group by pID

query name: qryFirstRevision
SELECT pID, Min(pdate) as FirstRevisionDate
FROM policy_details
GROUP BY pID

Now join the above query back to the policy_details table joining by both the date fields (pdate-FirstRevisionDate) and the pID fields.

SELECT fields
FROM policy_details INNER JOIN qryFirstRevision ON policy_details.pDate=qryFirstRevision.FirstRevisonDate AND policy_details.pID=qryFirstRevison.pID

You would save the above query.

You can then join this last query back to the main policy table and any of the other tables you mention so that it has all fields necessary for the report.

You would use the final query created above in your UNION query.


You would also create your date range query that includes all of the tables/fields needed for the final report separately as well. You would use this final date range query in the UNION query.

In other words, all the fields you want in the report should be present in both queries that will be used in the UNION query.

 

[scopelenz]

I wanted to do min(pDetailsID) because the aggregate function popped out and I needed something to associate it with pDetailsID. It's not a permanent solution, I figured.

I'm not sure why you want Min(pdetailsID), you just want to use the min(pdate) and group by pID

query name: qryFirstRevision
SELECT pID, Min(pdate) as FirstRevisionDate
FROM policy_details
GROUP BY pID

Now join the above query back to the policy_details table joining by both the date fields (pdate-FirstRevisionDate) and the pID fields.

SELECT fields
FROM policy_details INNER JOIN qryFirstRevision ON policy_details.pDate=qryFirstRevision.FirstRevisonDate AND policy_details.pID=qryFirstRevison.pID

You would save the above query.

So this means I will set up two different queries, one to drag out the pIDs of the first revisions, and another for all the relevant data of all the first revisions?

You can then join this last query back to the main policy table and any of the other tables you mention so that it has all fields necessary for the report.

You would use the final query created above in your UNION query.

I got lost here. So I will join Query #2 with the main policy_list table and all other report mentioned fills so that I can UNION SELECT in the Report Source query, is that right?

You would also create your date range query that includes all of the tables/fields needed for the final report separately as well. You would use this final date range query in the UNION query.

In other words, all the fields you want in the report should be present in both queries that will be used in the UNION query.

My date range query utilises a simple WHERE BETWEEN function, and is part of the Report Source query. In diagrammatic manner, it should look like this:

SELECT from policy_list AND policy_details JOIN on (Query #2 JOIN ON qryFirstRevision) UNION SELECT Main Report Source Query WHERE BETWEEN "Date Range".

Am I getting it right?

 

[jzwp22]

So this means I will set up two different queries, one to drag out the pIDs of the first revisions, and another for all the relevant data of all the first revisions?

I got lost here. So I will join Query #2 with the main policy_list table and all other report mentioned fills so that I can UNION SELECT in the Report Source query, is that right?

What this does is just uses a series of queries to get to the final first revision query that brings in all fields necessary for the report for all of the first revisions of all policies.


You would use a similar approach for those queries in the date range. Of course, you would need fewer queries to get to the final date range query.


You would then reference these 2 final queries in the UNION query you need for your report

SELECT fields.. FROM qryFinalFirstRevision

UNION

Select fields...FROM qryFinalDateRange


Remember that the number and order of the fields in the two subqueries must be the same for the UNION query to work.