By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Assign a value to a field in the query underlying a report

P: 21
Good Day to All, This is my first post to the scripts, and I'm glad to be part of a community of experts that can pull me out of the holes that I sometimes dig for myself. I am running XP and Access 2002 in 2000 mode.

I have a report that prints a reminder to members of the status of pledges that they have made. I want to be able to turn on a flag for each member in the query underlying the report (or somewhere else if it is advisable) to identify whose name and address will be printed in my print label feature. Of course, if there are only a few reports to print, the user can simply go to the print labels feature and click those few names, but I want to do this automatically for those instances where there are scores or even hundreds of reports to print.

I attempted set the flag in the MemberID Header's On Format event,

Me.IncludeInMM = -1

but I got an error - "could not assign value to this object" and then I went to the query and I see that I cannot update it. Here is the SQL:

SELECT ------------- (All fields included in report) FROM (tblMembers INNER JOIN ((qryPledgesMade1 INNER JOIN tblPledgePayments ON qryPledgesMade1.PledgeID = tblPledgePayments.PledgeID) INNER JOIN qryContributionType ON qryPledgesMade1.PledgeTypeID = qryContributionType.ContributionTypeID) ON tblMembers.MemberID = qryPledgesMade1.MemberID) INNER JOIN qrySumOfContributions ON (tblPledgePayments.PledgeID = qrySumOfContributions.PledgeID) AND (tblMembers.MemberID = qrySumOfContributions.MemberID)
WHERE (((qryPledgesMade1.PledgeAmount)>[SumofContributionAmount]));

Any suggestions will be greatly appreciated, and I am not averse to altering my strategy.

Thanks in advance,

David
Mar 10 '07 #1
Share this Question
Share on Google+
24 Replies


NeoPa
Expert Mod 15k+
P: 31,276
You've lost me a bit here (Your SQL is also very hard to read as it is neither in [code] tags nor laid out so that it can be put in them without making it mostly scroll off the side of the page).
What exactly are you trying to do? Update the flags in the record or simply detect which records should be printed?
Do I need to understand the long and complicated structure of your tables? Or is that simply all a red herring?
Mar 11 '07 #2

P: 21
Thanks for coming to my rescue. I am sorry for not tagging the code; an oversight due to my first post. I hope to get it right this time. I have changed the original query, but it still does not work. Here is the SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMembers.MemberID, tblMembers.IncludeInMM, tblPledges.PledgeAmount, Sum(tblPledgePayments.ContributionAmount) AS SumOfContributionAmount
  2. FROM (tblMembers LEFT JOIN tblPledges ON tblMembers.MemberID = tblPledges.MemberID) INNER JOIN tblPledgePayments ON (tblMembers.MemberID = tblPledgePayments.MemberID) AND (tblPledges.PledgeID = tblPledgePayments.PledgeID)
  3. GROUP BY tblMembers.MemberID, tblMembers.IncludeInMM, tblPledges.PledgeAmount;
This query has the 3 relevant tables that are needed to create the mailing labels. however, this query will not run in datasheet view. If I click Run (!), nothing happens.

In plain English (I hope), this is what I want to do. When the user clicks the appropriate command button that creates a pledge satus report for members, I want to turn on IncludeInMM (yes/no field) for each member who gets a report. Turning on this switch will automate the process to print mailing labels to those who receive a report. I do not want every member of the organization to have a mailing label printed for them; only those members who have a report. Basically the underlying query that prints the report looks at all contributions made on behalf of a pledge, and if the pledge is greater than the contributions made on its behalf, then a status report is created. This may not be relevant, but FYI, a member could have a report that shows more than one outstanding pledge.

I appreciate your assistance,

David
Mar 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,276
Let's start by getting the SQL into a legible format (As previously requested).
Expand|Select|Wrap|Line Numbers
  1. SELECT M.MemberID,
  2.        M.IncludeInMM,
  3.        P.PledgeAmount,
  4.        Sum(PP.ContributionAmount) AS SumOfContributionAmount
  5. FROM (tblMembers AS M LEFT JOIN tblPledges AS P 
  6.   ON M.MemberID=P.MemberID) INNER JOIN tblPledgePayments AS PP
  7.   ON (M.MemberID=PP.MemberID) AND (P.PledgeID=PP.PledgeID)
  8. GROUP BY M.MemberID,
  9.          M.IncludeInMM,
  10.          P.PledgeAmount;
