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.

 

[SkipVought]

Hi,

Just curious what your resultset looks like.

But in general, add a field like

, 'table 1' as Table

...in each Select clause.

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

 

[cr84net2]

Currently, I am not getting a resultset. This version is of the query is generating this browser error: You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function. Sorry I am not skilled at using the query tools in Access. Almost everything I do is as a web designer. I will try to add the field in each select and let you know how it turns out.

Thanks

 

[cr84net2]

After making the changes my select looks like below and is returning the same result.

select 1 AS menu from(Select Count(*) AS Acnt, 'table1' AS app, 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, 'table2' AS dept, 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, 'table3' AS new, 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, 'table4' AS pub, 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, 'table5' AS train, ID, title, sorder From training_center WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)

 

[SkipVought]

Put that additional field in the OUTSIDE Select, rather than the inside.

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

 

[Andrzejek]

Just curious what your resultset looks like.

Me too...

Is it something like:

[pre]
menu table_name rec_count
1 apparatus 1234
2 department 543
3 news 5643
....
[/pre]
???

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.

 

[cr84net2]

I tried it like this:

select 'table1' AS app from(Select Count(*) AS Acnt, ID, title, sorder From apparatus WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)          
UNION ALL                                                                                                                                                          
select 'table2' AS dept from(Select Count(*) AS Dcnt, ID, title, sorder From department WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)        
UNION ALL                                                                                                                                                          
select 'table3' AS new from(Select Count(*) AS Ncnt, ID, title, sorder From news WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)               
UNION ALL                                                                                                                                                          
select 'table4' AS pub 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 'table5' AS train from(Select Count(*) AS Tcnt, ID, title, sorder From training_center WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)

I have the same error as before.

I believe after doing some additional reading that I am missing a Group By statement or I should be using a field instead of an * in the count function. I will try using an alias for the tables and use the alias.ID in the count function and let you know how that works.

Thanks again, I appreciate the help.

 

[SkipVought]

select 1 AS menu, 'Table 1' As Table 
from(Select
  Count(*) AS Acnt
, 'table1' AS app
, ID
, title
, sorder 
From apparatus 
WHERE display='1' 
ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID
)
UNION ALL
select 2, 'Table 2'
 from(Select Count(*) AS Dcnt, 'table2' AS dept, ID, title, sorder From department WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID) 
UNION ALL 
select 3, 'Table 3'
 from(Select Count(*) AS Ncnt, 'table3' AS new, ID, title, sorder From news WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)
UNION ALL
select 4, 'Table 4'
 from(Select Count(*) AS Pcnt, 'table4' AS pub, ID, title, sorder From public_inf WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)       
UNION ALL
select 5, 'Table 5'
 from(Select Count(*) AS Tcnt, 'table5' AS train, ID, title, sorder From training_center WHERE display='1' ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)

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

 

[cr84net2]

I also tried this:

select 'table1' AS app from(Select Count(app.ID) AS Acnt, app.ID, app.title, app.sorder, app.display From apparatus AS app WHERE app.display='1' ORDER BY IIF(ISNULL(app.sorder), 999999, app.sorder), app.ID)
UNION ALL
select 'table2' AS dept from(Select Count(dept.ID) AS Dcnt, dept.ID, dept.title, dept.sorder, dept.display From department AS dept WHERE dept.display='1' ORDER BY IIF(ISNULL(dept.sorder), 999999, dept.sorder), dept.ID)
UNION ALL
select 'table3' AS news from(Select Count(news.ID) AS Ncnt, news.ID, news.title, news.sorder, news.display From news AS news WHERE news.display='1' ORDER BY IIF(ISNULL(news.sorder), 999999, news.sorder), news.ID)
UNION ALL
select 'table4' AS pub from(Select Count(pub.ID) AS Pcnt, pub.ID, pub.title, pub.sorder, pub.display From public_inf AS pub WHERE pub.display='1' ORDER BY IIF(ISNULL(pub.sorder), 999999, pub.sorder), pub.ID)      
UNION ALL
select 'table5' AS train from(Select Count(train.ID) AS Tcnt, train.ID, train.title, train.sorder, train.display From training_center AS train WHERE train.display='1' ORDER BY IIF(ISNULL(pub.sorder), 999999, pub.sorder), pub.ID)

