Patient Tracker and Package Tracker

You don't need to import your Excel files into tables in Access.  You can link to them, which will mean the database will always reflect updates of the data in the Excel files.  You can then return the pro rata income per therapist per day with the following query:

SELECT [Patient Attendance Tracker].[Date], [Patient Attendance Tracker].Therapist,

SUM([Package Price]/((Nz([Package to],DATE())-[Package From])+1)) AS [Pro rata daily income]

FROM [Patient Attendance Tracker] LEFT JOIN [Patient Price Tracker]

    ON ([Patient Attendance Tracker].[Date]>= [Patient Price Tracker].[Package From])

    AND ([Patient Attendance Tracker].[Date] < [Patient Price Tracker].[Package To]+1 )

    AND ([Patient Attendance Tracker].Department = [Patient Price Tracker].Package)

    AND ([Patient Attendance Tracker].Therapist = [Patient Price Tracker].Therapist)

GROUP BY [Patient Attendance Tracker].[Date], [Patient Attendance Tracker].Therapist;

This will return the pro rata income per therapist per date for those dates where a patient has an attendance date within a range specified for the therapist in a row in [Patient Price Tracker].  With the data in the two Excel files you posted this is not always the case, so a therapist's name will be returned for a date, but with a Null at the [Pro rata daily income] column position.  Also, in one case the range has a start date later than its end date.

If you wish to return rows for all dates, in addition to those returned by the above query, then you'd need to include an auxiliary Calendar table in the query as follows:

SELECT Calendar.calDate, [Patient Attendance Tracker].Therapist,

SUM([Package Price]/((NZ([Package to],DATE())-[Package From])+1)) AS [Pro rata daily income]

FROM Calendar LEFT JOIN ([Patient Attendance Tracker] LEFT JOIN [Patient Price Tracker]

    ON ([Patient Attendance Tracker].[Date]>= [Patient Price Tracker].[Package From])

    AND ([Patient Attendance Tracker].[Date]< [Patient Price Tracker].[Package To]+1 )

    AND ([Patient Attendance Tracker].Department = [Patient Price Tracker].Package)

    AND ([Patient Attendance Tracker].Therapist = [Patient Price Tracker].Therapist))

    ON Calendar.calDate = [Patient Attendance Tracker].[Date]

GROUP BY Calendar.calDate, [Patient Attendance Tracker].Therapist;

For means of generating auxiliary calendar tables take a look at Calenadr.zip in my public databases folder at:

https://onedrive.live.com/?id=44CC60D7FEA42912%21169&cid=44CC60D7FEA42912

This little demo file you'd use the Daily Calendar option, and possibly exclude weekends and/or public holidays, for which the demo includes provision.

team280 

sql:

select * from Sheet2;

select * from Sheet1;

select Sheet2.f03,sum(f06) from Sheet2 join Sheet1 on Sheet2.f02=Sheet1.f03 and Sheet2.f03=Sheet1.f02 group by Sheet2.f03;

team280 here is a video on how to upload your csv to a database.

import csv .

i also attached a ms db. open it and upload the csv to Patient Attendance Tracker table.

after you successfully uploaded the csv, you open Form1 for preparation to print your report.

team280 , can you upload the real csv file.

it is hard to copy and paste the one you show in excel.