Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
6 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How would modify a query to group by a field and within each grouping, have an order by clause?
Posted on 2014-01-10
I am working with a query using Access 2003.
How would you modify the following query to order the records by
[Age(Days)] DESC within tblBanks.[Senior Management
In other words I want to group records by the field tblBanks.[Senior Management
and withinin each grouping, the records should be sorted in [Age(Days)] DESC.
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
ORDER BY [Age(Days)] DESC;
How would you modify the following query to order the records by
[Age(Days)] DESC within tblBanks.[Senior Management
In other words I want to group records by the field tblBanks.[Senior Management
and withinin each grouping, the records should be sorted in [Age(Days)] DESC.
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
ORDER BY [Age(Days)] DESC;
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
5 Comments
Active today
try this
select x.*
From
(SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
) As X
Order By x.[Age(Days)] Desc
or use use this Order bY
Order By 11 DESC
select x.*
From
(SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
) As X
Order By x.[Age(Days)] Desc
or use use this Order bY
Order By 11 DESC
Active 1 day ago
ORDER BY [Senior Management Tab], [Age(Days)] DESC
OR
ORDER BY [Senior Management Tab] ASC, [Age(Days)] DESC
OR
ORDER BY [Senior Management Tab] ASC, [Age(Days)] DESC
Active today
oops sorry, forgot about the Management group
select x.*
From
(SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
) As X
Order By X.[Senior Management Tab], x.[Age(Days)] Desc
.
select x.*
From
(SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("A","D","E");
UNION ALL
SELECT tblOpenItems.[Process Date], tblOpenItems.[Trans Date], tblBanks.[Bank Code] AS [BRS#],
tblBanks.[GLAcct#] As [Taps/Margin],
tblBanks.[ACCOUNT#] As [Bank Account Number], tblOpenItems.T As [_Type], tblOpenItems.Type As [Trans Code],
tblOpenItems.Description,
tblOpenItems.[Office] & ' ' & [CheckNum] As [Check/Reference#],
tblOpenItems.Amount, tblOpenItems.AgeDays As [Age(Days)], tblOpenItems.footnote As Comments,
tblBanks.[Report Name] As Responsibility,tblBanks.Cu
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
Where tblBanks.[RISK REPORT] = 'YES' AND tblOpenItems.t In ("B","C")
) As X
Order By X.[Senior Management Tab], x.[Age(Days)] Desc
.
Please see attached doc. Thank you.
Synax.doc
Synax.doc
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …
Suggested Courses
Course of the Month6 days, 7 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.