Let's also be clear from the start.
If I ask three questions and you respond to only one of them, I'm not going to keep chasing you for the information. If you don't provide the information then I shall simply wait patiently for the answer or death from old-age, whichever comes the sooner.
What exactly are you trying to do? Update the flags in the record or simply detect which records should be printed?
Clearly an attempt has been made to answer the first part of this question. Don't worry that it's still a bit confusing and not very exact. We can move on to that I hope. The second part, however, has been ignored as far as I can see. This wasn't a random question. I need this information to have any sort of idea of what you want.
Do I need to understand the long and complicated structure of your tables? Or is that simply all a red herring?
Another question overlooked.
Sorry if this all sounds overly critical, but I can't think of any other way of turning a question like this into one that can find an answer. Remember, we can't see what you're working with and rely on what you tell us. If that's not clearly explained (This is not) then we either respond requesting further information or simply ignore the thread (By far the simpler option).
Mar 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,276
Just as a tip for explaining things in 'Plain English' :
Never refer to anything that is neither generally understood, nor explained (or defined) previously in your explanation.
It's very difficult to make any sort of sense of something when words creep in that mean nothing to you. The rest of the explanation may make perfect sense, but what would a reader make of it without knowing what the basics are that you're building your explanation on.
Please don't take this as criticism (That was all in the previous post :D), just a tip for the future. I appreciate that you made an attempt to explain more clearly and, believe me, there are very few who actually exhibit the skills to communicate well in the written word.
Mar 12 '07 #5

P: 21
Pardon my lack of posting protocol; however, I do not see a way to view previous posts while making a response; thereby causing me to overlook certain necessary aspects of previous posts. I hope I explained what I am trying to do in my previous post; and no I do not think you need to understand the complex tables, and no they were not just red herrings.

Thanks again for coming to my rescue,

David
Mar 12 '07 #6

NeoPa
Expert Mod 15k+
P: 31,276
Not being able to see previous posts is a recognised problem with the current layout and we're expecting a fix to that sometime shortly (No idea how shortly :(), so we use the 'Open link in new window' or 'Open link in new Tab' options.
The most important question is still the one about whether the data needs to be updated or whether simply including a field that reflects the criteria in a query is adequate. The latter is the recommended approach but I know far too little about your database to make that decision for you.
When we know that we have to look at a way of providing your needs for you. The approaches will be entirely different depending on the answer to this question. If you're not sure and need to discuss the issues that might go to making up the answer, just let us know and we can go there first.
Mar 12 '07 #7

P: 21
Not being able to see previous posts is a recognised problem with the current layout and we're expecting a fix to that sometime shortly (No idea how shortly :(), so we use the 'Open link in new window' or 'Open link in new Tab' options.
The most important question is still the one about whether the data needs to be updated or whether simply including a field that reflects the criteria in a query is adequate. The latter is the recommended approach but I know far too little about your database to make that decision for you.
When we know that we have to look at a way of providing your needs for you. The approaches will be entirely different depending on the answer to this question. If you're not sure and need to discuss the issues that might go to making up the answer, just let us know and we can go there first.


I currently have switch in my members' contact data table - tblMembers - named IncludeInMM. For various programming reasons in addition to this need, this field is a switch that determines which members are included in a Word mail merge or are included in a mailing label printout. For whatever the need to turn this switch on, once the feature is finished, and the form/report/query are closed, this switch is turned off. So I always know that the switch is False if I need to open tblMembers. With that little background, it appears to me that IncludeInMM needs to be updated to True and then only those members whose switch is turned on, or true, will be printed in the mailing label feature. However, if this is not advisable, please suggest a workable solution.

Thanks,

David
Mar 13 '07 #8

