Query that sums a field based on 2 tables

[scratchmb]

I have 2 tables the first table "tblpayrollmiles" has the fields "Miles" "ProcessDate" , the second table "Accidents" has the fields "IncidentDate" "Rate".



I need to sum the "Miles" based on the "ProcessDate" greater than the last "IncidentDate" that the "Rate" ="1" Or "3" . I have been able to do that, however if there is no "IncidentDate" with "Rate"="1" Or "3" , I get no value.

Please help!

[June7]

Post the query SQL statement for analysis.

[scratchmb]

SELECT DISTINCTROW tblPayrollMiles.[Driver code], tblPayrollMiles.Miles, Last(tblAccidents.DateofIncident) AS LastOfDateofIncident, tblPayrollMiles.ProcessDate
FROM tblAccidents INNER JOIN tblPayrollMiles ON tblAccidents.[Driver code] = tblPayrollMiles.[Driver code]
WHERE (((tblAccidents.RATE) Like "1" Or (tblAccidents.RATE)="3"))
GROUP BY tblPayrollMiles.[Driver code], tblPayrollMiles.Miles, tblPayrollMiles.ProcessDate
HAVING (((Last(tblPayrollMiles.ProcessDate))>Max([tblAccidents].[DateofIncident])));

[June7]

The query provided some clarifying info, such as the correct names of table and fields and what the linking fields are, however, I am still unclear about what the issue is.

Do you mean rate for an incident date is neither 1 nor 3? What would it be otherwise? If no records match this criteria then the no results output is correct.

I think you will have to show some sample source data and the desired output. Could even provide the project.

[scratchmb]

basically what i'm trying to do is track consecutive safe miles for a particular driver based on either total miles if they have had no preventable accidents, or [Miles] since the last preventable accident. [RATE] can be 1, 2, 3, 4 - 2 or 4 doesn't affect consecutive safe miles while 1 or 3 would reset consecutive miles to 0 from the date of the accident.

For instance a driver may have 1,000,000 total miles since he started driving, however if he had a preventable accident 1 year ago, he would have only 125,000 miles since his last preventable accident. i need the query to show the total miles since he had his last preventable or if he has no preventable accidents it would show total miles

the table with the miles is updated every week [ProcessDate] the table with the accidents is updated at the time the driver has the accident [IncidentDate]

[June7]

Try:

SELECT tblpayrollmiles.[Driver code], Sum(tblpayrollmiles.Miles) AS SumOfMiles
FROM
(SELECT tblAccidents.[Driver code], Max(tblAccidents.DateOfIncident) AS MaxOfDateOfIncident
FROM tblAccidents
WHERE (((tblAccidents.Rate)="1" Or (tblAccidents.Rate)="3"))
GROUP BY tblAccidents.[Driver code]) AS Query1
RIGHT JOIN tblpayrollmiles ON Query1.[Driver code] = tblpayrollmiles.[Driver code]
WHERE (((tblpayrollmiles.ProcessDate)>Nz([MaxOfDateOfIncident],#1/1/2000#)))
GROUP BY tblpayrollmiles.[Driver code];

[scratchmb]

I'm going through my data but at first glance that looks like it worked exactly as i needed. I appreciate your help.