Report grouping

Posted on 2014-01-18
Last Modified: 2014-01-19
I want to make a report from a query. The query has a date column with dates throughout the year.

My end result is I want three grouping of the report 1 Group for the current Day. The second group for the current week and the third group for the current year.

Can I do these groupings from the report or the query? Do I need to write seperate queries for each grouing and then join them in one big group?
Question by:cansevin
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
  • 2
  • 2
  • 2
  • +1
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791184
do the grouping in the report.

Author Comment

ID: 39791185
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791192
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39791223
You could also create a computed colum in the query, something like:

RptGrp: iif([DateColumn] = Date(), 1, IIF(Week([DateColumn]) = Week(Date()), 2, 3))

If you dates outside of the current year, you would need to either filter those out of the report, or modify to include another IIf( ) statement.

The problem is, that today falls into all three of those group definitions, but a specific record could only fall into one of those categories, so todays record could not show up in the Today, Week, and Year groupings.
LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 39791329

If having the overlapping dates in the report is a hard requirement, you can create a separate query for each date range.  (...adding in a "DateSpec" column for each query: CurrentDay, CurrentWeek, CurrentYear)

Then UNION all three queries into one query.

Then create a Report from the Union query, grouping on the DateSpec Field.

See the attached sample

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39791347
cansevin, follow up...

Typically, in situations like this (overlapping data), a separate report for each Date grouping is used.
You can run all of them all in a batch operation so that they all print out at the same time.

...Because if you keep them all in one report, ...ultimately you will be asked..."Can you just give me a report with the current week?"


Author Closing Comment

ID: 39792205
Thanks! This will lead me in the right direction.

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question