• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Is there a way to consolidate 3 queries into 1 query in an Access 2003 application?

I am developing an Access 2003 application that uses 3 queries as follows:
Is there a way to consolidate these 3 queries into 1 query?

1) qryOIDetailsModSrADE =
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblOpenItems.t In ("A","D","E");

2) qryOIDetailsModSrBC =
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblOpenItems.t In ("B","C");

3)
qryMonthlyOpenItemsDetails =
SELECT [Process Date], [Trans Date], [Bank Code] AS [BRS#], [GLAcct#] As [Taps/Margin], [ACCOUNT#]
As [Bank Account Number], T As [_Type], tblOpenItems.Type As [Trans Code], Description, [Office] & ' '
& [CheckNum] As [Check/Reference#], Amount, AgeDays As [Age(Days)], footnote As Comments,
tblBanks.[REPORT NAME] As Responsibility, tblBanks.Currency,tblBanks.[Senior Management Tab]
FROM qryOIDetailsModSrADE
WHERE (tblBanks.[SENIOR MANAGEMENT TAB] <>"NA");
UNION ALL SELECT [Process Date], [Trans Date], [Bank Code] AS [BRS#],  [GLAcct#] As [Taps/Margin],  
[ACCOUNT#] As [Bank Account Number],T As [_Type], tblOpenItems.Type As [Trans Code], Description, [Office]
& ' ' & [CheckNum] As [Check/Reference#], Amount, AgeDays As [Age(Days)], footnote As Comments,
tblBanks.[REPORT NAME] As Responsibility,  tblBanks.Currency, tblBanks.[Senior Management Tab]
FROM qryOIDetailsModSrBC
WHERE (tblBanks.[SENIOR MANAGEMENT TAB] <>"NA")
ORDER BY [Age(Days)] DESC;
0
zimmer9
Asked:
zimmer9
1 Solution
 
chaauCommented:
Yes it is possible. The select statements in your UNION ALL are exactly the same, and the code for the queries is similar. I would do it like this:

SELECT [Process Date], [Trans Date], [Bank Code] AS [BRS#], [GLAcct#] As [Taps/Margin], [ACCOUNT#]
As [Bank Account Number], T As [_Type], tblOpenItems.Type As [Trans Code], Description, [Office] & ' '
& [CheckNum] As [Check/Reference#], Amount, AgeDays As [Age(Days)], footnote As Comments,
tblBanks.[REPORT NAME] As Responsibility, tblBanks.Currency,tblBanks.[Senior Management Tab]
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblOpenItems.t In ("B","C","A","D","E") AND (tblBanks.[SENIOR MANAGEMENT TAB] <>"NA")
ORDER BY [Age(Days)] DESC;

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now