Create recordset from 5 tables with identical fields then reference the records by table

[cr84net2]

I need to create a recordset from 5 tables with identical fields then reference the records by table. I also need the record count for each table. I am using this for a menu and I have been banging my head against the wall. Any help would be appreciated.

This is the select statement I am currently working with.

select 1 AS menu from(Select Count(*) AS Acnt, ID, title, sorder From apparatus WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)
UNION ALL" _
select 2 from(Select Count(*) AS Dcnt, ID, title, sorder From department WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)
UNION ALL" _ 
select 3 from(Select Count(*) AS Ncnt, ID, title, sorder From news WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)
UNION ALL" _
select 4 from(Select Count(*) AS Pcnt, ID, title, sorder From public_inf WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)      
UNION ALL" _
select 5 from(Select Count(*) AS Tcnt, ID, title, sorder From training_center WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)

I need to sort each tables records individually so I am trying the sub select for each table. I have tried several different approaches with out any luck. I did alias the table names and applied the alias to each field but this version was easier to read for this post. Anything that would point me in the right direction would be appreciated.

 

[Andrzejek]

Could you share with us what the outcome should look like?

"I need to create a recordset with the following fields from each table. ID, title, sorder and display. display needs to ='1'"

So is it:[pre]
ID title sorder display[/pre]

"I am gathering the count for each table" from this new recordset? Or do you want to have another field in your recordset indicating how many records you have in each table?

It would be beneficial to see an example of data in a couple of tables with just a few records, and what the outcome should look like. IMHO

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Make sure each individual query gives an expected result BEFORE joining them!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]