Replies

(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!

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…

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

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.

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.

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

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.

we use microsoft mail in our office. we are like a satellite office and are connected via a WAN to another office that used exchange server. Whether that matters, I’m uncertain. However, whenever there is an error in delivery of an email or a fax (microsoft fax) I get an automatic msg From: System Administrator, Subject: Undeliverable:(name of original subject), the body of the msg says: The following recipients could not be reached and it normally gives a list of the email addresses or fax numbers or the alias’ they use and a reason. There is a button to Resend this msg and when I click it the, a new window pane opens with the names in the Resend To: field and there is the text of the original msg. When i have sent the original msg using the BCC field. the resulting error msg from the System Administrator is blank. For some reason it is programmed to report only the recipients in the TO: field and doesn’t state the problem with delivery. So when i therefore hit resend msg… there are no names posted in the Resend BCC: field and i have know way of knowing who didn’t get the msg or the reason why. Quite Annoying! So I’m wondering if either the system administrator msg could be changed to read BCC field or if there is somewhere else to “look” and find the errors… Thank you both for the feedback.

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

Thanks alot jscher! come to find out someone in our company has outlook98 so i think we will upgrade but the book is a great idea too… in general we have not taken advantage of all outlook has to offer so I think now is the time for some research. Happy holidays!

Right again you two. Thanks…

Private Sub cboLName_AfterUpdate()
cboCampName.RowSource = “SELECT tblCampaign.CampName FROM tblLegislators” & _
” INNER JOIN (jctblLegCamp INNER JOIN tblCampaign ON jctblLegCamp.CampID =” & _
” tblCampaign.CampID) ON tblLegislators.LegID = jctblLegCamp.LegID” & _
” WHERE jctblLegCamp.LegID=” & [cboLName] & ” ORDER BY tblCampaign.CampName”
End Sub

This is the correct one…. SQL view is very helpful for people like myself who aren’t well versed in writing SQL statements.

Darn all those joins! Thanks again.

Campaign Type works great! However, when I tried to adapt the code to work on the LName listbox, I am clearly generating an error, but don’t know why. Can anyone look at the sql statement below and see the error?

Private Sub cboLName_AfterUpdate()
cboCampName.RowSource = “SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] (INNER JOIN jctblLegCamp ON tblLegislators.LegID = jctblLegCamp.LegID) INNER JOIN tblCampaign ON jctblLegCamp.CampID = tblCampaign.CampID” & _
“WHERE jctblLegCamp.CampID = ” & cboCampName & ” ORDER BY [tblCampaign].[CampName]”

End Sub

Note*** there can be more than one campaign name per selected last name….

Thanks a bunch

Ahhh! it was the missing space after the open quote….

I’ve gotten both boxes to requery properly after a change to the CampType box. Thanks for your help Hans… I will work on the other ones. I’ll post back with any questions as they arise. But for today, my work is done… til after Turkey Day…

I’ve gotten this statement to work so far. how do you add the ORDER BY part? I’ve tried and access thinks it’s part of the parameter of cboCampType…

cboCampName.RowSource = “SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] WHERE [tblCampaign].[CampTypeID] = ” & cboCampType

LOL, they started out as combo boxes Hans!

OK, in response to both of you: thanks first of all.

yes, cboCampType is a field in tblCampaign. jctblLegCamp consists of an automunber, LegID and CampID (not CampName).

If a user selects an item from CampType, then LName needs to be filtered along with the Camp Name box to show campaigns for that Legislator. There are some instances where a user might want to select the campaign name directly, and in that case it isn’t necessary for the other two to be requeried. Whichever way it is accomplished, I would like to end up in a position to use the campID the user selected in the next form that will open.

I do need to be able to clear the form too so the defaults return… each box now has a list of all their options, no filters applied. I do not want to add an all option to each of the boxes, rather a button that will remove any filters applied or reset the row sources to the original condition as stated in the earlier post.

Anyhow, If you know of how to set the row sources dynamically or where to find info about that specifically, I’d like the direction.

(As an aside, what i’m trying to accomplish is akin to selecting a customer and then placing an order for that customer. In my case a “customer” is a “campaign name” but users rarely know the proper name for a campaign, they may know the name of the legislator or the office for which they are running. In this version of the application, I want to give them several options for choosing a campaign and it is a better means for helping them choose the correct campaign.)

Again thanks for your posts! and any information you may still have to offer.