Listing Duplicates from mutiple Tables

[nikana]

I'm assembling a list of amateur athlete performances from various competitions.

• Data for each competition is assembled in separate tables, which includes multiple fields for their performance.
• The athlete ID is two fields; 'First_Name' and 'Last_Name'.
• The performance data also has consistent field names; eg. 'achievement', 'Body_Weight', 'Club'.

Functionally, I'd like to have a query that will assemble all the athletes names, and list all the competitions they participated in. Then I'd like a separate query assembling each athlete's achievements / performances from the competitions they participated in. The constant for this is the athlete's ID.

My SQL skills are quite limited.
Any direction will be greatly appreciated.

I'm using:
Windows Version: Windows 10
Access Version: Access 2016

[June7]

Names make very poor unique identifiers.

Each competition is in its own table? Should be one table for competitions with another field for competitionID. Otherwise, use a UNION query to combine the multiple competition tables into the single dataset should be to begin with.

[nikana]

Thank you June7, I will explore a UNION query. I'm also not sure I know how to combine the first and last name identifiers when searching other tables, but will see if I can figure it out.

I considered combining all competitions into one table but I'm going back decades and it would become quite cumbersome.

The data I am importing has the names already established, and there will be hundreds (maybe thousands?) by the end. What would you recommend instead of names for identifiers?

[June7]

In conventional structure, would have a table Athletes where each record is unique and autonumber field could generate unique PK. The AthleteID would be saved as FK in the Competitions table. However, since you are importing data, this may not be simple. If your import does not have any unique identifier other than the names, then there is no other connection to Athletes table. Pray you don't have multiple athletes named John Smith.

What's cumbersome is the non-optimized data structure you are working with. Why would correcting structure be more cumbersome than having to resort to UNION query? Once you have the data UNIONed, use it to create combined table.