I need to join 5 tables with identical fields then reference the records by table

[cr84net2]

I need to join 5 tables with identical fields then reference the records by table for a menu on a website. The database is access (I know). I have tried literally hundreds of different approaches with no luck. Here is the Select statement that I am currently trying.

dSQL="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 am using the individual select statements so I can sort each tables records individually (at least that is what i understand). I am currently getting a "Syntax error in FROM clause." error.

Any help at all would be greatly appreciated.

As suggested I will move this to the forum701: Microsoft: Access Queries and JET SQL

Thanks, everyone.

 

[ChrisHirst]

You need to write the concatenated query to the browser instead of sending it the the SQL server, so you can actually see what value or field is missing or misplaced, ....


Then, if you cannot see the problem ... ask the question in the forum for whatever database server you are sending the query to and provide the actually query, not server side code where no one knows what the field types are or the values you are sending.




Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[Borvik]

This is really a query problem - not an ASP problem. As it is Access related, check out forum701.

 

[guitarzan]

Just glancing at it, the end of the first line is missing a closing-parenthesis after the ID, whereas all the other lines have it.

 

[guitarzan]

Reposted here: thread701-1760367