Re: Summarizing records in one table that match a different table
- From: "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 26 Dec 2005 18:35:02 -0800
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:
- Prev by Date: Re: Summarizing records in one table that match a different table
- Next by Date: Re: Summarizing records in one table that match a different table
- 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
|
