Merge records from 2 queries as one record in a new query (1 Viewer)

TimTDP · Mar 11, 2019

I posted a question earlier on how to return first and last dates by year:
https://access-programmers.co.uk/forums/showpost.php?p=1614857&postcount=1
Colin (isladogs) kindly solved this for me.

In his solution, the Union query returns 2 records. One for the first date, and one for the last date
The query is:

SELECT yYear, FundId, FirstDateInYear FROM qryFundFirstStatementDateForYear
UNION SELECT yYear, FundId, LastReportingDate FROM qryFundCorrectedLastStatementDateForYear;

How can I create one record from the two queries?
yYear, FundId, FirstDateInYear, LastReportingDate

Thanks in advance

isladogs · Mar 11, 2019

Hi Tim
You got me again ....

That's actually easier to do....

Code:

SELECT Year([PaymentDate]) AS Expr1, Min(tblPaymentDates.PaymentDate) AS FirstPaymentDate, Max(tblPaymentDates.PaymentDate) AS LastPaymentDate
FROM tblPaymentDates
GROUP BY Year([PaymentDate]);

Just replace the table/field names with your own
Note that I've omitted the ID field as the two dates will come from different records

plog · Mar 11, 2019

From what I read, you don't need a UNION query to accomplish this. You can do both the MAX and MIN in the same select:

Code:

SELECT YEAR(YourDateField) AS DataYear, MAX(YourDateField) AS LastDateInYear, MIN(YourDateField) AS FirstDateInYear
FROM YourTableNameHere
GROUP BY YEAR(YourDateField)

Merge records from 2 queries as one record in a new query (1 Viewer)

TimTDP

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

Similar threads

Users who are viewing this thread