NeoPa
Expert Mod 15k+
P: 31,276
Let's go with the update method for now. There are a whole number of possible reasons why other methods may not work (although generally advisable - they may not have been designed for).
In your explanation you don't explain what is used to determine which members 'get a report'. An alternative worth considering (but not if it gets too complicated) would be to run the mailing report immediately after the 'pledge?' report and using the same criteria (obviously avoiding duplication of members with multiple pledge reports printed).
Otherwise, we need to run an update query that essentially does the same selection anyway. One benefit of this latter approach would be that it would be able to 'remember' multiple runs and be able to produce a mailing report for all members who had had pledge reports run for them since the last mailing report was run. Assuming, of course, that the reset is only done after a mailing report is finished.
Mar 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,276
It may be a good idea at this point, to get some table MetaData from you.
I include an example for you to copy so that the formatting is clearest and easy to read and work with :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
If you could provide something in this format for the three tables we need to use (I think) [tblMembers], [tblPledges] & [tblPledgePayments].
Mar 13 '07 #10

P: 21
It may be a good idea at this point, to get some table MetaData from you.
I include an example for you to copy so that the formatting is clearest and easy to read and work with :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
If you could provide something in this format for the three tables we need to use (I think) [tblMembers], [tblPledges] & [tblPledgePayments].
Thanks for the interest that you are taking in assisting me solve my problem. Here are the layouts of the tables that you requested; however, I did not know what "PK" and "FK" meant in your example.

Table Name = tblMembers
Expand|Select|Wrap|Line Numbers
  1. Field; Type; Index Info
  2. MemberID; Autonumber; Yes(No Dups)
  3. Member; Yes/No; No
  4. Title; String; No
  5. FirstName; String; No
  6. MiddleName; String; No
  7. LastName; String; No
  8. StreetAddress; String; No
  9. ApartmentNum; String; No
  10. City; String; No
  11. State; String; No
  12. Zip; String; No
  13. HomePhone; String; No
  14. IncludeInMM; Yes/No; Yes(Dups OK)
Table Name = tblPledges
Expand|Select|Wrap|Line Numbers
  1. Field; Type; Index Info
  2. PledgeID; Autonumber; Yes(No Dups)
  3. MemberID; Number; Yes(Dups OK)
  4. PledgeAmount; Currency; No
  5. PledgeDate; Date/Time; No
  6. PledgeTypeID; Number; No
  7. PayInterval; String; No
  8. PledgeDueDate; Date/Time; No

Table Name =tblPledgePayments

Expand|Select|Wrap|Line Numbers
  1. Field; Type; Index Info
  2. PledgePaymentID; Autonumber; Yes (No Dups)
  3. PledgeID; Number; Yes (Dups OK)
  4. ContributionAmount; Currency; No
  5. ContributionDate; Date/Time; No
  6. ContributionMethod; Striing; No
A member receives a Pledge Report if they have an unsatisfied pledge; which is (Sum(tblPledgePayments.ContributionAmount)) < tblPledge.PledgeAmount. This logic also triggers a requirement to turn on switch for mailing label.

David
Mar 14 '07 #11

P: 2
hi very thanks
Mar 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,276
hi very thanks
Don't worry about this (quoted) post.
I think it's a new member who's not sure how to use the forums yet.

PK = Primary Key.
FK = Foreign Key. A copy of another table's PK that enables related records from two separate tables to be linked.
Congratulations on posting the MetaData so well and exactly (apart from the PK/FK bit) as intended.
Your explanation of the way to determine the records required is also very helpful. I'm looking at this in detail shortly (or after work if I run out of time now) and I think I have all I need to go forward. If I find otherwise, I'll post back, but all seems great atm :)
Mar 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,276
Sorry David, it's nearly 02:00 atm & I can't think clearly enough to answer this tonight. Sorry for not getting to it earlier but I'll give it another go tomorrow.
Mar 15 '07 #14

P: 21
Sorry David, it's nearly 02:00 atm & I can't think clearly enough to answer this tonight. Sorry for not getting to it earlier but I'll give it another go tomorrow.
No problem. I just appreciate the time and care that you are taking to get me to home plate. We'll type later.

David
Mar 15 '07 #15

NeoPa
Expert Mod 15k+
P: 31,276
I'm actually looking at it now :)
I'm going to suggest an UPDATE query (Details to follow.) which should run before you open the report. This way you can use the newly updated [IncludeInMM] flag (If and ONLY if it is appropriate for YOUR situation.) to control what is printed in the report too. You won't NEED to change anything, but it may give you the option.

