Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: series of queries (Access97)

    The nasty part is removing the duplicates - the duplicate wizard query will give you a set of records where there are two or more duplicates, but deleting them is usually done manually. How many duplicates do you think you have? If it's lots (100s) then it may make sense to write some DAO code to step through the duplicate set of records and delete the first instance of each. But usually they aren't exactly duplicates - different timestamps or something and you may want to use some other criteria to decide which one to delete. Once the deletes are done, the append queries can be run. To eliminate the annoying message, simply do a SetWarnings False in a macro or in code (but be sure to turn it back on), and run the 4 queries. If any of this doesn't make sense, post back and we'll try to help.
    Wendell

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    Just make a macro (I know, I hate macros too, but it's a quick solution). Start the macro with SetWarnings and turn warning off. Then put OpenQuery statements for each query. The last argument should be another SetWarnings to turn them back on.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: series of queries (Access97)

    If you want to be lazy (and I am sure I will recieve some comments on this), use a Select Distinct or Select Distinct Row query on the final dataset to get the all unique combinations without the need to delete the duplicates. I find this easier then writing the code to eliminate duplicates.

    HTH
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    Here is an answer to part of your question.

    To turn the warnings off and to run the append queries you code like this:

    <font color=448800>'Turn the warnings off</font color=448800>
    DoCmd.SetWarnings False
    <font color=448800>'Run Stored queries</font color=448800>
    DoCmd..OpenQuery "QueryName"
    DoCmd..OpenQuery "QueryName2"
    <font color=448800>'Run a query built in code
    'strSQL contains the SQL statement</font color=448800>
    DoCmd.RunSQL strSQL
    <font color=448800>'Turn the warnings back on</font color=448800>
    DoCMD.SetWarnings True

    You will need to follow the lead of others for the deleting duplicate records.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    series of queries (Access97)

    <P ID="edit" class=small>(Edited by charlotte on 22-Jan-03 19:27. to activate link to prior post)</P>Regarding my old post # <!post=54573,54573>54573<!/post>. I took jscher's advice and have come up with 4 append queries that will append the eventID and DrID to tblapproval. I need help in constructing the on click event of a button that will loop through each of the queries, remove duplicates (especially since the combination of eventID and DrID represent the primary key), and then append the records without that default msgbox popping up telling the user they are about to append 5000 rows. Ok well not really 5000 but the point is that it's annoying and it's inappropriate to see that box in the application. Point is that the SQL's work... and work well... so... if code is necessary and append queries are ill advised... i'd appreciate knowing that. thanks for any help. Jenn.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    Hmmm... any chance a union query will work? or maybe a line of code which after the second and subsequent append queries tells Access to check for duplicates? i think that if a duplicate is appended there will be an error msg that says you've violated a primary key rule or something equally informative... Maybe appending to a temporary table, removing duplicates and then appending to tblapproval, once the data is all cleaned up (i would not even know how to begin that in code) but i think it's plausible. Oh and I even considered a macro (LOL) but as soon as I started, the thought was readily supplanted with an urge to ask the lounge for HELP! Anyway, these are the ideas that plague me today... I appreciate what you've all shared so far... any further comments or referrals are welcome.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: series of queries (Access97)

    One question - is this a one-time process just to clean up your database, or are you planning to do this on a regular basis?
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    This will be done with regularly, with each event/transaction that is recorded in the database. These queries represent a set of business rules that when combined, will produce a list of people who need to be sent a report. Every contribution I make, about 300 per year, will be passed through these queries. Thanks Wendell

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: series of queries (Access97)

    Since that's the case, I think you may want to rethink the data entry process, and prevent duplicates from ever being entered. There are a couple of ways of doing that, and if you build forms that deal with your new structure, you shouldn't really need to run the action queries you were originally concerned about. If this doesn't make sense, let's explore exactly what you need to do each time a contribution is recorded.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    You've got me thinking Wendell - do you suggest using forms based on the queries to filter tbldoctors versus the append queries. i'm not sure how that would work but i'm willing to try. all the background on the db is in the earlier post.... the reason duplicates will arise is because the records from tbldoctors are selected based upon multiple criteria. (position on a committee, which committee they are on, (there are 4 committees), location in the state, title in our organization, relationship to a legislator, because of this, a doctor could very well end up more than once in the resultant recordset. Upon looking back at jscher's original response to my post... i think changing from append queries to update queries using a yes/no field, then appending after all are tagged, might be a better idea. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Whichever way it happens... the EventID and DrID must end up in tblapproval but it is not an option to manually select the doctors, say from a drop down list... that's work the 'puter needs to do.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: series of queries (Access97)

    If I understand correctly, you are essentially creating a list of doctors for a specific issue, project, event, or whatever, and for any given event, you only want a doctor to show up once. It seems to me you should be able to create a SELECT DISTINCT query based on a UNION query which would give you just the unique DrIDs, and turn that into an Append query to tblapproval. You could do it with a series of update queries as well, but that seems more complex.
    BTW - disregard my rambling on about a form - I somehow concluded you were concerned about the entry of duplicate doctor records - <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    well, seems the append queries didn't work in the end... they append great, unless i have additional records for the same legislator, then they won't append the data (darn those key violations!)... so... onto another approach... i think i will have a go at union queries, seeing that the sql's for the append queries do work. potential snag: i need a field somewhere... a yes/no that after I append, i can update. this field should be used to leave out records that have already been satisfied. thank you all for the ideas given so far... they have been very helpful.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: series of queries (Access97)

    Can you be more specific about needing a Yes/No field that you can update <img src=/S/question.gif border=0 alt=question width=15 height=15> If you do your append using the results of a UNION query, there shouldn't ever be duplicates (I guess I'm assuming that "leave out records that have already been satisfied"="duplicates").
    Wendell

  14. #14
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    What timing you have Wendell! I have constructed the union query and the append query works! ... However I have tried several methods of constructing an update query and to no avail... RTE 3073 "Operation must use an updatable querie" has got me beat. I need a work around.... the goal is to update a yes or no field in tblEvent to "yes" after the records are appended to tblApproval, understanding that there is one record for each event in tblEvent... in tblapproval there can be up to 30 records for each event because several people approve each event. Obviously a one to many relationship. so after the append process, i need to tell Access "hey, look at the eventIDs that were just appended and put a check mark in this table for me, so the next time i run this process, you won't bother appending the same records again!"

    The original SQL statements used in the union queries are based on a filtered version of tblEvent that checks the [AprvlConst] field and only selects those that = no. This is the field that needs to be updated with a check mark after the append querie runs. Any suggestions??? temp table of some sort??? loop through the records??? <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

  15. #15
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: series of queries (Access97)

    ok... i have a solution that works... i think LOL

    two complex select queries based on filtered tables.... unioned to remove duplicates.... two append queries based on this union query. 1- appends EventID and DrID to tblapprovals 2- appends EventID to tempapprovalusing group by statement... update query run based on tempapproval table which updates [AprvlConst] in tblEvent. I think i can string these along in code but what i don't know how to do is... empty the temp table after it updates... or check that it's empty before it accepts new records...

Page 1 of 2 12 LastLast

Posting Permissions

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