The accomodation record is a record in a table called tbl_Accomodation - fields are accom_id, date_in, date_out,venue,employee_fk,team_name.

The timesheet table is tbl_Timesheet - timesheet_id,employee_fk,timesheet_date,start_time,End_time,Team,Location. Both tables are on the many side of the table tbl_employee and the primary key in that table is employee_id. So in both the child tables, the child employee_fk is Foreign Key. That is the join.

I have an employee form based on a select query (SELECT * FROM TBL_EMPLOYEE). The timesheet and accomodation forms are subforms on that employee form and are linked by the employee_id/Employee_fk join. That all works fine (both subforms are populated by (SELECT * FROM respective table)
However, I now need to be able "link up" the accomodation and timesheet to the team. The difficulties are:

1) one accomodation record might span several days as the booking might be for multiple days but the timesheets have a separate entry (record) for each day.

2) The ladies doing the data entry refuse to have to create "single day" accomodation entries (if they would I would not have an issue as I could join the timesheet and corresponding accomodation record by TEAM and Timesheet_DATE and Accomodation.date_in).
To add a level of complexity, a "TEAM" is dynamic in this context. That means different employees in the same or different team each week. To support this, the accomodation data is organised with a accomodation "header" - accom_id,Team, datein,dateout,venue. Another table, tbl_Accom_People (Accom_People_ID, Accom_FK,Employee_FK,Cost) has many join to tbl_Accomomdation via the child key Accom_KF.

I need to be able to create a list for each Accomodation "Header record" of dates included between datein and dateout and then link that specific date to that specific timesheet date for that specific team.

---

Reply from Richard Rost:

Your queries just need inequalities in order to show the proper related records. For example, you'd want to show all records in your Timesheet where the date is between the start date and end date in your Accommodation table. I cover something similar to this in Access B9 where I show how to display records from a specific date using an inequality.

I'd need to play with your database to figure out an exact solution for you, but you should be able to do this without programming. If not, a simple recordset loop would make it work too.

If you want to send us your database to look at, you can submit it via the TechHelp page. I'm pretty slammed until the end of the year, but perhaps Alex could look at it for you.