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

Yes, by all means, let me have your website address so that I can download
it. I sure would like to know how to do this. I found it easy to detail list
just those client records which have an activity record, but it is
frustrating that I can't just use those records for summary purposes. If I
could create a file of matching client records, it would make it so much
easier for me to use that file for summarizations that I need to do. Oh
well, such is life. I do appreciate you coming back, but I wasn't surprised,
beacuse of my past experience with your helping me.
Thanks again, Tomc

"tina" wrote:

> well, i was afraid of that - sometimes *i'm* hopeless a describing things in
> text so they make sense. i built a small db in A2000 with just the two
> tables, a query, and a report, to work out the solution before posting it. i
> can load it to my website, Tom, if you'd like to download it to *see* how i
> set the report up. let me know if you want to take a look.
>
> hth
>
>
> "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:44850346-3BC0-4AA5-ABC9-18C325760AE8@xxxxxxxxxxxxxxxx
> > I'm back again. I couldn't get it to work. I can do everything you
> > suggested but I probably put things in the wrong place. First question is
> > with my instruction to add an unbound textbox and name it
> "txtClientCount".
> > I used the Text Box tool and inserted "txtClientCount" in the text box and
> > deleted the label. That probably isn't correct because I got an error on
> > "txtClientCount" as being undefined? Second question you instructed that
> in
> > the VBA code window *above* the top procedure, add the code "Dim intCopunt
> As
> > Integer". I'm not sure where you intended me to put that line of code. I
> > tried different places but obviously, I didn't do it correctly. Helpless
> and
> > Hopeless, Tom
> >
> > "tina" wrote:
> >
> > > okay, i think i get the picture. i also think i'm having a stupid
> attack,
> > > because i had to use VBA to get the count of clients for each language
> in
> > > the report (using your example). there's probably an easier way to do
> this,
> > > but here's what i came up with:
> > >
> > > using "Language" as the example (i had to add a field to my Clients test
> > > table for language), i created a report based on the query with the
> joins
> > > we've already discussed. in the report's Sorting and Grouping dialog, i
> > > added the Language field and set the GroupFooter property to Yes. next,
> i
> > > added the ClientID field and set the GroupHeader property to Yes.
> > >
> > > in the report design view:
> > > i removed any fields from the Detail section and dragged the bottom edge
> up
> > > so the section was "closed".
> > > i removed any fields from the ClientID Header section, and dragged its'
> > > bottom edge up so it was as close to closed as i could get it - without
> > > actually closing it.
> > > in the Language Footer section, i added an unbound textbox and named it
> > > txtClientCount.
> > > back in the ClientHeader section, i added the following code to the
> OnPrint
> > > event procedure, as
> > >
> > > intCount = Nz(intCount, 0) + 1
> > >
> > > in the Language Footer section, i added the following code to the
> OnFormat
> > > event procedure, as
> > >
> > > Me!txtClientCount = intCount
> > > intCount = 0
> > >
> > > in the VBA code window, *above* the top procedure, i added the following
> > > line of code, as
> > >
> > > Dim intCount As Integer
> > >
> > > if you don't know how to create an event procedure from report design
> view,
> > > see the instructions to "Create a VBA event procedure" at
> > > http://home.att.net/~california.db/instructions.html.
> > >
> > > if you were using a separate query for each report, you could do the
> > > counting in a Totals query, and base the report on that. but since
> you're
> > > using the same query for a number of different reports, this is the only
> way
> > > i could figure out to do it.
> > >
> > > hth
> > >
> > >
> > > "TomC" <TomC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:DB27C8DB-195A-4243-BAFD-50E82DF3800A@xxxxxxxxxxxxxxxx
> > > > 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
.

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: 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)
  • 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)