Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2014
    Posts
    16

    Unanswered: Combination of Tables without using UNION

    I have a problem with my database development which is preventing me moving forward.
    I have been tasked by my manager to develop a database that uses our Accounts systems information (which is on dbo tables) that will be able to draft our invoicing and act as a job control database. For the most part I have managed to make this work and on the original test bed where I hand entered sample data I got this all to work perfectly.
    I have since been given access to the files I need and have been trying to rebuild the database around these data sources.
    The issue I am currently struggling with is as follows;

    I have Timesheet information from our accounts system which I have queried to produce the following fields (names not exact)
    Job Number, Inspector Name, Date, Total Hours, Total Miles, Total Expenses, Inspector Grade.

    We also have sub-contractors whose timesheets do not get entered onto our accounts system which I need to interrogate at the same time to produce the invoice which is based on time input.
    I therefore have a table which I populate with this information to give me the same 7 fields as I have whittled down from our accounts system.
    I then need to combine these into one query so that I can attach the query which calculates the costs for the invoices onto it.

    I did some research and built a UNION query which accomplishes this combination task perfectly except that I cannot then use the data it creates as it has no "Fields" and it will not let me create a Make Table query from my UNION query although I was reluctant to do this as I need this to be a continually interpreted system rather than having to run it each time I want to produce an invoice.

    The two queries I am using are both the 3rd or 4th in a chain of queries designed to add information to them that wasn't present to start with, both are linked for example to a table with details about each Inspector as well as some other joins which were required to get them to the stage they are currently at.

    Can anyone suggest another way in which I can get the information to combine into one query which I can then interpret and extrapolate from?

  2. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Not sure if this meets your requirements, but save the UNION query. Then create a new query select the union query and turn that into a normal query. You can turn that into a table.

    It's not pretty sorry all I could find in my limited time.

    HTH
    Last edited by VLOOKUP; 04-23-15 at 16:10.

  3. #3
    Join Date
    Oct 2014
    Posts
    16
    Hi VLOOKUP

    I tried to query the Union query but it has no fields which can be selected and when you try and use the double click on the star and turn into a make table it just tells me there is no source information despite the fact that when you run the UNION Query you get all of the data in the seven columns.
    As I don't want to have to use a make table query I was hoping there might be another way to join the information without having to use a UNION which seems to cause more problems?

    regards

    Quote Originally Posted by VLOOKUP View Post
    Not sure if this meets your requirements, but save the UNION query. Then create a new query select the union query and turn that into a normal query. You can turn that into a table.

    It's not pretty sorry all I could find in my limited time.

    HTH

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest redesign the datamodel
    have a single table with the timesheet data
    for a PK you will need to efectivley create one for your internal system
    use the accounts system reference assuming that is unique
    create a reference for the sub scontractors.

    if you prefer add a column which identifies if the row has come fromt eh accounts system or not (for traceability)

    then your data is one table. tit ceases to require union queries or whatever.

    If you really really must have separate tables for internal and external tables then use three tables using a sub/supertype model.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2014
    Posts
    16
    Unfortunately the table with the information from our accounts system comes from an ODBC link via our server rather than a table I have created within my own database and this dbo table of data is full of other information which I have had to clean out to enable me to utilise it properly.

    I am sorry for my ignorance with regard to the shorthand terms and nomenclature as I have self taught myself the use of Microsoft Access and bits of SQL and coding to make it work.
    What does PK stand for?
    What do you mean by a "sub/supertype model.[/QUOTE]" with three tables?



    Quote Originally Posted by healdem View Post
    Id suggest redesign the datamodel
    have a single table with the timesheet data
    for a PK you will need to efectivley create one for your internal system
    use the accounts system reference assuming that is unique
    create a reference for the sub scontractors.

    if you prefer add a column which identifies if the row has come fromt eh accounts system or not (for traceability)

    then your data is one table. tit ceases to require union queries or whatever.

    If you really really must have separate tables for internal and external tables then use three tables using a sub/supertype model.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Quote Originally Posted by Harbinger View Post
    Hi VLOOKUP

    I tried to query the Union query but it has no fields which can be selected and when you try and use the double click on the star and turn into a make table it just tells me there is no source information despite the fact that when you run the UNION Query you get all of the data in the seven columns.
    As I don't want to have to use a make table query I was hoping there might be another way to join the information without having to use a UNION which seems to cause more problems?

    regards
    I'm wondering why you don't have any fields in that union that you can't access??? Just in case I am posting my steps below. Because you are right, you can't make a table directly off of a UNION query.

    I'm going to go step by step just to make sure we aren't missing something.

    First create you union query so it's listed in the queries catagory with the union symbol.

    Next make another query from the create query design, using your union query. Select the columns, don't use the star (at least I only tested on selecting the columns)

    double click those into the table. Once those are in the column view at the bottom save the query. This query should go right into the queries list.

    Relaunch the saved query off of the union (not the union query) and then make table off of that query, it will work I just did it while typing this.

    That query will be tied to create table so anytime the union query changes that will pass through the query designed off of the union and update the table. In my case table pizza.
    Last edited by VLOOKUP; 04-24-15 at 10:50.

  7. #7
    Join Date
    Oct 2014
    Posts
    16
    VLOOKUP

    I have added a print screen of what I see when I run Query Design from the Union Query.
    You can see the Union Query name and the symbol for it in the list of database items to the left. As you can see, there are no columns for me to select there is only the star.
    Any suggestions as the UNION Query runs properly and creates everything fine?
    Click image for larger version. 

