Summarizing records in one table that match a different table

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 07:46:01


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
 
 
 

Summarizing records in one table that match a different table

Post by tina » Wed, 28 Dec 2005 08:14:34

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




each
Client
don't
a
the
not.
that

 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 09:50:02

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
 
 
 

Summarizing records in one table that match a different table

Post by tina » Wed, 28 Dec 2005 11:07:25

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




Activities
screen
you

the
Clients.ClientID
Client


for
an
I
"active"
using
counts
or
 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 11:35:02

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

"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by tina » Wed, 28 Dec 2005 11:45:54

ell, 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


"TomC" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
life
it's
I
as
tblClientActivities.NumberOfClothesVouchers,
where to
it
menu
entire
if
the
you
Clients
to
entry
contains
all
because
tried
record
me so


 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 12:09:01

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

"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by tina » Wed, 28 Dec 2005 12:30:23

kay, 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


"TomC" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
a
1.
doesn't
activity
suggest
the
respectively
Time
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords
the
but
proceed.
SQL
=
it's
explain
the
suggested is
it
the
etc."?? So
done in
fields
table
those
not
the
it
give


 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 12:52:02

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
"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by tina » Wed, 28 Dec 2005 14:08:55

kay, 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


"TomC" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
"Language
Detail
The
that
counting?
need
report
based
need
that. so
know I
time
records
of
change
the
system
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords
for
entered. So
asked,
the
tblClientActivities.ClientIdfk,
tblClientActivities.GasVoucherAmt,
tblClients.Zip,
tblClients.ClientId
you
and
on
the
the
and
at
the
the
Activites
only
one
Table
But
question,
for
I've
but
Activity
could


 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Wed, 28 Dec 2005 21:43:03

hank 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

"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Thu, 29 Dec 2005 06:07:35

'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

"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by tina » Thu, 29 Dec 2005 12:32:23

ell, 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


"TomC" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
"txtClientCount".
in
As
and
attack,
in
this,
joins
i
up
OnPrint
OnFormat
view,
you're
way
just
to
this
this
Table.
the
now
or
you
query
with
I
every
count
correctly
my
records
above.
you
how
and
see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords
couldn't
you
copied
tblClientActivities.DateUpdated,
tblClients.FirstName,
show
well,
to
and
copy
with
table
looking
Join
have
all
add
=
but
news: XXXX@XXXXX.COM ...
is
Activities
Client.
(Count(*))
ID.
source,


 
 
 

Summarizing records in one table that match a different table

Post by VG9tQ » Thu, 29 Dec 2005 23:31:07

es, 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

"tina" wrote:

 
 
 

Summarizing records in one table that match a different table

Post by tina » Fri, 30 Dec 2005 11:10:12

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


"TomC" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
list
surprised,
things in
it. i
how i
question is
and
on
that
intCopunt
code. I
Helpless
language
do
test
dialog, i
next,
edge
its'
without
it
following
design
only
have
element
for
with no
doing
In
of
for
but
header
if
each
a
just
clients
do
because
count
activity
a
all
*before*
see
named
Date
the
already.
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords
what
let us
have
tblClientActivities.MEMO,
i'll
trying
view,
and
news: XXXX@XXXXX.COM ...
you
query
Clients