Display filtered data in a table 1

[rinos2]

Hi All,

I need to display results from a query based on a a certain field (the field is state in my case). I need to display the same fields in the table but the filter criteria is different (in first case I need to display all fields from the query where state is like California and in the second case I need to display all fields from the same query where state is not like california). I need to show the table header on each page for the results to be displayed. I tried to use grouping but it does not seem to work for me. Please let me know how I can resolve this problem.

Thanks.

 

[sxschech]

a UNION query?
Open your existing query in SQL view, copy it, remove the semi colon and hit enter, type the word UNION
and then paste. Then you can change the second where clause to be not like California.

For the grouping, you may want to consider adding a new field to your query:

If you are in SQL view use this:

iif(State="California","Calif","Not Calif") AS StateGroup

If you are In Designview use this:
StateGroup:iif(State="California","Calif","Not Calif")

Then in your report, you can sort on the field StateGroup

 

[rinos2]

Thanks a bunch for your tips on soving this problem!