 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005Summarizing records in one table that match a different table
I have a Clients Table and an Activities Table. There is one entry for each client with identification information. The Activities Table contains an entry for each date that something happens to a Client. But not all Client records have an activity and this is what causes my question, because I don't know how to have Access just give me a summary (Count(*)) for the "active" clients only. The two tables are linked by a Client ID. I've tried using a Query that ties both tables together as the report source, but it counts the Client record regardless of whether it has a matching Activity record or not. So that is my story, and I'd appreciate any clues you could give me so that it would add only the "matching" Client records. Thanks, Tom create a new query with the Activities table, showing all the fields you need to see about activities. in the query design view, add the Clients table. link the two tables with a LEFT JOIN from the Activites table to the Clients table, as FROM Activities LEFT JOIN Clients ON Activities.ClientID = Clients.ClientID this will ensure that you get all Activities records, but only those Client records where there is a matching Activities record. hth > I have a Clients Table and an Activities Table. There is one entry for each > client with identification information. The Activities Table contains an [quoted text clipped - 7 lines] > it would add only the "matching" Client records. > Thanks, Tom OK Tina. I am afraid that my knowledge of doing what you suggested is lacking. My problem - not yours. I created a new query with the Activities table. Then in the query design view I added the Clients table and it automatically linked using the Client ID code. Now I'm looking at the screen and do not know how to enter the "From Activities Left Join etc."?? So if you will take me by the hand and lead me through this, as you have done in the past, I would greatly appreciate it. Thanks, Tom > create a new query with the Activities table, showing all the fields you > need to see about activities. in the query design view, add the Clients [quoted text clipped - 26 lines] > > it would add only the "matching" Client records. > > Thanks, Tom copy/paste the query's SQL statement into a post, and i'll show you where to make the change. (you can do it from the design grid as well, and it's actually pretty easy - but i cringe it the thought of trying to explain it in a post. if i could sit next to you and point... <g>) to copy the SQL statement, open your query in design view, and on the menu bar click View | SQL View. in the SQL pane, highlight and copy the entire SQL statement. hth > OK Tina. I am afraid that my knowledge of doing what you suggested is > lacking. My problem - not yours. I created a new query with the Activities [quoted text clipped - 35 lines] > > > it would add only the "matching" Client records. > > > Thanks, Tom AHA! I believe you just turned the light on for me. I couldn't for the life of me figure out where or how this SQL statement could get entered. So it's View - SQL that gets me there. I now believe I can do what you asked, but I have enough sense never to feel confident with this, so let us proceed. After doing a new query and adding the client table, I have copied the SQL as you suggested: SELECT tblClientActivities.ActivityId, tblClientActivities.ClientIdfk, tblClientActivities.Date, tblClientActivities.Time, tblClientActivities.NumberOfBags, tblClientActivities.GasVoucherAmt, tblClientActivities.GiftCardAmt, tblClientActivities.NumberOfClothesVouchers, tblClientActivities.NumberOfTokens, tblClientActivities.MEMO, tblClientActivities.NumberFed, tblClientActivities.DateUpdated, tblClients.ClientId, tblClients.LastName, tblClients.FirstName, tblClients.Address, tblClients.City, tblClients.State, tblClients.Zip, tblClients.HomePhone, tblClients.NumberInFamily FROM tblClients INNER JOIN tblClientActivities ON tblClients.ClientId = tblClientActivities.ClientIdfk; Thanks,Tom > copy/paste the query's SQL statement into a post, and i'll show you where to > make the change. (you can do it from the design grid as well, and it's [quoted text clipped - 59 lines] > > > > it would add only the "matching" Client records. > > > > Thanks, Tom well, lets just switch the FROM clause around a bit, as FROM tblClientActivities LEFT JOIN tblClients ON tblClientActivities.ClientIdfk = tblClients.ClientId; the rest of the SQL statement looks fine, so just change the above. suggest you look at the "join line" in the query design view *before* you change the SQL statement, and again *after* you change the SQL - you'll see how the change is displayed in the design view. and btw, i noted two fields in your tblClientActivities, named respectively Date and Time. suggest you change those field names because Date and Time are reserved words in Access; you'll run into problems with the system getting them confused sooner or later, if you haven't already. see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords for more information. hth > AHA! I believe you just turned the light on for me. I couldn't for the life > of me figure out where or how this SQL statement could get entered. So it's [quoted text clipped - 80 lines] > > > > > it would add only the "matching" Client records. > > > > > Thanks, Tom OK. I changed it and here is what I'm experiencing now - because I know I didn't explain myself well to begin with. I am getting a count every time a client record matches an activity record. So if I have 2 activity records matching 1 client record, I get a count of 2, and I just want a count of 1. If I have no activity records for a client record, then it correctly doesn't count that condition. But in effect, I am getting a count of all my activity records????. How do I fix this one, e.g. if I have 50 client records matching 200 activity records, I just want a count of 50. Sorry, I am so bad at explaining what I'm trying to do, Tom > well, lets just switch the FROM clause around a bit, as > [quoted text clipped - 120 lines] > > > > > > it would add only the "matching" Client records. > > > > > > Thanks, Tom okay, i admit i didn't get that picture from your first post. but now that we're on the same page, let me ask you: where are you doing the counting? in the query, as a Totals query? or in the report, in a group header or footer? i'll try to shorten some of the dialog here, by telling you that if you need to show all those fields from tblClientActivities, as detail of each activity, in the report - then you'll have to do the counting in a report section header or footer (or else write a more complex query, or query based on a query - and i'm not the person to take you there!). if you just need the query to return one record - a count of the number of clients with records in tblClientActivities - then we can change the query to do that. so which is it? hth > OK. I changed it and here is what I'm experiencing now - because I know I > didn't explain myself well to begin with. I am getting a count every time a [quoted text clipped - 21 lines] > > are reserved words in Access; you'll run into problems with the system > > getting them confused sooner or later, if you haven't already. see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords > > for more information. > > [quoted text clipped - 105 lines] > > > > > > > it would add only the "matching" Client records. > > > > > > > Thanks, Tom I really don't need all those elements for a single report. I was just setting it up to use the query for various summary reports that I have to produce. For example, I need to do a summary report for the element "Language at Home". So I set up a Count(*) in the group header for "Language at Home" and an overall total Count (*) in the Report Footer - with no Detail lines. So the answer to your question is that I will only be doing this counting in a Report which uses the query as the record source. In this example I mentioned, I just need that one element from the Client Table. The report would have one line for each Language with a summary count of the number of Clients who have at least one activity record. I hope I have answsered all your questions and thank you very much for pursuing this with me, Tom > okay, i admit i didn't get that picture from your first post. but now that > we're on the same page, let me ask you: where are you doing the counting? [quoted text clipped - 176 lines] > > > > > > > > it would add only the "matching" Client records. > > > > > > > > Thanks, Tom okay, i think i get the picture. i also think i'm having a stupid attack, because i had to use VBA to get the count of clients for each language in the report (using your example). there's probably an easier way to do this, but here's what i came up with: using "Language" as the example (i had to add a field to my Clients test table for language), i created a report based on the query with the joins we've already discussed. in the report's Sorting and Grouping dialog, i added the Language field and set the GroupFooter property to Yes. next, i added the ClientID field and set the GroupHeader property to Yes. in the report design view: i removed any fields from the Detail section and dragged the bottom edge up so the section was "closed". i removed any fields from the ClientID Header section, and dragged its' bottom edge up so it was as close to closed as i could get it - without actually closing it. in the Language Footer section, i added an unbound textbox and named it txtClientCount. back in the ClientHeader section, i added the following code to the OnPrint event procedure, as intCount = Nz(intCount, 0) + 1 in the Language Footer section, i added the following code to the OnFormat event procedure, as Me!txtClientCount = intCount intCount = 0 in the VBA code window, *above* the top procedure, i added the following line of code, as Dim intCount As Integer if you don't know how to create an event procedure from report design view, see the instructions to "Create a VBA event procedure" at http://home.att.net/~california.db/instructions.html. if you were using a separate query for each report, you could do the counting in a Totals query, and base the report on that. but since you're using the same query for a number of different reports, this is the only way i could figure out to do it. hth > I really don't need all those elements for a single report. I was just > setting it up to use the query for various summary reports that I have to [quoted text clipped - 59 lines] > > > > are reserved words in Access; you'll run into problems with the system > > > > getting them confused sooner or later, if you haven't already. see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords > > > > for more information. > > > > [quoted text clipped - 125 lines] > > > > > > > > > it would add only the "matching" Client records. > > > > > > > > > Thanks, Tom Thank you very much Tina. I shall go through all your recommendations. Based on your previous help, I know that they will work. Thank you for all the time you put into this one. Happy New Year! Tom > okay, i think i get the picture. i also think i'm having a stupid attack, > because i had to use VBA to get the count of clients for each language in [quoted text clipped - 273 lines] > > > > > record > > > > > > > or I'm back again. I couldn't get it to work. I can do everything you suggested but I probably put things in the wrong place. First question is with my instruction to add an unbound textbox and name it "txtClientCount". I used the Text Box tool and inserted "txtClientCount" in the text box and deleted the label. That probably isn't correct because I got an error on "txtClientCount" as being undefined? Second question you instructed that in the VBA code window *above* the top procedure, add the code "Dim intCopunt As Integer". I'm not sure where you intended me to put that line of code. I tried different places but obviously, I didn't do it correctly. Helpless and Hopeless, Tom > okay, i think i get the picture. i also think i'm having a stupid attack, > because i had to use VBA to get the count of clients for each language in [quoted text clipped - 273 lines] > > > > > record > > > > > > > or well, i was afraid of that - sometimes *i'm* hopeless a describing things in text so they make sense. i built a small db in A2000 with just the two tables, a query, and a report, to work out the solution before posting it. i can load it to my website, Tom, if you'd like to download it to *see* how i set the report up. let me know if you want to take a look. hth > I'm back again. I couldn't get it to work. I can do everything you > suggested but I probably put things in the wrong place. First question is [quoted text clipped - 133 lines] > > system > > > > > > getting them confused sooner or later, if you haven't already. see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords > > > > > > for more information. > > > > > > [quoted text clipped - 109 lines] > > > > > > > > > > > > > > > > > > > > "TomC" <TomC@discussions.microsoft.com> wrote in message news:AEA3CF2D-36C3-4F77-9275-54CD66E182E0@microsoft.com... > > > > > > > > > > > I have a Clients Table and an Activities Table. There is > > one [quoted text clipped - 33 lines] > > > > > > record > > > > > > > > or Yes, by all means, let me have your website address so that I can download it. I sure would like to know how to do this. I found it easy to detail list just those client records which have an activity record, but it is frustrating that I can't just use those records for summary purposes. If I could create a file of matching client records, it would make it so much easier for me to use that file for summarizations that I need to do. Oh well, such is life. I do appreciate you coming back, but I wasn't surprised, beacuse of my past experience with your helping me. Thanks again, Tomc > well, i was afraid of that - sometimes *i'm* hopeless a describing things in > text so they make sense. i built a small db in A2000 with just the two [quoted text clipped - 272 lines] > > > > > > > > > > and do not know how to enter the "From Activities Left > Join okay. go to http://home.att.net/~california.db/instructions.html, scroll to the bottom of the page, right click on the "demo" link, and choose Save Target As... from the shortcut menu. (at least that's how i downloaded it in Internet Explorer, you may do it a little differently in another browser.) the file name is demo.bak, so make sure you change the extension to .mdb *before* you open the file in Access. if you have any questions about the demo db, you're welcome to email me. from the webpage, click the Tips button at the top of the page. from the list of topics at the left, click on the topic "Posting email addresses in Access newsgroups" (Tip #11). use the Example email address, following the instructions AND changing the number 1 to a number 2. hth > Yes, by all means, let me have your website address so that I can download > it. I sure would like to know how to do this. I found it easy to detail list [quoted text clipped - 188 lines] > > > > > > > > getting them confused sooner or later, if you haven't already. > > see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords > > > > > > > > for more information. > > > > > > > > [quoted text clipped - 72 lines] > > > > > > > > > > > > > > > > > > > > "TomC" <TomC@discussions.microsoft.com> wrote in message news:96360D1A-CDBD-43E9-BE05-31B0089C9887@microsoft.com... > > > > > > > > > > > OK Tina. I am afraid that my knowledge of doing what you > > > > > > suggested is [quoted text clipped - 13 lines] > > > > > > > > > > > and do not know how to enter the "From Activities Left > > Join Thanks Tina. I'll give this a go tomorrow. Tomc > okay. go to http://home.att.net/~california.db/instructions.html, scroll to > the bottom of the page, right click on the "demo" link, and choose Save [quoted text clipped - 271 lines] > what > > > you I couldn't wait till tomorrow, so I got it, tried it and it looks really good. Thanks for all your help. I'll leave you alone now and I wish you a very Happy New Year. I do have the email translated and I may try that another time. Tomc > okay. go to http://home.att.net/~california.db/instructions.html, scroll to > the bottom of the page, right click on the "demo" link, and choose Save [quoted text clipped - 271 lines] > what > > > you you're welcome, and Happy New Year to you as well. :) btw, while you can email me if you have questions about the demo, you'll want to come back to the newsgroups to post any other questions you have on this topic, or any other Access issue. > I couldn't wait till tomorrow, so I got it, tried it and it looks really > good. Thanks for all your help. I'll leave you alone now and I wish you a [quoted text clipped - 259 lines] > > already. > > > > see http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords > > > > > > > > > > for more information. > > > > > > > > > > > > > > > > > > > > hth > > > > > > > > > > > > > > > > > > > > "TomC" <TomC@discussions.microsoft.com> wrote in message news:01A3D5B5-9917-49C6-8240-BC7018B60ABD@microsoft.com... > > > > > > > > > > > AHA! I believe you just turned the light on for me. I > > > > couldn't [quoted text clipped - 7 lines] > > what > > > > you Tina, I Tried to send you an email as you instructed but is came back undelivered?? Anyway, I'll just say here what I was going to tell you in the email: I thank you very much for the Demo data base. You took me to places I didn’t know existed. I really needed that Demo to do what you had told me to do before. After doing the Query and Report as you instructed, I got the correct totals, but the only thing was I was getting multiple line spaces between some of the Language Totals. Most of the total lines were single spaced, but on a couple of occasions, it spaced at least 5 lines before the next total printed. So rather than bother you again, I come up with a solution that I wanted to share with you. I knew I could list a single client record in a report by tying the Client Table to the Activities Table with a Query and grouping by ClientID. So, I did such a report which included those elements that I eventually wanted to summarize in reports. All this report did was set me up to create a file I could export to Excel and then import back into Access. I exported the Previewed report as an .xls File. In my data base, I went to tables and then did a File, Get External Data and then input the xls file into my Access data base. I now had a table of single Client records which I can use to run my reports. So there it is for what it’s worth. I’m sure you will never get this question again, and I know your effort for me wasn’t lost, because I ran your solution to the problem, and it did verify that I was getting the correct totals with my solution. So keep on helping, because you do such a terrific job and are knowledgeable beyond belief. Thanks again, Tom > you're welcome, and Happy New Year to you as well. :) > btw, while you can email me if you have questions about the demo, you'll [quoted text clipped - 274 lines] > of > > > all re my email address: oops, sorry! the "instructions" on the webpage are okay, but in my post i said to also change the number 1 to a number 2, when what i *meant* to say was to change the number 2 to a number 1. at any rate, you're welcome again, and i'm glad that my suggestions helped you find a solution that works for you. :) > Tina, > I Tried to send you an email as you instructed but is came back [quoted text clipped - 3 lines] > I thank you very much for the Demo data base. You took me to places I > didn't know existed. I really needed that Demo to do what you had told me to > do before. After doing the Query and Report as you instructed, I got the > correct totals, but the only thing was I was getting multiple line spaces [quoted text clipped - 15 lines] > So there it is for what it's worth. I'm sure you will never get this > question again, and I know your effort for me wasn't lost, because I ran your > solution to the problem, and it did verify that I was getting the correct > totals with my solution. [quoted text clipped - 260 lines] > > > > > > > > > > > > > > > > > > > > "TomC" <TomC@discussions.microsoft.com> wrote in message news:A70A7686-B24B-4255-99EB-CDE55A64920B@microsoft.com... > > > > > > > > > > > OK. I changed it and here is what I'm experiencing now - > > > > because [quoted text clipped - 20 lines] > > of > > > > all |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.