Multiple Reports from One Query

A

April Conner

I have a query that has a data field for each month of the year ie. Jan Sls,
Jan GP, Feb Sls, Feb GP.....; there are many categories of data related to
each month. I want to generate a report every month that only shows the
current month's data. How can I do this without making individual repports
for each month?
 
P

PSIAccess

Can you add a Parameter criteria to your query? In the
date field you want to restrict, add something like

Between [Enter the Start Date] and [Enter the End Date]

Put this in the Criteria line of the date field in your
query and then run the query, you will be prompted for
dates and then the query will be dynamic based on what
the user enters.

There may be more to your query than I know so if you
still need help, give me a little more detail and I'll
try and help again.

Good Luck!
 
A

April Conner

Thanks for replying. However, I don't think this will work because the
information is not dated the way you are thinking. There is a column or
field for each Month such as Jan Sls, Feb Sls, Mar Sls, etc. and there are
$$ values in each column. So, I want to select the monthly columns for each
individual report. Is there possibly a way that I can set up a parameter
criteria to select the columns that I want to show in the report? I am stuck
Can you add a Parameter criteria to your query? In the
date field you want to restrict, add something like

Between [Enter the Start Date] and [Enter the End Date]

Put this in the Criteria line of the date field in your
query and then run the query, you will be prompted for
dates and then the query will be dynamic based on what
the user enters.

There may be more to your query than I know so if you
still need help, give me a little more detail and I'll
try and help again.

Good Luck!
-----Original Message-----
I have a query that has a data field for each month of the year ie. Jan Sls,
Jan GP, Feb Sls, Feb GP.....; there are many categories of data related to
each month. I want to generate a report every month that only shows the
current month's data. How can I do this without making individual repports
for each month?
.
Click to expand...
Click to expand...
 
M

Marshall Barton

April said:
There is a column or
field for each Month such as Jan Sls, Feb Sls, Mar Sls, etc. and there are
$$ values in each column. So, I want to select the monthly columns for each
individual report. Is there possibly a way that I can set up a parameter
criteria to select the columns that I want to show in the report? I am stuck
on this one, please help.
Click to expand...


This sounds like a spreadsheet, not a proper database table,
and is the cause of your difficulties.

The answer is No, there is no way to specify criteria to
select specific fields from a table or query. If this is a
crosstab query based on a properly normalized table, then
you would be better off creating a new Totals query that
just selects the desired month for the report.

If you really can't avoid the speadsheet style data, then
you'll have to use code in the report's open event to
construct the report's own record source query. Here's some
example air code to give you the general idea:

mth = Forms!someform.sometextbox
strSQL = "SELECT [" & mth & " Sls] As Sales, " _
& "[" & mth & " GP] As GP"
FROM yourquery
Me.RecordSource = strSQL

The report text boxes can then be bound to the Sales and GP
fields in the query without being aware of which month was
selected.
 
Top