Queries that affect reports

[Kelly H]

I am using Access 2000 and I have run into a few problems when I try to run a
report based off queries it asks me to enter the date range multiple times.
Only one of the queries actually has a date range and the other uses that as
a source.
Is there a way to only enter the date range once?

The main source is:
SELECT [tbl Progress].Installed, [tbl Progress].Comissioned, [tbl
Progress].[TRAINING COMPLETE], [tbl Progress].[INVOICE NUMBER], [tbl Site
Survey Information].[Scheduled Installation Date], [tbl Site Survey
Information].[Site Survey Date], [tbl Site Survey Information].[EMnet
Address]
FROM [tbl Progress] RIGHT JOIN [tbl Site Survey Information] ON [tbl
Progress].[EMNET ADDR] = [tbl Site Survey Information].[EMnet Address]
WHERE ((([tbl Site Survey Information].[Scheduled Installation Date])
Between [enter start date] And [enter last date]))
ORDER BY [tbl Site Survey Information].[Scheduled Installation Date], [tbl
Site Survey Information].[EMnet Address];

The secondary query is:
SELECT Count([qry progress reporting].[Scheduled Installation Date]) AS
[CountOfScheduled Installation Date], Count([qry progress
reporting].Installed) AS CountOfInstalled, Count([qry progress
reporting].Comissioned) AS CountOfComissioned, Count([qry progress
reporting].[TRAINING COMPLETE]) AS [CountOfTRAINING COMPLETE], Count([qry
progress reporting].[INVOICE NUMBER]) AS [CountOfINVOICE NUMBER],
Count([qry progress reporting].[EMnet Address]) AS [CountOfEMnet Address]
FROM [qry progress reporting];
- Any Help is appreciated!
Kelly

 

[Chaim]

I'll admit straight off that I am not certain about this. But it seems that
since you can use the query ([qry progress reporting]) as a table
(conceptually, that is exactly what it is), you can refer to the fields of
that query.

What happens if you take the date range criteria out of the base query and
put them into the final query? As in:

SELECT ..., [tbl Site Survey Information].[Scheduled Installation Date],
.... FROM ... (WHERE clause is no longer here)
ORDER BY ...;

And then add the WHERE clause to your secondary query:

SELECT <select list>
FROM [qry progress reporting]
WHERE [qry progress reporting].[Scheduled Installation Date])
Between [enter start date] And [enter last date]));

Good Luck!

 

[Kelly H]

Chaim-

I am not sure if that will work because I have to use the date range on both
sections. (one is a subreport with counts, the main report the individual
site information)- Therefore I do need the date range for both. Would a make
table query work? How could I set up a query to run off a changing table
from another make table query?

I'll admit straight off that I am not certain about this. But it seems that
since you can use the query ([qry progress reporting]) as a table
(conceptually, that is exactly what it is), you can refer to the fields of
that query.

What happens if you take the date range criteria out of the base query and
put them into the final query? As in:

SELECT ..., [tbl Site Survey Information].[Scheduled Installation Date],
.... FROM ... (WHERE clause is no longer here)
ORDER BY ...;

And then add the WHERE clause to your secondary query:

SELECT <select list>
FROM [qry progress reporting]
WHERE [qry progress reporting].[Scheduled Installation Date])
Between [enter start date] And [enter last date]));

Good Luck!
--

Chaim

I am using Access 2000 and I have run into a few problems when I try to run a
report based off queries it asks me to enter the date range multiple times.
Only one of the queries actually has a date range and the other uses that as
a source.
Is there a way to only enter the date range once?

The main source is:
SELECT [tbl Progress].Installed, [tbl Progress].Comissioned, [tbl
Progress].[TRAINING COMPLETE], [tbl Progress].[INVOICE NUMBER], [tbl Site
Survey Information].[Scheduled Installation Date], [tbl Site Survey
Information].[Site Survey Date], [tbl Site Survey Information].[EMnet
Address]
FROM [tbl Progress] RIGHT JOIN [tbl Site Survey Information] ON [tbl
Progress].[EMNET ADDR] = [tbl Site Survey Information].[EMnet Address]
WHERE ((([tbl Site Survey Information].[Scheduled Installation Date])
Between [enter start date] And [enter last date]))
ORDER BY [tbl Site Survey Information].[Scheduled Installation Date], [tbl
Site Survey Information].[EMnet Address];

The secondary query is:
SELECT Count([qry progress reporting].[Scheduled Installation Date]) AS
[CountOfScheduled Installation Date], Count([qry progress
reporting].Installed) AS CountOfInstalled, Count([qry progress
reporting].Comissioned) AS CountOfComissioned, Count([qry progress
reporting].[TRAINING COMPLETE]) AS [CountOfTRAINING COMPLETE], Count([qry
progress reporting].[INVOICE NUMBER]) AS [CountOfINVOICE NUMBER],
Count([qry progress reporting].[EMnet Address]) AS [CountOfEMnet Address]
FROM [qry progress reporting];
- Any Help is appreciated!
Kelly