I have built a query that pulls past records in a certain month, and allows to add new records. This worked just fine ubtil I realized that I have to be able to distinguish rates by date as they change every year. I added a Rates Billing query along with the existing tables. It goes out and finds the rates just fine for existing records but I cannot add new records. below is the SQL statement for the query. Can you please help?
SELECT [Units Worked Table].UnitsID, [Units Worked Table].[Invoice #], [Units Worked Table].Date, [Worker table].LastName, [Units Worked Table].WorkerID, Services.ServiceID, [Units Worked Table].Service, [Units Worked Table].[Hours Worked], [Units Worked Table].Billed, [Units Worked Table].Paid, [Units Worked Table].Adjusted, [Units Worked Table].AuthID, [Units Worked Table].ClientID, [Units Worked Table].Modifier1, [Units Worked Table].Modifier2, [Units Worked Table].Modifier3, [Units Worked Table].Suspence, [Rates Billing Query].EffecDate, [Rates Billing Query].[Expire Date], [Rates Billing Query].Program, [Rates Billing Query].UnitConv, [Units Worked Table].Units, [Units Worked Table].AmtBilled, [Units Worked Table].AmtPaid
FROM ([Worker table] INNER JOIN (Services INNER JOIN [Units Worked Table] ON Services.ServiceID = [Units Worked Table].Service) ON [Worker table].WorkerID = [Units Worked Table].WorkerID) INNER JOIN [Rates Billing Query] ON Services.ServiceID = [Rates Billing Query].ServiceID
WHERE ((([Units Worked Table].Date)>=[Forms]![Billing Work Form]![MOStart] And ([Units Worked Table].Date)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].EffecDate)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].[Expire Date])>=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].Program)=[Forms]![Billing Work Form]![ProgChoice]))
ORDER BY [Units Worked Table].UnitsID, [Units Worked Table].Date;
Thanks, Doug
SELECT [Units Worked Table].UnitsID, [Units Worked Table].[Invoice #], [Units Worked Table].Date, [Worker table].LastName, [Units Worked Table].WorkerID, Services.ServiceID, [Units Worked Table].Service, [Units Worked Table].[Hours Worked], [Units Worked Table].Billed, [Units Worked Table].Paid, [Units Worked Table].Adjusted, [Units Worked Table].AuthID, [Units Worked Table].ClientID, [Units Worked Table].Modifier1, [Units Worked Table].Modifier2, [Units Worked Table].Modifier3, [Units Worked Table].Suspence, [Rates Billing Query].EffecDate, [Rates Billing Query].[Expire Date], [Rates Billing Query].Program, [Rates Billing Query].UnitConv, [Units Worked Table].Units, [Units Worked Table].AmtBilled, [Units Worked Table].AmtPaid
FROM ([Worker table] INNER JOIN (Services INNER JOIN [Units Worked Table] ON Services.ServiceID = [Units Worked Table].Service) ON [Worker table].WorkerID = [Units Worked Table].WorkerID) INNER JOIN [Rates Billing Query] ON Services.ServiceID = [Rates Billing Query].ServiceID
WHERE ((([Units Worked Table].Date)>=[Forms]![Billing Work Form]![MOStart] And ([Units Worked Table].Date)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].EffecDate)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].[Expire Date])>=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].Program)=[Forms]![Billing Work Form]![ProgChoice]))
ORDER BY [Units Worked Table].UnitsID, [Units Worked Table].Date;
Thanks, Doug
