Multiple Queries for report...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I have is a form where a user can input the query criteria (location,
month, programcode). This generates a report which will list representative
and totals for the month(np & rp for new and repeat business). The trouble
is that I am required to break the monthly totals down into weeks.
(totalwk1, totalwk2...totalwk6) The 6th is to cover the event of a single day
in the week since we work on a Mon-Sat week. In any case, I am able to run
seperate queries for each week, based on the criteria entered, and they work
fine except that as soon as these queries are referenced from within the
report, I am prompted for input for eack week query. Any ideas how to work
around this?

Here is the SQL code for the main and weekly queries:


Main Query:

SELECT Format$([refdate],'mmmm') AS [refdate By Month], tbl_actual.officeID,
tbl_actual.repID, tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp,
Sum(tbl_actual.np) AS SumOfnp
FROM tbl_actual
GROUP BY Format$([refdate],'mmmm'), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes
HAVING (((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate])
AND ((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;


Week Query:

SELECT weekofmonth([refdate]) AS WoM, tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp, Sum(tbl_actual.np) AS
SumOfnp, Format$([refdate],'mmmm') AS [refdate By Month]
FROM tbl_actual
GROUP BY weekofmonth([refdate]), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Format$([refdate],'mmmm')
HAVING (((weekofmonth([refdate]))=1) AND
((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]) AND
((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;
 
PerplexedPeon said:
What I have is a form where a user can input the query criteria (location,
month, programcode). This generates a report which will list representative
and totals for the month(np & rp for new and repeat business). The trouble
is that I am required to break the monthly totals down into weeks.
(totalwk1, totalwk2...totalwk6) The 6th is to cover the event of a single day
in the week since we work on a Mon-Sat week. In any case, I am able to run
seperate queries for each week, based on the criteria entered, and they work
fine except that as soon as these queries are referenced from within the
report, I am prompted for input for eack week query. Any ideas how to work
around this?

Here is the SQL code for the main and weekly queries:


Main Query:

SELECT Format$([refdate],'mmmm') AS [refdate By Month], tbl_actual.officeID,
tbl_actual.repID, tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp,
Sum(tbl_actual.np) AS SumOfnp
FROM tbl_actual
GROUP BY Format$([refdate],'mmmm'), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes
HAVING (((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate])
AND ((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;


Week Query:

SELECT weekofmonth([refdate]) AS WoM, tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp, Sum(tbl_actual.np) AS
SumOfnp, Format$([refdate],'mmmm') AS [refdate By Month]
FROM tbl_actual
GROUP BY weekofmonth([refdate]), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Format$([refdate],'mmmm')
HAVING (((weekofmonth([refdate]))=1) AND
((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]) AND
((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form "frm_report1_select" should be open while the report runs. Is
it? I usually close the criteria form for a report when the report
closes.

Also, you can improve the efficiency of your query by changing the
HAVING clause to a WHERE clause. HAVING clauses run after all the data
has been retrieved from the table. WHERE clauses run as the data is
retrieved from the table. IOW, WHERE will help the query return only
the data you want - takes up less memory space than using the HAVING
clause.

Also, for queries that "feed" reports it's pointless to have an ORDER BY
clause - let the report sort the output. The query will run faster.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQxIIechKqOuFEgEQLYhgCfSN9PRcf+6n3ZBeQmrjhjyPx5rz4An3GM
c9ddQInZLwxY3KSOU7GQRdI8
=W3i6
-----END PGP SIGNATURE-----
 
Thank you for the additional optimization tips!
The selection form is set to auto-minimize when the report is generated, so
yes, it's still open when the queries are run. That's the part I'm having
trouble with; the query for weekofmonth=n works fine when it's a standalone
query(with select form open) but not when it's been called from within the
report. Is there something I need to do for the additional week queries to
properly receive the criteria then pass it along into the report?

MGFoster said:
PerplexedPeon said:
What I have is a form where a user can input the query criteria (location,
month, programcode). This generates a report which will list representative
and totals for the month(np & rp for new and repeat business). The trouble
is that I am required to break the monthly totals down into weeks.
(totalwk1, totalwk2...totalwk6) The 6th is to cover the event of a single day
in the week since we work on a Mon-Sat week. In any case, I am able to run
seperate queries for each week, based on the criteria entered, and they work
fine except that as soon as these queries are referenced from within the
report, I am prompted for input for eack week query. Any ideas how to work
around this?

Here is the SQL code for the main and weekly queries:


Main Query:

SELECT Format$([refdate],'mmmm') AS [refdate By Month], tbl_actual.officeID,
tbl_actual.repID, tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp,
Sum(tbl_actual.np) AS SumOfnp
FROM tbl_actual
GROUP BY Format$([refdate],'mmmm'), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes
HAVING (((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate])
AND ((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;


Week Query:

SELECT weekofmonth([refdate]) AS WoM, tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Sum(tbl_actual.rp) AS SumOfrp, Sum(tbl_actual.np) AS
SumOfnp, Format$([refdate],'mmmm') AS [refdate By Month]
FROM tbl_actual
GROUP BY weekofmonth([refdate]), tbl_actual.officeID, tbl_actual.repID,
tbl_actual.progCodes, Format$([refdate],'mmmm')
HAVING (((weekofmonth([refdate]))=1) AND
((tbl_actual.officeID)=[Forms]![frm_report1_select].[officeID]) AND
((tbl_actual.progCodes)=[Forms]![frm_report1_select].[progCodes]) AND
((Format$([refdate],'mmmm'))=[Forms]![frm_report1_select].[rptdate]))
ORDER BY tbl_actual.officeID, tbl_actual.repID, tbl_actual.progCodes;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form "frm_report1_select" should be open while the report runs. Is
it? I usually close the criteria form for a report when the report
closes.

Also, you can improve the efficiency of your query by changing the
HAVING clause to a WHERE clause. HAVING clauses run after all the data
has been retrieved from the table. WHERE clauses run as the data is
retrieved from the table. IOW, WHERE will help the query return only
the data you want - takes up less memory space than using the HAVING
clause.

Also, for queries that "feed" reports it's pointless to have an ORDER BY
clause - let the report sort the output. The query will run faster.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQxIIechKqOuFEgEQLYhgCfSN9PRcf+6n3ZBeQmrjhjyPx5rz4An3GM
c9ddQInZLwxY3KSOU7GQRdI8
=W3i6
-----END PGP SIGNATURE-----
Click to expand...
 
PerplexedPeon said:
Thank you for the additional optimization tips!
The selection form is set to auto-minimize when the report is generated, so
yes, it's still open when the queries are run. That's the part I'm having
trouble with; the query for weekofmonth=n works fine when it's a standalone
query(with select form open) but not when it's been called from within the
report. Is there something I need to do for the additional week queries to
properly receive the criteria then pass it along into the report?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how you're calling the query in the report. Is it in VBA?

I found, once, that I had to do wierd things to a query I called from a
report. Instead of just running the query & getting a good recordset I
had to "force" the parameters in. E.g.:

Query:
PARAMETERS Forms!frmCrit!txtFrom Date, Forms!frmCrit!txtTo Date;
SELECT * FROM myTable
WHERE this_date between Forms!frmCrit!txtFrom And Forms!frmCrit!txtTo

In the report I did this:

dim db as dao.database
dim qd as dao.querydef
dim rs as dao.recordset

set db = currentdb
set qd = db.querydefs("my query")
qd(0) = eval(Forms!frmCrit!txtFrom)
qd(1) = eval(Forms!frmCrit!txtTo)

set rs = qd.openrecordset()

This got the rs populated w/ what I wanted. If I didn't use the Eval()
function to "force" the values on the criteria form into the query's
parameters I'd get an empty set. This was back in Access 97 days.
Haven't run across it since.

If the week info is in subreports the subreports should be able to pick
up the criteria info. In Access 2000 I found that I couldn't use the
same subreport multiple times in one report, or, in more than one report
that was open at the same time as other reports w/ the same subreport.
I had to create a separate subreport (same data & format, etc.) for each
separate report. What a PITA!

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtRaPoechKqOuFEgEQJggACg0fiO8wnL8RmJj6FJu8nOtZyw+fAAn0dt
9ay9eaAx50G3arbF11C+RKoK
=Zzaw
-----END PGP SIGNATURE-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query by Month problems 1
Cross-Tab Query 2
Combining 3 queries 2
Totals query rework 1
Last Question on Select Query 3
Union query error 3
Another Question about Select Queries 5
Report Limitation 2

Back
Top