Name:	UNION Query Print Screen.png 
Views:	7 
Size:	262.8 KB 
ID:	16314

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    You mentioned the names not exact, are you talking about exact in your message on the forum or not exact in the tables?

    I'm thinking if they are different they may be omitted from selecting in your query.

  9. #9
    Join Date
    Oct 2014
    Posts
    16
    Sorry, Not exact in my message. I have gone through and they are definitely exact in the tables.


    Quote Originally Posted by VLOOKUP View Post
    You mentioned the names not exact, are you talking about exact in your message on the forum or not exact in the tables?

    I'm thinking if they are different they may be omitted from selecting in your query.

  10. #10
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Just to make sure, when you UNION these two tables are both of them Access tables or is one of them from an external data source?

  11. #11
    Join Date
    Oct 2014
    Posts
    16
    VLOOKUP.

    They are both Access Queries rather than Tables. There are a number of tables which they both use to reference extra details which is why they are queries but they are definitely both built and made in Access.

    Quote Originally Posted by VLOOKUP View Post
    Just to make sure, when you UNION these two tables are both of them Access tables or is one of them from an external data source?

  12. #12
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Very clearly put thank you. I'll review and see if I can offer up a solution.

  13. #13
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I was able to recreate the scenario and get it to work.

    I created two queries based off tables (table queries) and selected the fields I planned on setting up for the union. I then created a second query based off of the second table.

    So now I have two queries pulling the information I want back. I then built a union query slamming both table queries together. Now I have two table queries and a union query. I then built another table query directly off of the union query and directed that table query selecting off of the union query to make table. I set the table up first just incase, which had 3 columns.

    The behavior, once it has ran blows away the data in the target table and repopulates it with the fresh data from the query. I went back through and updated table 1 and table 2 on seperate occasions it kept pulling the new data into the table. I didn't have to mess with the first two queries built or the union. Just execute the query that makes table.

    I would consider going to SQL view and running a select statement from there to make your table queries if you have trouble, then writing the SQL UNION based off of those queries and save that union as what you showed me on your screenshot.

    Then you could either go back and create a table query off of the union through the GUI or write something like this in SQL mode.

    SELECT * INTO [Target_Table]
    FROM [SELECT * FROM query1 UNION SELECT * FROM query2]. AS [PleaseWork];

    You can save this as a update query in your queries list and this will update the target table using SQL to union your two queries. Just make sure you set up your two table queries to match for the union.

    HTH
    Last edited by VLOOKUP; 04-28-15 at 01:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •