Create a Report query from two tables

[PatD]

I have two tables:
(TblOne) contains multiple (CompetitorID), (Race Date) & the (Race Finishing Position) on that date, sorted by race Date.
(TblTwo) contains all the years (Single Race Dates) and the (No of Competitors) for each RaceDate, sorted by Race Date.

Race Dates have an inner join from TblOne to TblTwo.
A Form is used to obtain the CompetitorsID from a single selected item listbox. This works OK.

For the Report I want to display by CompetitorID (The Report Heading) and then columns to show RaceDate, No of Entries, Race finishing Position.
I actually have two more columns which are calculated using ‘No of Entries’ & ‘Race Finishing Position’
Could anyone help with:
The writing of the Select Query please &
Can I do calculations within the report column Control Source?

Many thanks
Pat

 

[jzwp22]

If I understand you table structure correctly, I believe that this query along these lines will work. (I would recommend not having spaces and special characters in the table or field names)

SELECT TblTwo.SingleRaceDates, TblTwo.NoOfCompetitors, TblOne.CompetitorID, TblOne.RaceFinishingPosition
FROM TblTwo INNER JOIN TblOne ON TblTwo.SingleRaceDates=TblOne.RaceDate;

I personally would not use a date field to join tables. I generally use an autonumber field in the main table that joins to a corresponding long number field in the related table.

I've attached an example database that has the report I came up with. And yes, you can do calculations on the report.

 

[PatD]

Many thanks for that, I thought my problem was the query but your answer showed my problem was how to use the query results on the Report.
Thanks once again, Pat

 

[jzwp22]

You're welcome. Good luck with your project.