Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Red face Unanswered: Combine two queries

    Hello All,

    I have two seperate tables in my database they used to be in seperate database now i have them combine in one. I used to have a button setup for in both of my databases to export the query in excel format and then i would sort them seperately, now that i have both databases combine in one i would like one query exported to excel to do that i was trying to make a union query but i just cant get it to work.

    here is the sql view of my first query..........

    SELECT Issues.Title, Issues.Problems, Issues.Comment, Owners.Owner, Issues.[Opened By], Issues.[Opened Date], Issues.Category, Issues.OrderNumber
    FROM Issues INNER JOIN Owners ON Issues.Owner = Owners.Owner
    WHERE (((Issues.Status)="active"));

    Here is the second one.............

    SELECT CieIssues.Problem, CieIssues.Research, CieIssues.Suggestions, CieIssues.[Opened Date], CieIssues.DeliveryNumber, CieIssues.OrderNumber
    FROM CieIssues
    WHERE (((CieIssues.Status)="active"));

    How can i combine two of these queries together?

    Thanks again
    I am using Access 2003

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you are going to use a UNION query, the number of columns needs to be the same in each query. and they need to correspond to one another.

    You have a field called TITLE in your first query, but nothing like that in the second.

    Could you provide some sample data, and expected output?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2005
    Posts
    144

    Unhappy Add queries together into one big query

    Quote Originally Posted by RedNeckGeek
    If you are going to use a UNION query, the number of columns needs to be the same in each query. and they need to correspond to one another.

    You have a field called TITLE in your first query, but nothing like that in the second.

    Could you provide some sample data, and expected output?
    Thanks for a quick reply RedNeckGeek !!

    I have just realized if I were to make a union query that would just add them in one field which is not what i want. Instead i would like to have both queries combine in one big query.
    I am attaching my dbase here.
    How can make the existance two queries to export into one big excel file? Instead of two seperate excel files?

    Thanks again
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Can you give me a hint on how the data in the two queries is related?
    Or do you really want to see a cartesian result?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2005
    Posts
    144
    Its not related yet but i would like too. My boss used to see it seperately in two different excel reports however now he wants it in one excel file and i did that using two different queries.
    I hope i am making sense
    Thanks

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm having trouble creating a mental picture of what the data would look like in the excel report. You're going to have to give me an example for me to be any help.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jul 2005
    Posts
    144
    oh its quite really simple in the database there is a query called All active owner just make a macro and use transferspreadsheet option and use this query and export it and you will see what i mean. All its doing is taking the query and dumping it in excel file. But i want both queries to do that in one excel file instead of two seperate ones.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Just to make my point, let's simplify things a little.

    You have query1

    Select field1, field2 from table1

    and query2

    Select field3, field4, field5 from table2

    After somehow getting them both into one excel sheet, you want
    something that looks like(?):

    field1...field2
    abc.....4
    def......7

    field3...field4...field5
    john.....24.......11/1/2006
    mark....17........1/19/2007

    (That, by the way, is not possible without some VB)

    Or, maybe you're really looking for(?):

    field1...field2...field3...field4...field5
    abc.....4
    def......7
    ....................john....24.......11/1/2006
    ....................mark....17.......1/19/2007
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jul 2005
    Posts
    144

    Wink

    Or, maybe you're really looking for(?):

    field1...field2...field3...field4...field5
    abc.....4
    def......7
    ....................john....24.......11/1/2006
    ....................mark....17.......1/19/2007[/QUOTE]

    RedNeckGeek,
    I have to appologize for not being clear since the beginning, above format is the way i want to go

    Now is this way possible?
    Thanks again Bud

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You want to use something like this

    Code:
    SELECT Issues.Title, Issues.Problems, Issues.Comment, Issues.[Opened By], Issues.[Opened Date], Issues.Category, Issues.Priority, Issues.OrderNumber, "" AS Problem, "" AS Research, "" AS Suggestions,  "" AS DeliveryNumber
    FROM Issues
    WHERE (((Issues.Status)="active"))
    union all
    SELECT "","","","",CieIssues.[Opened Date],"","", CieIssues.OrderNumber,CieIssues.Problem, CieIssues.Research, CieIssues.Suggestions,  CieIssues.DeliveryNumber
    FROM CieIssues
    WHERE (((CieIssues.Status)="active"));
    Note that each side of the union has the same number of columns.
    I just used "" to fill in the blank spots. You could use Null, or 0, or
    something else if you wanted. You had columns in each query called
    Opened Date, and Order Number; so I put each of them in the same column.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jul 2005
    Posts
    144

    Cool

    Quote Originally Posted by RedNeckGeek
    You want to use something like this

    Code:
    SELECT Issues.Title, Issues.Problems, Issues.Comment, Issues.[Opened By], Issues.[Opened Date], Issues.Category, Issues.Priority, Issues.OrderNumber, "" AS Problem, "" AS Research, "" AS Suggestions,  "" AS DeliveryNumber
    FROM Issues
    WHERE (((Issues.Status)="active"))
    union all
    SELECT "","","","",CieIssues.[Opened Date],"","", CieIssues.OrderNumber,CieIssues.Problem, CieIssues.Research, CieIssues.Suggestions,  CieIssues.DeliveryNumber
    FROM CieIssues
    WHERE (((CieIssues.Status)="active"));
    Note that each side of the union has the same number of columns.
    I just used "" to fill in the blank spots. You could use Null, or 0, or
    something else if you wanted. You had columns in each query called
    Opened Date, and Order Number; so I put each of them in the same column.

    ThankYou so much RedNeckGeek,
    It works like a charm. I just have one last question OpenedBy is showing up as a number instead of names. This field is attached to a seperate table with names in it and i am seeing number instead of names in this field. I think number represents ids for each name.

    Any help on that.
    Thanks again

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You're going to have to link that table into the first half of the query,
    and then replace Issues.[Opened By] with the correct field from the new table.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Jul 2005
    Posts
    144
    Per your instruction i was able make this new statement

    SELECT Issues.Title, Issues.Problems, Issues.Comment, Issues.[Opened By], Issues.[Opened Date], Issues.OrderNumber, "" AS Problem, "" AS Research, "" AS Suggestions
    FROM Issues INNER JOIN Contacts ON Issues.First Name = Contacts.First Name
    WHERE (((Issues.Status)="active"))
    UNION ALL SELECT "","","","",CieIssues.[Opened Date], CieIssues.OrderNumber,CieIssues.Problem, CieIssues.Research, CieIssues.Suggestions
    FROM CieIssues
    WHERE (((CieIssues.Status)="active"));

    As you can see the bold part but it fails and give me error on that line
    syntax error on line
    Issues.First Name = Contacts.First Name
    On contacts table i have fields First Name and Last Name
    I hope it makes sense
    thanks
    Last edited by ranjah; 01-31-07 at 15:35.

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's why you shouldn't put spaces in field names (or table names)

    INNER JOIN Contacts ON Issues.[First Name] = Contacts.[First Name]
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Are you sure you want to join on First Name? What if you have two people
    named John? Your Contacts table should have a primary key field that
    (hopefully) corresponds to the Opened By field in the Issues table.
    Inspiration Through Fermentation

Posting Permissions

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