I want to Group portions of a counted fieldson a report

[PPSAG]

I have an Access97 database with a field that holds codes in it. The codes are three numeric characters then a - and a letter. I want to count and group by the numbers only. Then print them on a report that includes both monthly and annual totals. I think I wrote the query wrong, but I can't get the report to come even close to what I need. Mooo...

 

[Guest_imported]

I would say the first thing you need to do is create some fields in your query that you can use on your report.

MySortField:Left([CodeFieldName],3)
returns the first three characters (your numbers)

MyMonthField:Month([YourDateField])
returns the month for grouping monthly totals

MyYearField:Year([YourDateField])
returns the year for grouping annual totals.

You can then create you report using the wizard, and group by MyYearField first, MyMonthField second and MySortField last. Then in the appropriate Footers, you can get your totals for Monthly and Annually.

HTH

Paul

 

[PPSAG]

Thanks for the input. I don't think I have created fields before. What does that look like in SQL? Mooo...

 

[Guest_imported]

To create a field in a query you just type in a name for the field on the Field (top) line in your query. In this example we used

MySortField:

with the colon at the end, and then add whatever expression you want to get the results you need.

In this case, I wanted to find the first 3 characters in your code field so we used

Left([YourCodeFieldName],3)

Put them together on the Field line of your query like this.

MySortField:Left([YourCodeFieldName],3)

When you run the query you will have a column called

MySortfield

at the top, and the 3 character values you want in the column. You need to replace [YourCodeFieldName] with the actual name of your field.

If you create these fields in the query, then when you create the Report with the wizard, you can just add the fields from your query rather than trying to create them in your Report.
Start slow. Create the query with whatever Table/Query you want, then try creating one new field. See how it goes and post back with any questions. If you have problems, post what you have tried so we can see it and advise from there.

Good luck.

Paul