Results 1 to 10 of 10
  1. #1
    MFS is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2009
    Location
    Ohio
    Posts
    235

    Union Query or Multiple Queries.???

    I have an issue with a slow running Query and I believe because it is a Union Query.
    My question is I have 8 tables that are being "unioned" in this query. The user types in data for the criteria in text boxes on a form and hits a button to run the query. With this being said, I would like to build 8 separate queries and then add a listbox on my form that chooses which query I want the rest of the text boxes to generate criteria for that chosen query. Gosh that was complicated to get out.


    Can someone help me with this if it can be done?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Let's take a step back. The issue may be a database design issue.
    If you have 8 tables that are being "unioned" in a single query, that implies that the tables probably have similar structures.
    If that is the case, why do you 8 similar tables instead of just 1 table?

  3. #3
    MFS is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2009
    Location
    Ohio
    Posts
    235
    Good question,
    Wew!! this is very difficult to explain.
    To give you a brief understanding of my project, I'm using a data transaction manager from Rockwell Automation Called 'Factory Talk Transaction Manager' which pulls data from my plant floor PLC's into SQL Server 2008. And then I designed user forms and queries in Access for the front end, basically to make it easier for the user rather than them writing script in SQL Server.
    I have 8 devices on the floor that send data to FTTM every 20 seconds when an output bit, (unscheduled event) goes high in the PLC logic. The transaction manager then takes that bit and knows that it should pull data from it's assigned string and integer files and then dumps it into the corresponding table. When a different bit goes high it knows to pull data from other string and integer files and stores that data into another table. Simply put, in FTTM I can only have one unscheduled event or one PLC output bit per device which forces me to have 8 separate tables.
    If I used only one unscheduled event for all 8 devices, so I only had one table, it would pull data from all string and integer files and I may not necessarily need all that data at that particular time.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    this is very difficult to explain.
    Yes, I think you have thoroughly confused me. I am still not sure I understand the need for eight separate tables instead of one.
    Here are some other suggestions. I am not sure if they will work for you, because I do not have a clear understanding of the issue.

    Is there any chance you could write the data to eight Temp tables, and from there write all the data to one, all encompassing table?

    Or, another alternative if is you are having performance issues with a Union Query of eight tables, you could write the eight tables to a single Temp table, and then run your final query off of that.

    Or, you could have a series of Text Box selections, and then use VBA to build the SQL code of the query you need and run that.

  5. #5
    MFS is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2009
    Location
    Ohio
    Posts
    235
    I'm going to give this some further thought and provide follow up with my results. However I may try concatenating all 8 tables into one in SQL.
    Thank You,
    Mike

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    However I may try concatenating all 8 tables into one in SQL.
    Give it is a shot. If performance isn't bad, then that should be fine to do that.

    Just note that UNION queries are Read-Only, so you would NOT be able to update any data from a UNION query. If you are just trying to view the data or Report on it, that shouldn't be a concern.
    Last edited by JoeM; 08-28-2014 at 12:54 PM.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think Joe meant
    "...so you would NOT be able to update any data from a UNION query."

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Nice catch ssnafu. That one word really does change the whole meaning, doesn't it?
    I went back and edited my reply, so as not to confuse anyone.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Boy, your computer doesn't know how to spell....
    (its ssanfu - totally different meaning) Hahahahaha

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Boy, your computer doesn't know how to spell..
    What can I say, it was one of those days...
    I guess when it reigns, it pores!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Union query to join two crosstab queries
    By racefan91 in forum Queries
    Replies: 5
    Last Post: 09-26-2013, 10:24 AM
  2. Replies: 1
    Last Post: 10-23-2012, 02:04 PM
  3. Union query- 9 Queries
    By JessieBee in forum Queries
    Replies: 3
    Last Post: 07-14-2012, 05:46 AM
  4. One filter for 3 Queries in Union Query
    By rlsublime in forum Access
    Replies: 3
    Last Post: 03-20-2012, 04:01 PM
  5. Union Query and multiple entries
    By krutoigoga in forum Queries
    Replies: 1
    Last Post: 07-08-2011, 07:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums