INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

CODE

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.


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

Hi,

Just curious what your resultset looks like.

But in general, add a field like

, 'table 1' as Table

...in each Select clause.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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

CODE -->

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) 

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Quote (Skip)

Just curious what your resultset looks like.

Me too...

Is it something like:

menu   table_name  rec_count  
1      apparatus    1234
2      department    543
3      news         5643
....
 
???

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.

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

(OP)
I tried it like this:

CODE

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.

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
I also tried this:

CODE

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:

CODE

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?

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

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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
When I run your sample it says I am using a reserved word. When I bracket the word [Table] 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.

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

(OP)
Sorry posting at the same time again.

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

(OP)
I changed table to Menu2 with the same result.

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

Not going to do each query but...

CODE

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

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

@cr84net2

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

Have you tried just the first query alone?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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:
ID  title  sorder   display 

"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.

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close