REORTS USING MULTIPLE QUERIES

[triplettg]

With the information Donnie provided at the recent seminar I’ve been able to create a report of my basic worksheet for handicapping, which saves considerable time relative to my old procedure of dumping data from Access to Excel.

Now I would like to create a report of possible spot plays from about five different queries. I would prefer to have one report rather than five.

After selecting “NEW” and “REPORT WIZARD” you are asked to select the fields you want to include. At this point it is also indicated that you can chose more than one table or query, but it does not say how to do this. I have tried to select multiple queries using the ctrl key and also the shift key without success.

I would appreciate assistance from Donnie or anyone else that is knowledgeable in this area.

[Rick]

One way you can do it is to put all your queries into one query.

That means putting the other queries on the OR lines.

The easiest way I found to do it is to add the next OR line. Now look at it in SQL view. Determine where the info is going.

Now open up the other queries in SQL view and copy the criteria line and paste it into the master query in SQL view.

You may have to play with it a little to figure out what works.

I found that much easier than having to type in each line of criteria.

That may get you started. Then again it just might be enough to mess you up.

Make copies of everything before trying. :D

[Donnie]

One other thing you could do if you want to keep the queries seperated out:

1. Design all your reports first as individual reports. If there are 3 different queries I would design 3 different reports. Keep the reports short and to the point. Don't do a lot of fancy formatting with them.
2. Begin a new report, but DO NOT pick a table or query to "attach" to it (first step of creating a new report).
3. After opening the new, blank report, open up the toolbox....the button on the toolbar looks like a hammer and wrench. Hover over the different tools until you find the Subform/Subreport button. Click the button ONCE to turn the tool on. Put your cursor over the detail area of the new report and hold down your mouse button and drag across and down. A wizard will open that will ask you which form or report you wish to insert into your new drawn area. Repeat this for the other reports you wish to have on one sheet. It will take some playing around to format it the way you want it...but ain't that the beauty of Access?? Just learning this stuff!

[MikeDee]

I'm not a expert on report writing, jsut know enough t get by. But I just tried the report wizard. There is a pull down menu where you pick the table or wizard, then you move the fields that you want from the query into the report.

Next all you need to do is to go back to the pull down menu pick a different query and then you can move fields into the reprot from the second query.

I didn't continue with the test because I do not have 2 queries I want to put into a single report, but give it a try. let me know if this works for you.

[Donnie]

Mike-
I think you run into problems when you try to add fields from the second query....I had prblems in the past doing that...maybe it was just me!!:D

[MikeDee]

You are right Donnie I tried to take the next step and it won't work. Looks like you can only pull reports from different tables if the tables are properly joined under the relationships tab. the wizzard does a lously job of explaining the conditions where pulling data from mutiple queries or tables will work.

[triplettg]

Rick, Donnie, Mike

Thanks for the input. I’ll experiment a little more and let you know if I have any success.

[hurrikane]

Maybe I'm missing something.

If you create a separate report for all your queries.
Close and save the reports
Open a blank report in design view.
Select your individual reports and drag them onto the blank report.
This puts all your reports on one report.

Couple of things

1. when you create the individual(Sub reports)reports you need to open them in design view and shrink them so you just see the minimum. this way you can get them all on the main report as little bars.

2. you will have to play around with the titles in the report and where the data goes. I believe you put the titles in the Page header area they list the report names at the beginning. You may also have to move the field heads to a different place so they appear on the new report.(maybe also page head)


Is this the type of thing you are trying to do?


PS. If I'm way out of line just ignore me. Happens all the time. Something about getting older.

[triplettg]

HK-

You’re not out of line at all. I’ve had some success with your approach, which appears to be basically the same as Donnie’s. I’ve been able to consolidate all my individual reports into one report. The individual reports appear as sub-reports however, and I was hoping to integrate the data so that I could sort all of the data by track and race number. Whether or not this can be done, I’m not sure.

I do have one small problem with my new master report. I’m getting a message that the “section width is greater than the page width” and as a result the odd pages of the report are blank. I’ve played around with the formatting and sizing without success. It’s either too late at night or I’ve reached my level of incompetence. In any event I’ve learned a little bit more about Access.

Thanks to all of you for your help. Rick, your approach appears to be the easiest to implement but unfortunately I have too many OR lines in the various queries to be able to consolidate into one master query.

[Donnie]

In your new master report that error message is telling you that the design area...the gray area...of your report is wider than the page you are printing on. You can either go to File to Page Setup and change to landscape print mode, or go back into the design view of your report, place your mouse on the far edge of your report, when it turns to a double headed arrow, click hold and drag to the left. If there are any fields that extend out to the edge of the report, they must be moved back to the left first. The edge will only move if there are no fields in it's way. Also this goes for any formatting you may have in your report! Any horizontal lines must be shortened first!

As far as getting all data in one report, in the past people have wanted to keep the data seperate, so they know which horses came from which queries...I assumed that is what you wanted....do you not care which query threw which horse? Then do it the way Rick described!

Hope this helps!

[hurrikane]

Gary,
probably one of your sub reports is to long. be sure to change the margins on the sub report too.
as for sorting by track etc...and keep the same query. you will likely have to put them in one query or export the to excel or word and move them around.

[Donnie]

Why use excel or word when you can use a Make Table Query and an Append Query and stay in Access?

[triplettg]

Thanks again for all the help. With everyone’s assistance I’m much further along then when I tried to use an Access manual which is about three inches thick. Surprisingly enough “ACCESS FOR DUMMIES” didn’t work either.

Donnie-

For the particular report I’m working on I really don’t need to identify the specific query used. I’ve identified four factors I use to flag potential long shots (actually I should give Ken credit for the identification). By studying these four factors for various surface, distance and class combinations I’ve settled on the most promising queries. Consolidating the results of these queries in a daily report provides a list of potential long shots and I can easily identify which factor(s) caused a particular horse to appear in the report.

Consolidating the individual reports in a master report works quite well. It saves paper and only requires one print command. But once I got to this point I got greedy and thought I could take the next step and have the report grouped by track and race number.

I’d better get back to handicapping before I acquire another hobby--playing around with Access. Handicapping and golf are frustrating enough.

I’m truly appreciative of the time you have taken to assist me. The congenial nature of the HTR group truly amazes me. I browse the PA site occasionally and am appalled by the backbiting, the personal attacks and the evident feelings of self importance which exist on that board.

[Donnie]

you probably can get it grouped by track and race number....

the beauty of taking classes is to have someone right there to answer the question rather than scouring a 500+ page manual...you can get straight to the problem and directly to the solution!

if you want, I can always look at your database. If you empty out all the races you can send it along. It would give me an idea of your reports and the queries they are based on. If you are interested email me at mrdonn@hotmail.com and I will forward you my home email. My hotmail tends to fill will crap so I have people send me their files to my home email!

Don't worry about me stealing your work....what I use has served me well for the past year and a half and I have no interest in deviating from it!

BOL