T
Tanya
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
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