Query for Report

[Guest]

Hi All
I have two tables one called Ability(special Needs abilities eg deaf,
Autistic) and one called bookings, it’s a many to many relationship,
therefore I have a associative table called BookingsAbility eg a Ability can
be in many bookings and a booking can include many abilities.

The booking can have a number of children, a number of adults and a number
of students. I have therefore created a field in the Reports query that adds
the adults + children + students to give the total people in the booking.

I have grouped the report by BookingID then by AbilityID so I have the
booking then the list of the abilities under the each booking.

The problem I am having is in the report footer I want to be able to say how
many groups of 1, how many groups of 2, how many groups of 3 and how many
groups of 4 overall have made bookings.
I am currently using this =Sum(IIf([totalattend]>3,1,0) to achieve the
result for the groups of three.

Because of the way the query is written and the relationships are if there
were three different abilities within one booking instead of giving me the
answer of 1 group of three it is giving me the answer of 3 groups of 3,
because the booking is repeated three times in the query one for each of the
three abilities.

Does anyone know of a better way of doing this query/report so I can get
accurate information?

Any help would be much appreciated. (sorry bout the novel)

Thanks Tanya

 

[Allen Browne]

Yes, that makes sense. If you look at the data directly in report's query,
you will see there are 3 rows for the records where there are 3
disabilities, so the total count of records will be 3.

Workarounds include:
a) Leave the BookingsAbility table out of the query for the report, and use
a subreport to show this data.

b) Use a running sum instead of a count to collect the totals. This involves
putting a text box into the BookingID group footer, and giving it these
properties:
Control Source =1
Format General Number
Running Sum Over All
Name txtBookingCount
Visible No

Then in the Report footer section, put a text box with Control Source of:
=[txtBookingCount]