Report Design Help Needed

[jrl237]

I've recently inherited a SQL Server database with an Access front end. The report generation is also done in Access, and I need to create a new report. I haven't done a lot of report writing in Access, and I could sure use some help figuring out how to do this.

The report needs to display item information from the DB. The client wants a line per item. Easy enough. So far so good. But they want to see the sales totals by month for the previous three months for each item. Now I have two problems:

How do I get Access to calculate, query, and display the months? And how do I dump the totals to the report?

Any help at all would be appreciated.

TIA,

JRL

I hope this makes sense.

 

[Cretin]

To calculate a total in an access query you go to the query design view. You click on the view menu and then click totals. It will give you several actions you can take. For example you have a table with several items and they have a cost each a quantity and a total cost. You want to sum the total cost you merely set up a query with a sum of total cost and it will total it.
To get that in the report just set up a report with that as the field.

That was the basics. Now for your problem depending on how your database is set up may not even need a query. There are several ways you can do it. If you group by month on the report you can total each group in the report.

If the database is not set up for that to be readily done you can create a query. You can use the totals function I discussed earlier in the query then in criteria put months like >1/1/03 < 4/1/03 and will give you a sum assuming that each of the other items in the query is correct.

 

[jrl237]

Thanks, Cretin. I think you've pointed me in the right direction. I'll take a stab at it and see what happens.

JRL

 

[Cretin]

Ok if any other qyestions feel free to ask.