Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries (2000-xp)

    Ok, I am so in need of help!
    I have two tables - no gst and yes gst which I have combined into an excel spreadsheet so you could see it.
    The tables have all the same fields but some of the data is different. If I copy and paste the data i will end up with four copys of a unique value [field2]. I want to see all [field2] that has GST values or no GST values. I should really only see one GST values (0 or the highest number) for each [field 2]

    BTW field 2 is a flight confirmation number.

    I know Hans is a master with these types of things.

    I have tired a union but it is returning too many duplicate
    SELECT [field1], [field2],[field3],[field4],[field5], [field6],[field7],[field8], [field9],[field10], [Baseairamt], [Basetaxamt], [Ticket Amount], [GST]
    FROM [Yes gst]

    UNION ALL SELECT [field1], [field2],[field3],[field4],[field5], [field6],[field7],[field8], [field9],[field10], [Baseairamt], [Basetaxamt], [Ticket Amount], [GST]
    FROM [No gst];

    Example:
    179926 has no GST which is correct
    179884 is returning 4 time with the GST being $0, $34.37, $2.87, $0. The correct answer should be $34.37

    Thanks!

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

    Re: Queries (2000-xp)

    You will need several queries for this, but it is not clear to me what exactly you want to return. Take Field2 = 179884, for example. There are two records with this number in [Yes GST] and two in [No GST]. You have explained which value you want to return for GST ($34.37), but which values do you want to return from the other columns, in particular columns E, F, G, H, K, L, and M? Those from the record for which GST = $34.37? And what about records that occur only in [No GST] - which one of multiple records do you want to return?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (2000-xp)

    Oh Good Question Hans!

    I want the whole record that has 34.37 to come with it. So, if the gst is the highest than that record stays.

    I hope that answers your question.

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

    Re: Queries (2000-xp)

    OK, that's clear. But what about my other question?
    <hr>And what about records that occur only in [No GST] - which one of multiple records do you want to return?<hr>
    In that situation, one can't pick the record with the highest GST, since there is no GST! How to decide?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (2000-xp)

    I never even thought of that! How about the newest one. So if you pick the highest ID number that will work!

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

    Re: Queries (2000-xp)

    Which column is the ID field?

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (2000-xp)

    Hans,

    I have an ID field in both tables. The ID field is an autonumber. I may not have that included in my excel spreadsheet as i typically hide the ID fields. Sorry the field is called "ID"

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

    Re: Queries (2000-xp)

    I keep on asking questions. I noticed that there is a record in [Yes GST] with a negative GST amount. There is a corresponding record in [No GST]. What do you want to return for this couple: the negative amount from [Yes GST], or does the missing GST from [No GST] count as 0 and should that be returned?

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (2000-xp)

    Well i have to say you are great at teasing me with an answer!

    Well the negitive numbers are refunds but I still want them to be dominate over the zero values.

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

    Re: Queries (2000-xp)

    I'm sorry to have asked so many questions, but the situation you described in your first post wasn't clear to me, so I had to try to clarify it through question-and-answer.

    I have attached a demo database with what I think is the solution. It consists of 6 queries:
    qryYesGST selects the highest GST amount for each value in column B ("Veld2" in the table) in [Yes GST].
    qrySelectYes combines [Yes GST] and qryYesGST to return the complete record for each value in column B with the highest GST Amount.
    qryNoGST selects the highest ID for each value in column B in [No GST].
    qryNoGSTOnly selects the records from qryNoGST that don't have a match in [Yes GST].
    qrySelectNo combines [No GST] and qryNoGSTOnly to return the complete record with the highest ID and no match in [Yes GST].
    qrySelect is a union query that returns all records from qrySelectYes and qrySelectNo.

    Phew!

Posting Permissions

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