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

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