Summarizing records in one table that match a different table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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 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
 
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
 
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
 
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
 
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
 
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
 
Thanks Tina. I'll give this a go tomorrow.
Tomc

tina said:
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
Click to expand...
 
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
 
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.
 
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
 
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. :)
 
Back
Top