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

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: Run macro for each record in a query
    ... the new query will pick up the correct ... This will make the report run for the current user. ... which contains details of 'client ... I have a macro called 'send_emails' which uses the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Field Blank in Report - New Information
    ... Please post the SQL of your query. ... I've been testing the report and have noticed that.. ... when I create a new record and type in a [client name] ... It's in the query that I created the [SortName] field. ...
    (microsoft.public.access.reports)
  • Re: Report filtered on a calculated field
    ... Make a query to determine who owes you. ... make this into a Totals Query. ... now you can base your first report directly on this query. ... totals for each client, also define a Client footer section as well. ...
    (microsoft.public.access.reports)
  • Re: Run macro for each record in a query
    ... the new query will pick up the correct ... This will make the report run for the current user. ... which contains details of 'client ... I have a macro called 'send_emails' which uses the ...
    (microsoft.public.access.modulesdaovba)
  • RE: query drops records
    ... two tables or queries in the final query. ... report includes four different groupings: ... client can have both allowed and unallowed claims. ... pulled into the report query [qry Claim Report]. ...
    (microsoft.public.access.queries)