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

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

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

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

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

CODE

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: Microsoft: Access Queries and JET SQL: Microsoft: Access Queries and JET SQL

Thanks, everyone.

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

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

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

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.

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