Re: Summarizing records in one table that match a different table

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


"TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DB27C8DB-195A-4243-BAFD-50E82DF3800A@xxxxxxxxxxxxxxxx
> 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
> "tina" wrote:
>
> > 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
> >
> >
> > "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:A70A7686-B24B-4255-99EB-CDE55A64920B@xxxxxxxxxxxxxxxx
> > > 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
> > >
> > > "tina" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:01A3D5B5-9917-49C6-8240-BC7018B60ABD@xxxxxxxxxxxxxxxx
> > > > > 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
> > > > >
> > > > > "tina" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:96360D1A-CDBD-43E9-BE05-31B0089C9887@xxxxxxxxxxxxxxxx
> > > > > > > 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
> > > > > > >
> > > > > > > "tina" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > > "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > > > news:AEA3CF2D-36C3-4F77-9275-54CD66E182E0@xxxxxxxxxxxxxxxx
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Relevant Pages

  • Re: report showing all
    ... If a report returns blank, then go to the query behind the report. ... named Restaurants, and that form is open when the query runs, then... ... That worked wonderfully for my Clients. ...
    (microsoft.public.access.reports)
  • Re: Blank Subreport for table with no related records
    ... I had to adjust a little because my main report is based on a query to ... As an example, say the main report lists clients, and the subreport lists ...
    (microsoft.public.access.reports)
  • Re: Formatting/Coding a Report...
    ... Then you could export the query a an Excel ... or you could set up a report with that same ... > whether they are new or existing clients. ... > totals column on the right side of the report for individual programs and ...
    (microsoft.public.access.reports)
  • Re: Summarizing records in one table that match a different table
    ... could create a file of matching client records, it would make it so much ... > tables, a query, and a report, to work out the solution before posting it. ... >>> because i had to use VBA to get the count of clients for each language ...
    (microsoft.public.access.reports)
  • Re: Summarizing records in one table that match a different table
    ... > the report. ... > using "Language" as the example (i had to add a field to my Clients test ... i created a report based on the query with the joins ...
    (microsoft.public.access.reports)