Queries and Reports

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have a large datebase that updates every day with sales
reports. I want to use Access to generate reports on how
many were sold by type and rep by day. I want the report
to show each day's figures along with monthly totals. Is
it better to build a number of queries that answer each
individual data point and build a report around multiple
queries or is it better to build a querie that gets all
of the answers and then create a report that feeds off of
it? My table looks something like this:

Sold Order Rep Type Due Date
3/25/05 11265 Blanca Basic 4/27/05
3/25/05 11267 Blanca Basic 4/28/05
3/25/05 11275 Debbie Custom 4/27/05
3/29/05 11278 Debbie Custom 5/1/05
3/30/05 11279 Blanca Custom 5/1/05

I would like to see a report that looks something like
this:

Sold Rep Basic Custom
3/25/05 Blanca 2 0
3/25/05 Debbie 0 1
Total 2 1

Any suggestions?

Jerry
 
Hi Jerry,

If you are willing to settle for a simple crosstab query, with totals shown
in rows instead of columns, you can use the following SQL statement, where I
have used "tblSales" as the name of the table:

TRANSFORM Count(tblSales.Type) AS CountOfType
SELECT tblSales.Sold, tblSales.Rep, Sum([CountOfType]) AS Totals
FROM tblSales
GROUP BY tblSales.Sold, tblSales.Rep
PIVOT tblSales.Type;

I have a tutorial available for creating crosstab queries at:
http://www.access.qbuilt.com/html/crosstab_queries.html

This includes instructions for adding parameters, such as a beginning and
ending date. Alternatively, you can base a SELECT query on the following SQL
statement:

SELECT tblSales.Sold, tblSales.Rep,
Sum((IIf([Type]="Basic",1,Null))) AS Basic,
Sum((IIf([Type]="Custom",1,Null))) AS Custom
FROM tblSales
GROUP BY tblSales.Sold, tblSales.Rep;

A report is fairly easy to build too. For example, base a report on the
above SELECT query, using the report wizard. Once the report is built, click
on View > Sorting and Grouping. Select the Sold date with Group Header = No,
Group Footer = Yes, Group On = Each value. Bound textboxes for the Sold, Rep,
Basic and Custom fields from the query should be shown in the Detail section
of the report. Then add two calculated textboxes to the Sold footer section
with control sources of:

=Sum([Basic]) and =Sum([Custom])

respectively. Throw in a few descriptive labels, and perhaps the parameter
prompt for beginning and ending date, and you should be done.


Tom
___________________________________________

:

I have a large datebase that updates every day with sales reports. I want to
use Access to generate reports on how many were sold by type and rep by day.
I want the report to show each day's figures along with monthly totals. Is it
better to build a number of queries that answer each individual data point
and build a report around multiple queries or is it better to build a querie
that gets all of the answers and then create a report that feeds off of it?
My table looks something like this:

Sold Order Rep Type Due Date
3/25/05 11265 Blanca Basic 4/27/05
3/25/05 11267 Blanca Basic 4/28/05
3/25/05 11275 Debbie Custom 4/27/05
3/29/05 11278 Debbie Custom 5/1/05
3/30/05 11279 Blanca Custom 5/1/05

I would like to see a report that looks something like
this:

Sold Rep Basic Custom
3/25/05 Blanca 2 0
3/25/05 Debbie 0 1
Total 2 1

Any suggestions?

Jerry
 
Back
Top