Comparing two tables

[Bhoc]

I have searched on this and cannot find a thread that seems to match want I need to do.
As part of a database I have 2 tables. One which has players added to it and the other (foursomes table) is filled with data from the players table when user collects data using a form. The user selects three players from the players table and combines them into one record in the foursomes table. I need to compare the two tables to see if the player listed in the players table is now showing up within the records on the foursomes table. The players only that have the two check boxes - Block Entry and Day 3 Only - as true are eligible to be in the foursomes table.
I have attached the two tables to this thread hoping the above makes sense.:banghead:

 

[jdraw]

I took a quick look at your database. My first impression is that you have no relationships defined and your tables do not look like they have been normalized.
My suggestion is for you to step back from the details and tell readers in simple, plain English WHAT exactly you are trying to do. If there are processes involved please tell us about them -again simple English.

 

[stumac]

The players only that have the two check boxes - Block Entry and Day 3 Only - as true are eligible to be in the foursomes table.

No one in your 'Players' table have both set to true.

To see who is listed anbd who isn't you need to do a union query from your 'foursomes' table and join it to your 'players' table.

SELECT Players.Fullname, Q_Union.PlayerNo
FROM Players LEFT JOIN (select Player1 as player, "p1" as PlayerNo from foursomes UNION ALL select Player2 as player, "p2" as PlayerNo from foursomes)  AS Q_Union ON Players.PlayerID = Q_Union.player;

 

[Bhoc]

stumac

fantastic that was what I needed exactly
I should have said Block entry or Day 3 only to be true not both of them together

 

[accedeholdings]

I am subscribing to this thread because I just encountered the same situation.

 

[stumac]

Bhoc: no problem, if you edit the query in design view you will be able to add in the fields you need from players to set the criteria.

I should have said I totally agree with jdraw around the database design and normalisation. However I have been in a situation like this myself before where there were to many objects depending on the table(s) keeping the same format. Something to keep in mind for your next project though.

accedeholdings: can you give us some more information on your situation?

 

[Bhoc]

STumac

thanks for thatt - did that and it worked perfectly