Re: Count records, but not dupes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

As you can imagine, it's hard for us to see exactly what you are doing, as we cannot see your report, its sections, or its source query and the tables that feed that.

Presumably you have some text boxes on a form that provides the limiting dates. If so, you can concatenate the value of the dates into the Control Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level, another alternative is to use a subquery in the source for the report, so it brings the totals into the report. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chrissy" <Chrissy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DB6ABC18-39DE-4414-9D6A-27838BB082D9@xxxxxxxxxxxxxxxx
I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to $x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the way?


Again, thank you so much,
--
Chrissy


"Allen Browne" wrote:

I'm not sure exactly how your report is laid out, but if a group is defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chrissy" <Chrissy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E64AE2C-49D3-4EEA-BB43-E125C14CC364@xxxxxxxxxxxxxxxx
> Thanks, Allen. That seems a great tool. I am still having a bit of
> issue,
> though. It returns the TOTAL count of clients for the report's range.
>
> I cannot figure how to make it count only within a group, returning > only
> the
> count for that group. When I place a control with ECode into each > group
> it
> returns all clients for that date. I thought it would return only that
> group's clients. I tried to adjust the code, but I don't know how to
> ensure
> selected group count.
>
> How do I code it to only count of clients in the group it is placed in?
>
> I have now: =ECount("ClientID","[tbl 1 > Client]",[Report].[Filter],True)
> and my groups are [Source] and [SinceDate].
>
> I am strictly an amateur.
>
> Thanks for the help.
> -- > Chrissy
>
>
> "Allen Browne" wrote:
>
>> Copy the ECount() function from this web page:
>> http://allenbrowne.com/ser-66.html
>>
>> This is an extended DCount(), with an extra argument that lets you ask
>> for a
>> distinct count, so you can use a text box with Control Source like >> this:
>> =ECount("ClientID", "Table1", [Report].[Filter], True)
>>
>> -- >> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Chrissy" <Chrissy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:6747D455-3F7D-40B7-8B72-C910642F60A5@xxxxxxxxxxxxxxxx
>> >I am designing a report to return new clients, by source, by
>> >client-since
>> > date, within a date range. I have a form I use to enter dates that
>> > calls
>> > the report, passing the dates.
>> >
>> > I have groups on [Source] and [SinceDate] (grouped by month). In >> > those
>> > headers, I count invoices, dollars and average them. This is as
>> > expected,
>> > groups SinceDates for each Source. The numbers are correct.
>> >
>> > My issue is that I want to count the number of new clients >> > [ClientID]
>> > for
>> > those same groups-no dupes, display and use results in calculations.
>> > Note
>> > that each new client in the month they are new, may have multiple
>> > invoices.
>> > Thus, when I count [ClientID] it always equals the invoice count.
>> >
>> > I have searched and tried Count, DCount, tried counting on the >> > report's
>> > record source, to no avail.
>> >
>> > How do I count, in those groups, the new clients only once? I need
>> > something like:
>> >
>> > =Count(NewClientsNoDupes for this group, where [SinceDate] falls >> > within
>> > the
>> > date range I am passing to the report)
>> >
>> > What is placed in the [Source] group header should work also for the
>> > [SinceDate] group (monthly) when placed in that group header -- am I
>> > correct?
>> >
>> > I appreciate any help.
>> >
>> > Thanks.
>> > -- >> > Chrissy
>>
>>



.

Relevant Pages

  • Re: MS Access Reports and VB.NET Program
    ... think that I want to ship a 30mb .NET framework to each of my clients? ... Barns & Noble has some books that could help you learn .NET. ... It is Access TIMES eCommerce TIMES Sql Server. ... that you actually posted how to execute the report using a macro. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Summarizing records in one table that match a different table
    ... the report. ... using "Language" as the example (i had to add a field to my Clients test ... i created a report based on the query with the joins ...
    (microsoft.public.access.reports)
  • Re: Selecting Records to Print Based on Criteria
    ... I double-clicked on a report selector to open ... parameters that have nothing to do with criteria. ... one called Clients and one called Invoices. ...
    (microsoft.public.access.reports)
  • Re: Print Preview with EXPORT capability
    ... I would guess the txText control is what you want, ... It has rtf and html export and V7 came with a pdf component to export PDF. ... But I am trying to NOT have to have my clients acquire/install any version ... just to vew the report as a PDF. ...
    (microsoft.public.vb.general.discussion)
  • Re: Count records, but not dupes
    ... report as expected. ... Allen Browne - Microsoft MVP. ... I may have 20 clients that have 25 invoices that sum to $x. ...
    (microsoft.public.access.reports)