How does one join several queries to make one dynaset?

[Lee Redfern]

I have 3 different queries that run on the same table. One query has 7 rows, one has 2 rows and one has 1 row. Since it appears to me that Access has a limit of 9 rows in any one query, and my 3 queries have 10 rows between them, is there a way I can "join" the 3 queries in 1 and run it so that the result of the 3 separate queries will result in 1 dynaset that will contain the result of all 3 separate queries? If that's possible and anyone can tell me how to accomplish that, it would be much appreaciated. Also, is there a way to create a field in a query that if the conditions set are matched, will print out text (i.e., a description of the match, such as "Non-Maiden Fast Dirt Sprint")? If so, can someone tell me how to accomplish that also? Thanks for any help you can give me.

[HBee]

I have 3 different queries that run on the same table. One query has 7 rows, one has 2 rows and one has 1 row. Since it appears to me that Access has a limit of 9 rows in any one query, and my 3 queries have 10 rows between them, is there a way I can "join" the 3 queries in 1 and run it so that the result of the 3 separate queries will result in 1 dynaset that will contain the result of all 3 separate queries? If that's possible and anyone can tell me how to accomplish that, it would be much appreaciated. Also, is there a way to create a field in a query that if the conditions set are matched, will print out text (i.e., a description of the match, such as "Non-Maiden Fast Dirt Sprint")? If so, can someone tell me how to accomplish that also? Thanks for any help you can give me.

Hi Lee,

Welcome aboard.

There is a thread "Spot Play Report" several posts below this one that might help with your problem. It deals with putting several querries into one report. If you follow it to the point that you have all of your queries appending to a new table, then create a report on that table, it may do what you need.

I hope that helps.
I gotta go back to getting my but kicked in the handicapping contest :D

Herman

[Donnie]

Lee,
As per your second question, you could post a line like the following into a new column in your query design grid....this replaces a number from the Class field with text in it's place! What is in between the quotation marks can be replaced with anything you wish! Look up IIF functions to get a complete explanation in the Access Help section.

Class:IIf([HX4].[nClA]=1,"STK/ALW",IIf([HX4].[nCLA]=2,"CLAIMING",IIf([HX4].[nCLA]=3,"MSW","MAIDEN CLAIMING")))


Now I gotta go and kick Herman's butt a little more in the contest! :D
(just kidding Herman!!)

[HBee]

Now I gotta go and kick Herman's butt a little more in the contest! :D
(just kidding Herman!!)

Yeah!, but I got a shot at Dixie!! :rolleyes:

[MikeDee]

Lee

As to your first question on the number of rows. A query can have more then 9 rows. Access just shows 9 rows on the design screen.


To add more rows position your cursor to the left most edge of the first column in one of your rows. A black small thick arrow will appear. Then right click and hold and move the cursor down the number of rows you want to add.

This sequence will highlight the number of rows you select.

Now go to the insert command on the top line and click and then click on rows and Access will insert the number of new rows you have highlighted.

You can control where they are inserted, by where you position your cursor when you start the sequence. If you start in the first row they will be inserted first.

By selecting in this fashion you can add or delete rows to your query and you can copy and paste. Rows from one query to the next.

I use the cut and paste to build OR queries. I will build the first condition in a query. Then I will build the second condition in a new query. I do this to see the second condition by itself so I can make sure it is working like I want. Then I just copy and paste the second set of rows to the first query. (this is very helpful if yoru OR conditions for each set has multiple rows)

If you should try the copy and paste you have to be VERY sure the column headings are the same in both queris as access will just past the data in the row without regard to the field name in the column.

Also this sequence works on columns as well.

[Donnie]

Don't kick my dog too hard Herman! She's been taking a beatin' already! Good luck in the last two days here!

[Lee Redfern]

Mike: Thanks for the information. Apparently, Access works like Excel regarding insertion of columns and rows. The columns I had already figured out, but the rows, I had not, nor did I know one could copy and paste the rows. That's good to know as it will simplify matters greatly and (if the field names and ordering are identical in all queries) it will create more accuracy as well. Again THANKS.