Query one-2-many only invited to certain events

[sflknight]

Hey all,

I have a database with a table listing guests, then a table listing events they are invited to. Some guests are only invited to one (1) event, while others are invited to multiple. I am trying to query only guests that are invited to "event a" and nothing else, then guests who are invited to "event a" and "event b". Does anyone have guidence on how I can accomplish this?

Thank you!

[ItsMe]

Sounds like you will need to create a junction table. The third table could have as few as three columns; its own PK and an FK for each of the other two tables. Use code in your form to update the, third, junction table.

[sflknight]

I'm still a little bit confused, would you mind helping me out in a little more detail? So I have GuestID in the tbl Guest, then an RSVP table that has GuestID and the Event their invited to.

[ItsMe]

So three tables. As I type this out it, it would probably be a little different than I described in my previous post. Does not seem you will need a Junction table, technically.

tblGuest
Would include all the personal details of the guest.

tblRSVP
Would include all the details of a reservation. It would have fields for Date of reservation, date and time reservation was created, maybe a confirmation Boolean, etc.
It would also have the Key value from other tables not listed here. Maybe the Key value from a table that describes the salesperson/associate that created the reservation. You would also include the Key value from tblGuest.

tblRSVP_Jct
Here, there would be at least three columns. The autonumber, the key value from tblRSVP, and the key value from another table that describes the event. You can have many records in tblRSVP_Jct that are associated with a single record from tblRSVP.


Hope this helps out.

[sflknight]

Right I already have that set up. The problem I run into is bosses want the following reports:
1. Those ONLY invited to Event A
2. Those ONLY invited to Event B
3. Those invited to Event A and Event C
3. Those invited to Event B and Event C

Does that make sense? So the jct table is already created, that is the RSVP tbl. RSVP table can have Guest A with one record, or multiple (different events). It's confusing, sorry. But I really appreciate your help.

[ItsMe]

Reports do not typically dictate how data is stored in tables. Relations are relations are relations. Either it is normalized or it is not.

If you want to provide your DB for analysis remove private data and upload here. I should have time later to take a look.

The issue is you need a table to hold the reservation. That will have a unique identifier. Then, you need another table to explain the many things the reservation is associated with. There may be one record or there may be several records that have the RSVP table’s PK value in its FK field.

[sflknight]

I don't need anything stored, everything is already stored. What I need is to pull a query for each of those secifications listed above. Everything is already stored, I just need to query.

[ItsMe]

The query builder will help to build queries.

[sflknight]

Thank you ItsMe. I guess I'm not explaining it correctly.

RSVP table has the following:
AutoNum (PK)
GuestID
EventName

IE

AutoNum GuestID EventName
1 101 EventA
2 101 EventB
3 102 EventA
4 103 EventB
5 104 EventC

I want to pull Guests who were ONLY invited to Event A, and Guests who were invited to Event A AND event C. Does that make sense?

[ItsMe]

This is SQL to retreive only records for EventA

SELECT RSVP.AutoNum, RSVP.GuestID, RSVP.EventName
FROM RSVP
WHERE (((RSVP.EventName)="EventA"));

THis is SQL to get both EventA and EventC

SELECT RSVP.AutoNum, RSVP.GuestID, RSVP.EventName
FROM RSVP
WHERE (((RSVP.EventName)="EventA" And (RSVP.EventName)="EventB"));

You could base a form's Recordsource property off of the following SQL

SELECT RSVP.AutoNum, RSVP.GuestID, RSVP.EventName
FROM RSVP;

And then dynamicaly update the form's filter by applying a WHERE clause to the form's Filter or Recordsource.

WHERE (((RSVP.EventName)="EventA" And (RSVP.EventName)="EventB"));

I believe the filter property does not use the WHERE operator, so you would just leave that part out.

[sflknight]

Yes. However, the Event A list should not show anyone listed in the Event A and C list too. If they are on the Event A and C list, they should not be on the Event A or the Event C only lists. Does that make sense? lol

[ItsMe]

No, that does not make any sense. How you construct the WHERE clause is entirely up to you. So, if you want to use the AND operator to include those from EventA and EventC you would use the WHERE clause as illustrated here.

Perhaps you should decide when and how you want to apply the WHERE clause. Meaning, do you want to build it all into a query object? Perhaps have a parameterized query? Or do you want to use a form and VBA to dynamically update the WHERE clause. I think this would be the next step.

[sflknight]

So in the example table RSVP:

AutoNum GuestID EventName
1 101 EventA
2 101 EventC
3 102 EventA
4 103 EventB
5 104 EventC

My queries:
1. Those ONLY invited to Event A
Returns: 102
2. Those ONLY invited to Event B
Returns: 103
3. Those invited to Event A and Event C
Returns: 101

Does that help? The problem is on all 4 seperate queries, the guest should only appear once (1) across all 4.

[ItsMe]

The SQL examples in post #10 should provide the queries you described in post #13. What you are describing are separate queries. If the results from the SQL example do not meet your needs you may need something else entirely.

The following does not seem conventional or even rational.
"The problem is on all 4 separate queries, the guest should only appear once (1) across all 4."

Perhaps a crosstab query will help. The wizard can guide you.