Join Queries (1 Viewer)

[Joshann]

I have three queries that I would like to make into one query, but I'm having trouble. How do I combine the three queries into one? The three queries are below. The qryGetMaxChapter3 is the one that I will use, i.e. it is built on qryGetMaxChapter2, and qryGetMaxChapter2 is built on qryGetMaxChapter1.

qryGetMaxChapter1
SELECT tblCourt.CourtID, tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter
FROM tblCourt
WHERE (((tblCourt.CourtActionsID)=1 Or (tblCourt.CourtActionsID)=2));

qryGetMaxChapter2
SELECT tblCourt.ID_Number, Max(tblCourt.ActionDate) AS MaxOfActionDate
FROM tblCourt INNER JOIN qryGetMaxChapter1 ON tblCourt.CourtID = qryGetMaxChapter1.CourtID
GROUP BY tblCourt.ID_Number;

qryGetMaxChapter3
SELECT tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter
FROM tblCourt INNER JOIN qryGetMaxChapter2 ON tblCourt.ID_Number = qryGetMaxChapter2.ID_Number
WHERE (((tblCourt.ActionDate)=[MaxOfActionDate]));

 

[Ziggy1]

Do you mean, you want each record from all 3 queries to be on the same line?

You need to add all 3 to the query grid, create you joins between related data between the Chapter1 query and the other 2 queries. Set the join to return all records from Chaptor1....

Without seeing your design that is how I am interpreting it.

 

[Joshann]

Sorry, no that's not what I meant. I want to create a single SQL statement that does what all three queries do. In other words, I don't want to have three saved queries. I want one.

 

[Ziggy1]

Ok..well maybe I'll learn something new if someone else can post a solution.

I would just turn the 3 queries into Append queries, and append the data to a single table (think of it as a Temporary table). this is automated with a macro (or vba) along with a delete query to delete the records in the table appending "To" (or it will accumulate). Theses actions get triggered by whatever event you choose (like command button) eg opening a report.

Macro Actions...

Delete
AppendCH1
AppendCH2
AppendCH3
OpenReport (if report was bound to the appended table)

 

[tmercier]

If the 3 queries have the exact same fields in each then another option is to use an SQL Select Union All query.

Sample Code:
SELECT * FROM PriceListUS
UNION ALL SELECT * FROM PriceListCdn;

 

[Joshann]

Thanks for your reply, but the fields are not the same. Here's the deal. I have a table called tblCourt. For each group of records that have the same tblCourt.ID_Number, I need a query to show me the tblCourt.ID_Number and the Max of the tblCourt.ActionDate where tblCourt.CourtActionsID = 1 or tblCourt.CourtActionsID = 2. As I said before, I can create three queries that will get me the result I need (see my first post), but I really want to create a single SQL statement that achieves the same result as the three queries.