query design help

[Striker3070]

I have a small table in A2007 that has only a few fields in it. Date,
Location, Accepted, Count (these are the ones that matter)

I need to do a query that can group by Location and count the Yes/No's in
the Accepted field. On any one date there might be 20 input from the same
location, so I suppose the correct option is to group by location first.

The end goal here is to create a report that groups by location code and
counts the yes/no for that location. Currently I have them entering a date,
but I want to get to a report that does the same thing Year to date, and
month to date. Not sure if I should be doing a query that create a new
table each time

 

[Rick Brandt]

I have a small table in A2007 that has only a few fields in it. Date,
Location, Accepted, Count (these are the ones that matter)

I need to do a query that can group by Location and count the Yes/No's in
the Accepted field. On any one date there might be 20 input from the same
location, so I suppose the correct option is to group by location first.

The end goal here is to create a report that groups by location code and
counts the yes/no for that location. Currently I have them entering a
date, but I want to get to a report that does the same thing Year to date,
and
month to date. Not sure if I should be doing a query that create a new
table each time

God no, no need to be creating tables for simple reporting.

Are you saying you want...

A single report simultaneously showing year-to-date and month-to-date?

A single report that can be used for year-to-date in one instance and then
used for month-to-date in another?

Two seperate reports. One for YTD and one for MTD?

The most versatile design would be to have a query that gets a date-range
criteria from a form where the user specifies a start-date and an end-date
and then invokes the report (based on that query).

The chosen date-range on the form can then be displayed in the header of the
report so one seeing only the report knows what they are looking at.

On the form for the date-range you can provide shortcuts mechanisms that
auto-populate the dates with "popular" values for year-to-date, month-to-
date, etc..

 

[Striker3070]

Ideally I would like one report that does YTD, MTD and yesterdays counts.
If I need more than one report, fine but like to avoid many reports if
possible.

 

[Philip Herlihy]

You're unlikely to need to create a new table each time unless there are
other reasons you haven't told us about.

You can do some of this within a query (an "aggregate" or "totals" query).
Have a look at these two links:

http://599cd.com/tips/access/aggregate-query/ (taster sample for a
commercial site)

http://office.microsoft.com/en-us/access/HA100963111033.aspx

For more flexibility, stick to a normal query (no "totals") and make it the
record source of a report. If you're new to reports, the report wizard is
excellent. Just tell it where to get its records from, and it will ask
questions about how you want to the data laid out, including sorting and
grouping options. The trick is to run it repeatedly, discarding what it
generates, until it's starting to look right, and then look into how to make
those final adjustments in Design view. Experiment!

Phil, London

 

[Steve]

Ti start, you need to rename the fields Date and Count. Both are reserved
words and at some point in your database will cause you problems!

To answer your question ............
You need four Totals queries and a select query.

1. Create a query named QryRptYearToDateYes that includes Location, Date
and Accepted. Set the criteria for Date to:
Between DateSerial(Year(Date()),1,1) And Date()
Set the criteria for Accepted to True.
Click the Sigma button (looks like capital E) in the menu at the top of the
screen. Go down to the accepted field and change Group By to Count.

2. Create a query named QryRptYearToDateNo that includes Location, Date
and Accepted. Set the criteria for Date to:
Between DateSerial(Year(Date()),1,1) And Date()
Set the criteria for Accepted to False.
Click the Sigma button (looks like capital E) in the menu at the top of the
screen. Go down to the accepted field and change Group By to Count.

3. Create a query named QryRptMonthToDateYes that includes Location,
Date and Accepted. Set the criteria for Date to:
Between DateSerial(Year(Date()),Month(Date()),1) And Date()
Set the criteria for Accepted to True.
Click the Sigma button (looks like capital E) in the menu at the top of the
screen. Go down to the accepted field and change Group By to Count.

4. Create a query named QryRptMonthToDateNo that includes Location, Date
and Accepted. Set the criteria for Date to:
Between DateSerial(Year(Date()),Month(Date()),1) And Date()
Set the criteria for Accepted to False.
Click the Sigma button (looks like capital E) in the menu at the top of the
screen. Go down to the accepted field and change Group By to Count.

5. Create another query named QryRptAccepted that includes queries 1 to
4. Join the four queries on Location. CountOfAccepted in query 1 will give
you the year to date Yeses. CountOfAccepted in query 2 will give you the
year to date Nos. CountOfAccepted in query 3 will give you the month to date
Yeses. CountOfAccepted in query 4 will give you the month to date Nos.
Include Location and the four CountOfAccepted fields in your query.

Now you can use QryRptAccepted as the recordsource of your report and show
year to date Yes, year to date No, month to date Yes and month to date No
for each location in one report.

Steve
(e-mail address removed)

 

[John Spencer]

One query to count by location for total ever accepted, total accepted this
month and totalaccepted for the current year

SELECT Location
, Abs(Sum(Accepted)) as TotalAccepted
, Count(IIF([SomeTable].[Date] Between DateSerial(Year(Date()),1,1) and
Date(),1,Null)) as YearToDateAccepted

, Count(IIF([SomeTable].[Date] Between
DateSerial(Year(Date()),Month(Date()),1) and Date(),1,Null)) as
MonthToDateAccepted

FROM SomeTable
GROUP BY Location

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Striker3070]

that's perfect, now I just need to add yesterdays information in there in
two queries I suppose. What is the criteria for yesterday Today(-1)?

Now I could have a section for yesterday, MTD and then YTD

Thanks

 

[Steve]

Create the same kind of queries as you did in 1 to 2. Set the criteria for
Date as:
DateADD("d",-1,Date())
Include these queries in QryRptAccepted too.

Steve
(e-mail address removed)