Hi

I am trying to design a report in access, that takes data from more than one query. Is it possible? I'm not using a link to Word as the other complication is each report (which will be a longish document) will refer to one individual from a database of 800+. I'm a VBA novice - someone kindly gave me some code to select a specific record from the database, but told me the logistics of doing this within linkin to word would be too complicated. I have so much info that needs to go into the report that it would be too big for one table or query. Any suggestions gratefully received!!!!!

A report can only pull from one query/table. I'd recommend making a query that shows all the information you'd like to represent in your report, and have the report key off that query.

You mention that you have "...so much info... that it would be too big for one table or query." Give us an idea of what you're dealing with. I've got A97 reports happily chugging through 2 million or so records, producing a nice neat summary. Might that be part of the issue? Trying to figure out how to group/summarize your data? Access can do quite an astonishing array of things, and just might be able to provide what you are looking for in a simple manner.

Let us know what road-blocks are currently in your way. I'm sure one of us here can help you!

o!
P

Hi,

This is not true. You can use a system of subreports to display results from multiple queries.
Jim

<laugh> This reminds me of another board. What you said about subreports is correct in spirit. HOWEVER, each of THOSE reports can only pull from one query.

o!
P

We are talking semantics here. To a basic user, the use of 1 report or sub reports is inconsequential. The end result is that records from multiple sources are displayed on a single report. You provided solution was unsuitable as it implied that the task which was being asked for was impossible. It isn't. I'm not 'getting at you' I just want to clarify the post.
Jim

It would help if you gave us some more details about what you are trying to accomplish. The direct answer is that a report must be based on a single table/query. However, you can have subreports to bring in data from multiple sources.

However this may not be necessary. You can often create a grouping from a single query that willdo what you want.

Hi, thanks for your replies - more info:

My database is of 800+ transplant patients (I work in a hospital). We want to produce summary letters for each patient when they are discharged after the operation. At the moment there's always a big delay in preparing these summaries as they are very long and detailed (so the Drs put off doing them!). People in admin decided that almost all of the info is already in the database, so we're aiming at producing what looks like a summary letter, as a report in access. So for mr Bloggs's summary letter we would need to use bits and peices of information from about 6 different sections of the database. I hope this makes the situation clearer.... whilst waiting for any more input I'll see if I can do a whopper query as opposed to the 7 I currently have (when I thought that somehow I could use them all in the report) - though I'm doubtful it'll cope! A subreport won't work as we need to shift the info around. Thanks in advance

PS

I just tried a big query from all the tables, doesn't work. It tells me that one or more aren't linked to each other (not true) - seems to come up with this message when it has too much info.

Ok, First, its hard for use to tell without seeing your database structure. I can understand confidentiality issues occur here, but if you could post a copy with dummy records (just put in dummy names or anything else that could identify a person) we may be able to help more.

Second, when you are dealing with several tables its sometimes necessary to create interim queries and then build to your final query. As an example I have a PO app wherein I use 7 queries to get down to the one query that has the data I want to use in my PO. So try building your query step by step. You may find, in that way, you can arrive at a query that has the data you want.

HTH

Hi

Thanks - I just managed to do that! For some reason Access wasn't letting me when I tried before...

Thanks everyone!

Glad to assist

Ooops one more thing... NOW it says I have too many fields, it won't produce my report. Is it true you can only have 10 fields in a report? I have 153 :-(( any way around this? Thanks

Whoa 153? You can definitely have more than 10, but I'm not sure what the upper limit is. I think you need to relook at your data. I doubt if you need all those fields. Are you just creating one huge query with all the fields from all the tables or are you actually looking at the fields you need for the report.