Multiple queries on a report?

[aandrew]

*Ive posted this message again as first time I think I may have put it in the wrong forum*

Hi, Im new to access and have sucessfully managed to create two queries.

The first one reads all data that was enterd on a certain date (inputed by the user) and the second reads the same data but from the previous year. Now I wish to align the data against each other on the same form to do a comparrison between years.

The problem is that im unsure how to do this as whenever I try to create a report i get the following error

"you have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query"

Im not exactly sure how to fix this and would be really greatfull if someone could point me in the right direction.
thanks
Edit/Delete Message

 

[Kelemit]

Create 2 reports, one for each query.

The easy way to do this is to basically make one, make it HALF the size of the page (since you want them side by side) and design it EXACTLY how you want it to look / feel / etc.

When you have one done, copy and paste it, rename it.

Now go to the one you just copied and change its record source to the 2nd query.

So now you have rptThisYear and rptLastYear and they look identicle (sp?).
But one shows data from last year, and one from this year.

Open up another report now, and in it place both rptLastyear and rptThisyear side by side, (reason why I said to make the 2 reports HALF size) just like you would if it was a subform.

Viola, two reports side by side. Problem is, you will have to "order" your data in such a way as to make sure the reports line up.

IE: If you want to show month by month, but last year you had NOTHING in Febuary, but lots this year. Hmm.. Not sure how to line that up.

I'll have to think about that.

 

[Kelemit]

Sorry, my browser crapped out...

To finish off, what might be wisest to solve the "line up issue" of a month by month situation would be to then, on the main form, create a query that would end up display each month and year. (If you want just a monthly comparison).

Link the subforms to the main form by month.

Then each "record" of the main from will only display the sub form "records" of that month.

Alternatively, you could set the subreports query set up to have month(subreport!monthfield) = month(mainreport!monthfield)

I believe that would work.

Kelemit

 

[aandrew]

Thanks for that - I havnt actually tried it yet but it sounds like it should work