Unique Records in Header

[AGGGG]

I am trying to count the unique number of records, to be displayed in a grouped header.

The fields are:
week_no
delivery_date
delivery_postcode
delivery_id

There are 7 records with only 4 unique delivery_postcodes

The first 3 fields have been grouped with their own group header, with the delivery_id in the detail section.

In the delivery_postcode header, I have added a text box and counted the number of delivery_postcodes, The result is 2, 2, 2 and 1, which adds up to 7

The result that I am looking for, is in the delivery_postcode header, for the unique number of delivery_postcodes to be values of 1, 1, 1 and 1 (ie, the number of unique delivery_postcodes)

I then also, want to be able to display in the week_no header, the total number of unique delivery_postcodes, ie, 4

 

[dhookom]

Add a text box to the delivery_PostCode group header:
Name: txtCountPCs
Control Source: =1
Running Sum: Over All
Then add a text box to the report footer with a control sourect of:
Control Source: =txtCountPCs

Duane
MS Access MVP

 

[AGGGG]

Hi Duane,

Thanks for such a quick response. You answered the query I requested, but, (there's always a but

If the record had multiple week numbers, adding the text box to the Report Footer, will give a grand total for all the unique post codes.

I was wanting to sub-group the number of unique post codes by week number ?


Andy

 

[dhookom]

I think you could do this with creating more than one running sum in various group headers. It would get fairly messy. The option that I would use is to create a total query(s) that group by WeekNum and PostCode. Then another totals query that counts the PostCodes by WeekNum. Add this totals query to your report's record source and join the WeekNum fields. This allows you to add the Count of PostCodes to the report.

Duane
MS Access MVP

 

[AGGGG]

Hi Duane,

Success !!!

Using your suggested 'total query'approach, did the job

Andy