Summary Report (1 Viewer)

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
My user wants a report with (unrelated ish) data on it. The unrelated bit comes mainly because of the old design of the database and the fact it's not quite a relational model.

I can run several queries to get the data he wants. But how can I then get it onto the report.

So I want field 1 from query 1, field 2 from query 2 and field 3 and 4 from query 3.
I know how to run a query from VBA but how do I then get field 1 through 4 onto the report.

I'm sure I've done this but lost too many brain cells since
 

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
Sub reports?
Having had a quick read I don't think so. Sub reports seemed to be related to the report itself.

I basically want to be able to run x unrelated query. query1 through queryx
The data already I want appears on other reports. The user basically wants to extract 1 piece of data from several existing unrelated (as far as the design of the current database) reports.


On the new report I want something like

me.reportfield1 = query1.fielda
me.reportfield2 = query1.fieldb
me.reportfield3 = query2.fieldc
me.reportfield4 = query3.fieldd
....
me.reportfield5 = queryx.fielde
me.reportfield6 = queryx.fieldf


I can get this so each query only returns 1 row.

Does that make more sense?
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:41
Joined
Sep 21, 2011
Messages
14,050
Is this criteria going to change regularly?, so next week reportfield5 might be from queryY.fieldA
 

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
Is this criteria going to change regularly?, so next week reportfield5 might be from queryY.fieldA
No will come from same place. Obviously may have changed as it's a year to date report but will always come from same place.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:41
Joined
Sep 21, 2011
Messages
14,050
All I can think of is set the report controls from the fields in the recordsets.
You will open as many recordsets as necessary?

Put all the code in the report load event.?

Messy, but doable I would have thought?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
Having had a quick read I don't think so. Sub reports seemed to be related to the report itself.
Hi. Pardon me for jumping in but just wanted to say the above is not true. You can add a subreport on a report, and it doesn't have to be related to the main report, which means you can show anything you want to display within the subreport. Just a thought...
 

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
All I can think of is set the report controls from the fields in the recordsets.
You will open as many recordsets as necessary?

Put all the code in the report load event.?

Messy, but doable I would have thought?
OK thanks but DB Guy has mentioned sub reports again so will dig deeper
 

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
Hi. Pardon me for jumping in but just wanted to say the above is not true. You can add a subreport on a report, and it doesn't have to be related to the main report, which means you can show anything you want to display within the subreport. Just a thought...

OK but on the sub report will I have to do as Gasman suggested by opening a recordset and storing data in appropriate fields. If so then why do I need a subreport? Is it because I can't have an unbound report if that's the right terminology.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
OK but on the sub report will I have to do as Gasman suggested by opening a recordset and storing data in appropriate fields. If so then why do I need a subreport? Is it because I can't have an unbound report if that's the right terminology.
Hi. Pardon me, but I haven't really followed the entire thread. But let's say you have two disparate sets of information and want to display them on one report, you could create a report to show one of the information and another report to show the data for the other one. Now, you can put both of those reports on a third report as subreports, without linking them to anything (main report or each other), and you should have both information showing in one report. Hope that makes sense...
 

ryetee

Registered User.
Local time
Today, 07:41
Joined
Jul 30, 2013
Messages
952
Hi. Pardon me, but I haven't really followed the entire thread. But let's say you have two disparate sets of information and want to display them on one report, you could create a report to show one of the information and another report to show the data for the other one. Now, you can put both of those reports on a third report as subreports, without linking them to anything (main report or each other), and you should have both information showing in one report. Hope that makes sense...
Apologies took a week off!!

Yeah will look at that but it's a few disparate bits of info. They are actually related but database design is poor so they're not easy to get. I think I might look at running a few queries and write to a temp file and use that for the report.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
Apologies took a week off!!

Yeah will look at that but it's a few disparate bits of info. They are actually related but database design is poor so they're not easy to get. I think I might look at running a few queries and write to a temp file and use that for the report.
Hi. Either way, all I'm saying is you can display on one report any information you want from anywhere in your database. Cheers!
 

Users who are viewing this thread

Top Bottom