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

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: Summarizing records in one table that match a different table
    ... If I have no activity records for a client record, ... > and btw, i noted two fields in your tblClientActivities, named respectively ... >> of me figure out where or how this SQL statement could get entered. ...
    (microsoft.public.access.reports)
  • Re: Automatically Move Records
    ... Cascade Delete is only set in a relationship. ... either by a delete query or code. ... Your Resource For Help With Access, ... >>Are you sure the main client record is deleted from the ...
    (microsoft.public.access.queries)
  • Re: Automatically Move Records
    ... Are you sure the main client record is deleted from the table or is just not ... Does it matter that my query join option is to ... >>> (the supervision levels change often, ... >>> just "move" the supervision record from one table to the ...
    (microsoft.public.access.queries)