I had the same result. error: You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

I also tried:

select 'table1' AS app from(Select Count(app.ID) AS Acnt, app.ID, app.title, app.sorder, app.display From apparatus AS app WHERE app.display='1' ORDER BY IIF(ISNULL(app.sorder), 999999, app.sorder), app.ID)
UNION ALL
select 'table2' AS dept from(Select Count(dept.ID) AS Dcnt, dept.ID, dept.title, dept.sorder, dept.display From department AS dept WHERE dept.display='1' ORDER BY IIF(ISNULL(dept.sorder), 999999, dept.sorder), dept.ID)
UNION ALL
select 'table3' AS news from(Select Count(news.ID) AS Ncnt, news.ID, news.title, news.sorder, news.display From news AS news WHERE news.display='1' ORDER BY IIF(ISNULL(news.sorder), 999999, news.sorder), news.ID)
UNION ALL
select 'table4' AS pub from(Select Count(pub.ID) AS Pcnt, pub.ID, pub.title, pub.sorder, pub.display From public_inf AS pub WHERE pub.display='1' ORDER BY IIF(ISNULL(pub.sorder), 999999, pub.sorder), pub.ID)      
UNION ALL
select 'table5' AS train from(Select Count(train.ID) AS Tcnt, train.ID, train.title, train.sorder, train.display From training_center AS train WHERE train.display='1' ORDER BY IIF(ISNULL(pub.sorder), 999999, pub.sorder), pub.ID)
GROUP BY app.ID, app.title, app.sorder, app.display, dept.ID, dept.title, dept.sorder, dept.display, news.ID, news.title, news.sorder, news.display, pub.ID, pub.title, pub.sorder, pub.display, train.ID, train.title, train.sorder, train.display

With the same result again. This is really frustrating. Any Ideas?

 

[cr84net2]

Skip, we must have been posting at the same time. I will give your query a try.

 

[SkipVought]

BTW, you cannot have an As alias in following union queries: Only ONE ALIAS!

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

 

[cr84net2]

When I run your sample it says I am using a reserved word. When I bracket the word

that error goes away and I am back to the error: You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

 

[SkipVought]

did you try to use another more appropriate word???

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

 

[cr84net2]

O.K. Maybe I need to start from scratch. I have five tables that are identical. apparatus, department, news, public_inf and training_center. I need to create a recordset with the following fields from each table. ID, title, sorder and display. display needs to ='1' and I need to sort each table by sorder. It is possible that a table could be empty or have no records. I am gathering the count for each table so I can create a menu with the results and I need to know when to finish with each table. Is there a better way to do this other than a Union All query? Thanks again for your help.

 

[cr84net2]

Sorry posting at the same time again.

 

[cr84net2]

I changed table to Menu2 with the same result.

 

[SkipVought]

Not going to do each query but...

select 1 AS menu, 'Table 1' As Table 
from(Select
  Count(*) AS Acnt
, ID
, title
, sorder 
From apparatus 
WHERE display='1' 
GROUP BY
  ID
, title
, sorder 
ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID
)

Furthermore, I think I'd do it this way

Select
  Count(*) AS Acnt
, ID
, title
, sorder [b]
, 'Table 1' As menu1[/b]
From apparatus [b]
GROUP BY
, ID
, title
, sorder [/b]
ORDER BY IIF(ISNULL(sorder), 999999, sorder), ID)
UNION ALL
.....

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

 

[SkipVought]

@cr84net2

Tip: When you have multiple complex task items, get one thing working first. Work from the inside out adding new features.

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

 

[cr84net2]

Sorry, still no luck. I am going to step away from this and reevaluate how I have this set up. Perhaps I should merge the tables and add a field that identifies each as either apparatus, department etc. I truly appreciate your help, and you sticking with me on this. I will post again if I resolve this using the union all query. Thanks.

 

[SkipVought]

Have you tried just the first query alone?

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