Re: Summarizing records in one table that match a different table
- From: "tina" <nospam@xxxxxxxxxxx>
- Date: Tue, 27 Dec 2005 02:45:54 GMT
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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >
.
- Follow-Ups:
- References:
- Re: Summarizing records in one table that match a different table
- From: tina
- Re: Summarizing records in one table that match a different table
- From: TomC
- Re: Summarizing records in one table that match a different table
- From: tina
- Re: Summarizing records in one table that match a different table
- From: TomC
- Re: Summarizing records in one table that match a different table
- Prev by Date: Re: Summarizing records in one table that match a different table
- Next by Date: Re: Report on Total Population changes
- Previous by thread: Re: Summarizing records in one table that match a different table
- Next by thread: Re: Summarizing records in one table that match a different table
- Index(es):
Relevant Pages
|
