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

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


"TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:44850346-3BC0-4AA5-ABC9-18C325760AE8@xxxxxxxxxxxxxxxx
> 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: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
Loading