Duplicates caused by combinations

[grumpila]

Hello,

I have a table that contains a list of participants for various events. It is used to manage hotel reservations. One of the fields is "roommate", for participants who will share a hotel room. The names found in the "roommate" field must be participants as well. Therefore, For one hotel room, we might have 2 records (participant A with roommate B and Participant B with roommate A).

The problem is when I try to create a report showing the number of rooms required (as well as the names of the participants and roommates), I get twice the number of rooms and both combinations of names in the report (A:B and B:A).

How can I strictly count and show the first combination (in no particular order, maybe alphabetically?) ?



Thank you!

[June7]

Don't have roommate field. Just have a record for each person assigned room on the date.

Have you tried Sorting & Grouping in the report?

[grumpila]

Thank you for your reply,

Unfortunately, I inherited the database like that .

Even if I were to remove the roommate field, I still need to be able to report on who will be sharing a hotel room.

A form allows the user to determine who is sharing a room with who, but as I mentionned earlier, the report then shows duplicates (the A:B; B:A combinations) and overestimates the number of hotel rooms needed since it counts all combinations individually.

Is there any hope? Removing that field would lead to a lot of changes in forms, queries and other reports...

Thanks!

[June7]

I would probably have to review db. At the least, post sample data and query statement because I cannot visualize what you are doing.

Yes, you might have to modify objects to ignore that field.

[grumpila]

Thank for your help, I really appreciate it!

Here is a quick screen capture of the report and table relations, as well as the DB here SampleDB.zip. I apologize, there's a bit of French in there, I translated as much of the content as possible, all confidential data has been masked.

As you can see on the report, the name of the participant (Nom Prenom client) is replicated as the roommate two lines below (co-chambreur) and vice versa. That means that under the group " Lit(s) " (2 beds), it counts 4 rooms, wheras in this example, it should really only count 3 rooms (1. Johnson, Elizabeth shared with Smith, Avery; 2. Johnson James; 3. Smith William).

Also, I would like to see only 1 instance of that combination (either Johnson, Elizabeth as client and Smith, Avery as roommate, or the other way around, not both combinations).

In the table relationships diagram, you can see where the name of the participant comes from.

The name of the report in the DB is : "05 Par année / Besoins Hébergement" (lodging requirements per year)

[orange]

?? Why does this roommate two lines below (co-chambreur) show names sometimes and numbers sometime??

My first thought was there are multiple ways of populating this field. That may be an issue with design and subsequent reporting/querying.

Further to June's comment in post#2, you want to report Room-occupants, not individuals per se. In logical design terms you would have
Individuals/Guests, Rooms, Room-Occupants ( or whatever terminology applies to your business).

eg
Guest (Name, id)
Jane, 100
Polly, 123
Susan ,342

Room (RoomID)

2000
2300
2301

Room-Occupant (assuming we are dealing with consistent dates which are not shown)

RoomID, GuestID
2000, 100
2000, 342
2301, 123

which indicates Guest 100 and 342 are sharing Room 2000 for this stay.
Room 2301 has guest 123
Room 2300 is unoccupied

This is the concept you are dealing with.

Good luck.