Count results - ignore duplicates

[littleboots]

I need to count the number of double beds required on a report. Currently the report is sorted by room number and then shows the people who are occupying the cabin (either 1 passenger or 2 passengers).
If I do a Count by either CABIN or by BKRF (booking reference), =COUNT([BKRF]), then it counts every record, whereas I only want to count the booking reference or cabin once, no matter how many times it appears in the report results.
Is it possible to do this using a text box (I'm not very good at the VB or SQL side of Access!)

 

[scriverb]

First of all this thread(question) should be posted in the Reports Forum. This is the Forms Forum.

Secondly, we need more information about your report. You say you are sorting on Room Number and then shows the people in the room. Does each person occupy a seperate record? Or is there a field that indicates 2 people to this room? Are you utilizing the Grouping feature to have a Group Header or Footer for one of the fields? These are questions that we need to know about before we can answer you question.

So, please describe in detail the record description of important fields and exactly what you are looking to count and where you want the count displayed.

Repost with that information and we can proceed with assistance. Bob Scriver

 

[WildHare]

Yeah, like Bob says - how do you know who requires a double bed? Is it because there are Two people booked? What if a single person wants a double bed? What if two passengers want two twin beds? ((Rob and Laura come to mind..

It is NOT possible to do this using a text box. Per se. You will do this in a 'formula' or 'expression', and show the result in a text box. It could also be done in a query expression.

The only way to "count the number of double beds required" is to have some sort of flag set PER CABIN that resolves as to whether or not the CABIN requires a Double, or if a cabin can have more than ONE double bed, HOW MANY double beds..

Then, you won't COUNT so much as SUM the numbers up.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[littleboots]

Firstly - sorry, I didn't realise I'd selected the Forms Forum... apologies

In my report there is one record per passenger with the fields Booking Reference [BKRF], [NAME], and [CABIN].
The report is based on a query that only pulls records with bookings that have a double bed configuration requested. Every passenger has a record against their name of T (Twin) or D (Double), I only want to report on those with Double Beds. My report is then sorted in cabin order.
If Mr Smith is travelling with Miss Jones and they've requested a double bed, there are two records for the one cabin/booking ref.
If Mr Green is travelling alone, but requires a double bed, he'd have one record.
So in that example although there are 3 records, I only need to count (or sum) 2 double beds

Does that make a little more sense?

 

[scriverb]

Okay, add a new column to your query that is populating the report.
One: 1

Now in your report click the Sorting and Grouping button and you need to be sorting on [BKRF] and answer Yes to a Group Footer.

Now you have a Group Footer for the field [BKRF] which should be equal to one double-bed counter, correct? Pick One from your Field List and place in in the Group Footer Section. Delete the label and make it's property visible = false. Also, push it up so that its bottom and top at at the top of the section. Just a thin line. Now push up the Group Header. We won't actually be seeing this section.

Now select from the View Menu Report Headers and Footers. In the Report Footer create another control and call it Count_Of_DblBeds. Give it a control sounce of this:
=Sum([One])

Now run the report. You should get a count of the double-bed bookings. let me know if you have any trouble with this concept. Bob Scriver

 

[scriverb]

Hold off on this last posting. Just create a new query with the following code and name it qryBKRF
SELECT tblYourTableName.BKRF
FROM tblYourTableName
GROUP BY tblYourTableName.BKRF;

Create a control in the Report Footer with the following control source:
=DCount("*", "qryBKRF&quot

This should do it.
Bob Scriver