Report groupings

[Jane Long]

Help!

I am having problems with a report I am designing- I have
linked an excel spreadsheet with daily information. The
date field has mm/dd/yy format and contains records for
both 2001 and 2002. In my first query: the Query grid
has Field: date - Table: my spreadsheet - Total: sum
Criteria: >#01/01/01# AND >#01/01/02# and format mmmmyy
I want only the 2001 records for the first report. The
result is a line for each record. I want 12 lines - one
for each month with the totals summarized across.

ex)sumofGroupA SumofGroupB SumofGroupC SumofGroupD Date
1 1 Jan-01
1 1 Jan-01

Labels on report would be as follows

Month Total Amt Total Count GroupA GroupB GroupC GroupD

I want each month to be together per group As follows:

Jan $9,500.00 65 21 33 6 1
Feb $3,300.00 23 36 22 7 8

but my report is not grouping as above- I have several
lines with the same month. I tried using the group header
and/footer selecting group on month but this did not
work. I can reformat my spreadsheet and change the date
to mmmmyy and have a spreadsheet per year then link and it
works but I want to use my original spreadsheet> I know I
am doing something wrong but can't seem to get my desired
result with my attempts. Is my query or report design the
problem?

I certainly would appreciate help!

 

[Steve Schapel]

Jane,

I am not 100% clear what you are doing, from your description. But
maybe this comment will help. It may be confusing, because of 2
different meanings of the word 'format'. But if your date is a Date,
it doesn't matter what the format is, the actual data will be the
same, and trying to use it as the basis of sorting and grouping won't
work. However, if you use the Format function (another matter
entirely), this returns a string as defined. So, in your query you
could put a new column like this...
MonthName: Format([YourDateField],"mmm")
.... and use this to Group By in your Totals query.
In fact, you will also need another calculated field...
MonthNumber: Month([YourDateField])
.... if you want to be able to sort the output in chronological order
rather than alphabetical month order.

An incomplete answer, I know, but hopefully point you somewhere
useful.

- Steve Schapel, Microsoft Access MVP