Reports using union queries 2

[MwTV]

Just received a request for a weekly report that has the following columns;

Wk Beginning Date (1)
Number of Accts Received (2)
$ Amount for Accts Received (3)
No. of Accounts Reviewed (4)
Amount of Accounts Reviewed (5)
$ Amt of Payments Received During Week (6)
$ Amount of Payments - YTD (7)

The report is to be distributed on Fridays. The Week Beginning date is every Monday. However, there might be a need to enter date parameters later instead of having the Week Beginning date always defaulted to Mondays.

My initial response to this was that a union query is needed. Something like

Select count(tblAccounts.AcctNo) where tblAccounts.DateRecordCreated between
trunc(sysdate) - 4 and trunc(sysdate)
Union
Select total_charges from tblAccounts
where (select count(tblAccounts.AcctNo) where
tblAccounts.DateRecordCreated between trunc(sysdate) - 4 and trunc(sysdate)
Union
Select count(tblAccounts.AcctNo) where tblAccounts.DateReviewed between
trunc(sysdate) - 4 and trunc(sysdate)
Union
.
.
.
and so on


Any assistance is greatly appreciated.

Thanks in advance.

 

[BNPMike]

I don't think that's what you want to do. A Union adds tables of the same shape 'underneath each other', so your columns need to be the same for each select. What you seem to be doing is taking several slices of the same table.

I think you'd be better off collecting all the records you are interested in (looks like one simple select) and then doing all the counting and adding in controls on your report.

 

[MwTV]

BNPMike,

Conseqently, it appears that I would use several select statements with subqueries.

It definitely appears that this is a use of scalar and aggregate functions within the same query.

 

[BNPMike]

I've not looked at this closely but it looks to me as though you simply collect all the records (in the raw) then use the report to do the adding and counting (and indeed the sorting, if you want). It's more flexible at doing this than SQL. So the query gets the data and the report does all the analysis and presentation.

 

[MwTV]

Hopefully, one can understand the additional context below.

Management desires a rather complex report with the following 7 columns. My initial response to Management is that this type of report should be constructed in Excel and is not possible within Access with a table structure that is not normalized. (For example, there are fields such as AmtReceived1, AmtReceived2, AmtReceived3, AmtReceived4, DateReceived1, DateReceived2, and so on.)


Wk Beginning Date (1)
Number of Accts Received (2)
$ Amount for Accts Received (3)
No. of Accounts Reviewed (4)
Amount of Accounts Reviewed (5)
$ Amt of Payments Received During Week (6)
$ Amount of Payments - YTD (7)

Definition of the desired columns is displayed below.


2) Count (Pt Type = IP) & (Status Code = "Not Worked" & "Open")

3) Total (Pt Type = IP) & (Status Code = "Not Worked" and "Open" ) & associated Charges

4) Count (Pt Type = IP) & Date Submitted fields 1-4 within date range X & Y

5) Total (Pt Type = IP) & (Date Submitted fields 1-4 within date range X & Y) & corresponding Amount Submitted field

6) Total (Pt Type = IP) & (Date Received fields 1-4 within date range X & Y) & corresponding Amount Received field

7) Total (Pt Type = IP) & (Date Received fields 1-4 within date range X & Y) & (corresponding Amount Received field) + (Data point C)

Can this be performed within Access?

 

[BNPMike]

I must admit I haven't done very complex reports but as I recall Access Reports can have all sorts of stuff. One thing you might want to look at is more than one report on a page. I'm pretty sure you can do sub-reports which I assume is a set of sections each fed by a different query. This sounds like the way you are viewing the problem rather than using controls to divide up all the statistics.

So I suspect the answer is "yes and no". Yes - it can be done in Access, but no maybe at this point you can't, at least without spending a lot of time learning the facilities and capabilities of the Report module.

Haven't you got a programmer you can give this task to?

 

[lespaul]

why not create a union query that gets you some normalized data and use that as the source of the query for the report?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins