Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    IF/Than in append query (2002)

    Hi!

    This one is way outta my league and I need advice before I spend hours trying to figure out the best approach. I have a temporary table that stores data and which would need to be appended to a similar table that is being used as a history table. The temporary table may be used multiple times during the day by different people. The temporary table imports information from Excel. The data will often times repeat itself as well as contain new data. There is an [ID] in the temporary table however it will change each time the data is imported so it can't realistically be used to be sure duplicate data is not entered into the history table. The goal is to enter distinct data into the history table. [clientname] and [care_date] when used together would be distinct. If I use "not in [SELECT DISTINCT]....." for either of these rows, then data gets eliminated that should not. It is the combination of these two fields together that will set up the correct distinct scenerio. Is it even possible to do that and if so how do I do it??

    Thanks,
    Leesah

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF/Than in append query (2002)

    Make an append query like you see in the attachment. Link all fields to each other except the ID field form the destination table (History table).
    Make all relations between the fields showing All Records from the Append From Tables (Temp Table).
    Add the Id from the history table to the query and add a condition Is Null.
    This should do it.
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF/Than in append query (2002)

    Hi!

    Well I'm psyched there is a way to do this! I "think" I set up the query like the example but nothing is going into the history table. The history table is now empty. I emptied it to be sure data was going in and then not replicated each time I ran the query. I'm not sure where I'm going wrong. The table should update with records except when the [care_date] AND [clientname] are the same.

    Thanks,
    Leesha

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF/Than in append query (2002)

    Maybe my names in my sample were not very clear.
    You tried to append the history table to the other.
    If you only care about client name and care date you have only to set the relations on these two fields.
    Here your modified db.
    Francois

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF/Than in append query (2002)

    WOW! That is so wonderful. Thanks Francois!

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF/Than in append query (2002)

    Back again. I tried duplicating this process to the attached query and it's not working. The history table seems to be adding records X 3 ---- for each of the fields that I have linked. In this scenerio, records with the same [employeename], [sub_skill] and [care_date] should not be duplicated in the history table. What am I doing wrong??

    Thanks,
    Leesha

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IF/Than in append query (2002)

    If your temporary table contains duplicate records then you need to write another query that selects the unique records you want to append, then use this query as part of the append query rather than the temp table.

    The problem is that the decision about what to append is not made line by line as each line is processed. Instead the records to append are selected fromthe state of the tables at the start of the process.
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IF/Than in append query (2002)

    Check the differences between your query and the one I amended.

    On downloading Francois's example he shows you what to do. Your problem in the query you have is that you are using the History.ID to test for null and to setup this field in the history field as well.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF/Than in append query (2002)

    Thanks! The temp table was not supposed to have duplicate records unless the times were different on the care date. I hadn't picked up on this while testing. Thanks so much!

    Leesha

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF/Than in append query (2002)

    Hi Pat,

    Having a separate example to look at helped me to understand what I was missing. I've just duplicated it in a third table without any problems.

    Thanks!
    Leesha

Posting Permissions

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