series of queries (Access97)

Topic

New Reply

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.

Reply | Quote

Replies

(Edited by charlotte on 22-Jan-03 19:27. to activate link to prior post)

Regarding my old post # 54573. 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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Here is an answer to part of your question.

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

‘Turn the warnings off
DoCmd.SetWarnings False
‘Run Stored queries
DoCmd..OpenQuery “QueryName”
DoCmd..OpenQuery “QueryName2”
‘Run a query built in code
‘strSQL contains the SQL statement
DoCmd.RunSQL strSQL
‘Turn the warnings back on
DoCMD.SetWarnings True

You will need to follow the lead of others for the deleting duplicate records.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. 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.

Reply | Quote

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 –

Reply | Quote

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.

Reply | Quote

Can you be more specific about needing a Yes/No field that you can update 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”).

Reply | Quote

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???

Reply | Quote

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…

Reply | Quote

Are you doing all this in VBA?
If so to delete all records from your “temp” table is straight forward.
DoCmd.RunSql “DELETE * FROM Temp”
is all it takes.
Or have I misunderstood the problem?
Pat

Reply | Quote

Not sure I understand the need for a temporary table, but if that works, good. A delete query as the first step in your process will take care of emptying the table – I usually like to do it that way in case a question arises about how things got the way they are. If you delete the data at the end of the process, you can’t go back to figure out where something went wrong.

As to a query not being updateable, there are some useful tips on how to make sure queries are updateable in the Access help – the most basic one is to make sure that tables all have a primary key.

Reply | Quote

(Corrected SQL- Thanks Wendell)
Eureka! and thanks All! I did reverse the delete command to delete at the beginning rather than end. great idea! I had to append to a temp table – seems to be the easiest way to make the recordset updatable. And wouldn’t you know… it works! this has been a thorn in my side for a very long time… and it used to frustrate me so much that i just couldn’t follow through for the solution… below are the sql’s involved and the rudimentary VBA i know… Thanks to all who helped… and come monday, i’m planning on removing another thorn… should y’all want to help!…

qrytblEvent =
SELECT tblEvent.*
FROM tblEvent
WHERE (((tblEvent.AprvlConst)=No));

qryUnionApprovalsNoDups =
SELECT DISTINCT qrytblEvent.EventID, jctblDrtoMSSNYComm.DrID
FROM (qrytblEvent INNER JOIN (tblCampType INNER JOIN (tblMSSNYComm INNER JOIN (jctblDrtoMSSNYComm INNER JOIN (jctblCampTypetoMSSNYComm INNER JOIN tblCampaign ON jctblCampTypetoMSSNYComm.CampTypeID = tblCampaign.CampTypeID) ON jctblDrtoMSSNYComm.MSSNYCommID = jctblCampTypetoMSSNYComm.MSSNYCommID) ON (tblMSSNYComm.MSSNYCommID = jctblCampTypetoMSSNYComm.MSSNYCommID) AND (tblMSSNYComm.MSSNYCommID = jctblDrtoMSSNYComm.MSSNYCommID)) ON (tblCampType.CampTypeID = jctblCampTypetoMSSNYComm.CampTypeID) AND (tblCampType.CampTypeID = tblCampaign.CampTypeID)) ON qrytblEvent.CampID = tblCampaign.CampID) INNER JOIN tblEventDetails ON qrytblEvent.EventID = tblEventDetails.EventID
WHERE (((tblEventDetails.ProposedAmt)>500) AND ((tblMSSNYComm.MSSNYComm)=”PACExec”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”Chair” Or (jctblDrtoMSSNYComm.DrCommTitle)=”EVP”)) OR (((tblEventDetails.ProposedAmt)>500) AND ((tblMSSNYComm.MSSNYComm)=”FCES” Or (tblMSSNYComm.MSSNYComm)=”SCES”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”Chair”)) OR (((tblEventDetails.ProposedAmt)>500) AND ((tblMSSNYComm.MSSNYComm)=”PACExec”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”StateChair”) AND ((tblCampType.CampType)=”OtherComm”)) OR (((tblEventDetails.ProposedAmt)1000) AND ((tblMSSNYComm.MSSNYComm)=”PACExec”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”Member”)) OR (((tblEventDetails.ProposedAmt)>10000) AND ((tblMSSNYComm.MSSNYComm)=”MSSNYExec”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”Member”))