-Adrian.
Mar 15 '07 #16

NeoPa
Expert Mod 15k+
P: 31,276
This really isn't as simple as it looks is it?
I've had to create a test rig to try to get it to work without complaining about needing an updatable query in the UPDATE.
In case you're interested, this is what I have so far (That I know doesn't work).
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblMembers AS M INNER JOIN (SELECT P.MemberID
  2. FROM tblPledges AS P INNER JOIN tblPledgePayments AS PP
  3.   ON P.PledgeID=PP.PledgeID
  4. GROUP BY P.MemberID,P.PledgeID,P.PledgeAmount
  5. HAVING P.PledgeAmount>Sum(PP.ContributionAmount)) AS subQ
  6.   ON M.MemberID = subQ.MemberID
  7. SET M.IncludeInMM = True
Mar 15 '07 #17

NeoPa
Expert Mod 15k+
P: 31,276
As the criteria must, of necessity, total up the amounts paid, and a GROUP BY clause is never compatible with an UPDATE query, we will need to add an extra field into the [tblPledges] table called [TempPaid]. This will be a Currency field just like the [PledgeAmount] field, but it will typically be undefined. It will only ever have reliable information immediately after running through this process so should generally be treated with high suspicion.
This is more of a complicated solution so please give me a little more time to complete the details, but it would be a good start to get the field added now anyway.
Mar 15 '07 #18

NeoPa
Expert Mod 15k+
P: 31,276
An actual answer at last!

If you run the (Pledges) report from a CommandButton on your form, then before the DoCmd.OpenReport() you would have some code that updates the records like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. Call DoCmd.SetWarnings(False)
  4. 'Reset [TempPaid] field
  5. strSQL = "UPDATE tblPledges SET TempPaid=0"
  6. Call DoCmd.RunSQL(strSQL)
  7. strSQL = "UPDATE tblPledges AS P " & _
  8.          "INNER JOIN tblPledgePayments AS PP " & _
  9.          "ON P.PledgeID=PP.PledgeID " & _
  10.          "SET P.TempPaid=P.TempPaid+PP.ContributionAmount"
  11. Call DoCmd.RunSQL(strSQL)
  12. 'Using [TempPaid], set [IncludeInMM] flag
  13. 'This code is commented out as you said it was already effected
  14. 'but you can uncomment it if it's required
  15. 'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
  16. 'Call DoCmd.RunSQL(strSQL)
  17. strSQL = "UPDATE tblMembers AS M " & _
  18.          "INNER JOIN tblPledges AS P " & _
  19.          "ON M.MemberID=P.MemberID " & _
  20.          "SET M.IncludeInMM=True " & _
  21.          "WHERE P.PledgeAmount>P.TempPaid"
  22. Call DoCmd.RunSQL(strSQL)
  23. Call DoCmd.SetWarnings(False)
  24. 'Your code to run the report would follow on here...
In the end, this would probably have been easier doing the 'correct' way, as a SELECT query would not have fallen foul of the same restrictions (Needing to be an updatable query) that the UPDATE query does.
Mar 15 '07 #19

P: 21
An actual answer at last!

If you run the (Pledges) report from a CommandButton on your form, then before the DoCmd.OpenReport() you would have some code that updates the records like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. Call DoCmd.SetWarnings(False)
  4. 'Reset [TempPaid] field
  5. strSQL = "UPDATE tblPledges SET TempPaid=0"
  6. Call DoCmd.RunSQL(strSQL)
  7. strSQL = "UPDATE tblPledges AS P " & _
  8.          "INNER JOIN tblPledgePayments AS PP " & _
  9.          "ON P.PledgeID=PP.PledgeID " & _
  10.          "SET P.TempPaid=P.TempPaid+PP.ContributionAmount"
  11. Call DoCmd.RunSQL(strSQL)
  12. 'Using [TempPaid], set [IncludeInMM] flag
  13. 'This code is commented out as you said it was already effected
  14. 'but you can uncomment it if it's required
  15. 'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
  16. 'Call DoCmd.RunSQL(strSQL)
  17. strSQL = "UPDATE tblMembers AS M " & _
  18.          "INNER JOIN tblPledges AS P " & _
  19.          "ON M.MemberID=P.MemberID " & _
  20.          "SET M.IncludeInMM=True " & _
  21.          "WHERE P.PledgeAmount>P.TempPaid"
  22. Call DoCmd.RunSQL(strSQL)
  23. Call DoCmd.SetWarnings(False)
  24. 'Your code to run the report would follow on here...
In the end, this would probably have been easier doing the 'correct' way, as a SELECT query would not have fallen foul of the same restrictions (Needing to be an updatable query) that the UPDATE query does.
I had a longer than expected day yesterday,and when I went to log on the site last night, I saw that it was down,and then when it rains it pours; early this morning my browser, Firefox, kept freezing on me (which has happened with more frequency after installing some MS updates and programs). I have one project to move this morning and then I will come back and incorporate your answer into my application and let you know how it works.

Thanks for all of your assistance,

David
Mar 16 '07 #20

NeoPa
Expert Mod 15k+
P: 31,276
Not a problem David.
I too had problems accessing the site yesterday and also at lunchtime today. Changes are afoot. They should ultimately be for the best though, incorporating searching through specific forums for threads etc.
Mar 16 '07 #21

P: 21
Not a problem David.
I too had problems accessing the site yesterday and also at lunchtime today. Changes are afoot. They should ultimately be for the best though, incorporating searching through specific forums for threads etc.
Hi Adrian, Thanks for the time you have put into my problem. I haven't been able to squeeze enough time in to apply it to my application, and then Mrs. David took off from work today, and dared me to ignore her while working on my computer, so today was shot. I hope to do it tomorrow. I'll keep you posted.

Thanks again and have a pleasant day.

David
Mar 19 '07 #22

NeoPa
Expert Mod 15k+
P: 31,276
Hi Adrian, Thanks for the time you have put into my problem. I haven't been able to squeeze enough time in to apply it to my application, and then Mrs. David took off from work today, and dared me to ignore her while working on my computer, so today was shot. I hope to do it tomorrow. I'll keep you posted.

Thanks again and have a pleasant day.

David
So, did you dare to ignore her. I want all the gory details - How much blood? Any usable limbs left? How DOES one eat without teeth?

It would have been very infra-gallant to ignore your good lady when you have the opportunity to spend quality time together, so I'm sure you didn't. Please pass her my best wishes and get back to the project when you're good and ready.

-Adrian.
Mar 19 '07 #23

P: 21
So, did you dare to ignore her. I want all the gory details - How much blood? Any usable limbs left? How DOES one eat without teeth?

It would have been very infra-gallant to ignore your good lady when you have the opportunity to spend quality time together, so I'm sure you didn't. Please pass her my best wishes and get back to the project when you're good and ready.

-Adrian.

Woooo-ooo-Weee and a lot of laughing!! I would have never figured that one out. The difference between your magic and Copperfield's is that yours is real. That was spectacular. FYI, I didn't need to comment out

Expand|Select|Wrap|Line Numbers
  1. 'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
  2. 'Call DoCmd.RunSQL(strSQL)
because as you were already aware, I had taken care of that part of the feature. Also, I assumed that

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
at the end of your code should be set to True. Additionally, I added a line of code in the Report's OnClose event that opened my Print-Label Form and all of the appropriate names showed up - Unbelievable!! Consequently, thanks to you, all the user needs to do after printing the reports is place the labels in the printer and press the print button on the Print-Label Form.

Adrian, I am always thankful when someone comes to my rescue,and working with you over the last week taught me two things: First, how to set the IncludeInMM field to True for the Pledge Report; and two, how to be more specific in my explanations for assistance. Thanks for both lessons.

Have a blessed day as you have blessed mine.

David

p.s. I have been married too long and have tried too often to sneak on the computer when the Mrs is home to try that "prank" now. I am too old to run for my life :o).
Mar 20 '07 #24

NeoPa
Expert Mod 15k+
P: 31,276
You're absolutely right about the True on the return leg. Sloppy post, but you noticed it so that's cool.
I'm very pleased to hear it worked for you - that's what makes it all worthwhile, when you can help someone get that pleased :)
Mar 20 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.