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

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

"tina" wrote:

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

Relevant Pages

  • 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)
  • 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)