UNION SELECT DISTINCTROW qrytblEvent.EventID, jctblDrtoMSSNYComm.DrID
FROM (qrytblEvent INNER JOIN (tblMSSNYDistrict INNER JOIN (((jctblLegCamp INNER JOIN (tblCampType INNER JOIN (((tblMSSNYComm INNER JOIN (tblDoctors INNER JOIN jctblDrtoMSSNYComm ON tblDoctors.DrID = jctblDrtoMSSNYComm.DrID) ON tblMSSNYComm.MSSNYCommID = jctblDrtoMSSNYComm.MSSNYCommID) INNER JOIN jctblCampTypetoMSSNYComm ON tblMSSNYComm.MSSNYCommID = jctblCampTypetoMSSNYComm.MSSNYCommID) INNER JOIN tblCampaign ON jctblCampTypetoMSSNYComm.CampTypeID = tblCampaign.CampTypeID) ON (tblCampType.CampTypeID = tblCampaign.CampTypeID) AND (tblCampType.CampTypeID = jctblCampTypetoMSSNYComm.CampTypeID)) ON jctblLegCamp.CampID = tblCampaign.CampID) INNER JOIN jctblLegtoLegDist ON jctblLegCamp.LegID = jctblLegtoLegDist.LegID) INNER JOIN (tblMSSNYDisttoCounties INNER JOIN jctblLegDisttoCounties ON tblMSSNYDisttoCounties.CntyID = jctblLegDisttoCounties.CntyID) ON jctblLegtoLegDist.LegDistID = jctblLegDisttoCounties.LegDistID) ON (tblDoctors.DrMSSNYDist = tblMSSNYDistrict.MSSNYDistrict) AND (tblMSSNYDistrict.MSSNYDistrict = tblMSSNYDisttoCounties.MSSNYDist)) ON qrytblEvent.CampID = tblCampaign.CampID) INNER JOIN tblEventDetails ON qrytblEvent.EventID = tblEventDetails.EventID
WHERE (((tblEventDetails.ProposedAmt)>500) AND ((tblCampType.CampType)=”State” Or (tblCampType.CampType)=”Federal”) AND ((tblMSSNYComm.MSSNYComm)=”SCES” Or (tblMSSNYComm.MSSNYComm)=”FCES”) AND ((jctblDrtoMSSNYComm.DrCommTitle)”Alternate”));

qryAppendApprovals =
INSERT INTO tblApprovals ( EventID, DrID )
SELECT qryUnionApprovalsNoDups.EventID, qryUnionApprovalsNoDups.DrID
FROM qryUnionApprovalsNoDups;

qryAppendEventIDtotempAprvl=
INSERT INTO tempAprvl ( EventID )
SELECT qryUnionApprovalsNoDups.EventID
FROM qryUnionApprovalsNoDups
GROUP BY qryUnionApprovalsNoDups.EventID;

qryupdatetblEvent=
UPDATE tempAprvl INNER JOIN tblEvent ON tempAprvl.EventID = tblEvent.EventID SET tblEvent.AprvlConst = Yes;

Private Sub cmdgetapprovals_Click()
DoCmd.RunSQL “DELETE * FROM tempAprvl”
DoCmd.OpenQuery “qryAppendApprovals”, acViewNormal, acAdd
DoCmd.OpenQuery “qryAppendEventIDtotempAprvl”, acViewNormal, acAdd
DoCmd.OpenQuery “qryupdatetblEvent”, acViewNormal, acAdd
MsgBox “Approvals Are Ready To Send!”

If there was an easier way to do this… wait til tuesday to tell me!

Reply | Quote

Glad it seems to be working – I only see one problem with what you posted, and I suspect something simply got dropped from the post as this SQL syntax shouldn’t ever work

qryUnionApprovalsNoDups =
JOIN tblEventDetails ON qrytblEvent.EventID = tblEventDetails.EventID
WHERE (((tblEventDetails.ProposedAmt)>500) AND ((tblMSSNYComm.MSSNYComm)=”PACExec”) AND ((jctblDrtoMSSNYComm.DrCommTitle)=”Chair” Or (jctblDrtoMSSNYComm.DrCommTitle)=”EVP”)) OR
. . . .

as there isn’t a SELECT clause in that piece of the UNION query. I suspect one line got dropped when you created the post. Have a great weekend.

Reply | Quote