Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    What type of JOIN?? (2003)

    I am not sure what type of join I am looking for. Can someone please help?
    qry1-Job, Date, JC_Task, SumOfUnits
    qry2-JobNumber, Date, Code, SumOfQty

    qry1 is running via my accounting db (Pervasive.SQL) and qry2 is running via Access. I am wanting a query that will show both SumOfUnits and SumOfQty in different columns. I am not sure how to join them so i get all the information. Since the 2 qrys come from different dbs, they do not have the same date and JC_Task / Code. What I am wanting is to run the qry and see Job/JobNumber, Date, JC_Task/Code, SumOfUnits, SumOfQty. So that if there is not any SumOfUnits, I will still see the SumOfQty and visa versa.

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What type of JOIN?? (2003)

    You need to do this in two steps:

    1) Create a query that returns all combinations of Job/JobNumber, Date and JC_Task/Code. This is a union query that must be created in SQL view:

    SELECT Job, Date, JC_Task
    FROM qry1
    UNION SELECT JobNumber, Date, Code
    FROM qry2

    Save this query as qryUnion.

    2) Create a new query based on qryUnion, qry1 and qry2.

    Join qryUnion to qry1 on Job, Date and JC_Task. Double click each of the join lines in turn and select the option to include ALL records from qryUnion.
    Join qryUnion to qry2 on Job vs JobNumber, Date vs Date and JC_Task vs Code. Double click each of the join lines in turn and select the option to include ALL records from qryUnion.

    Add Job, Date and JC_Task from qryUnion, SumOfUnits from qry1 and SumOfQty from qry2 to the query grid.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: What type of JOIN?? (2003)

    Thanks much Hans

    jackal

Posting Permissions

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