J
Julie
I have a table I'll call Table1 that has fields "Staff ID", "Effective Date",
and "Wage." If the table is in ascending order by "Staff ID" then "Effective
Date", I want to create a query that adds an "End Date" that would be the day
before the "Effective Date" in the next record, but making the last record
for each "Staff ID" Date()+1. Then start again for the change in the value
of "Staff ID."
Here's what I have so far, but I don't know what to do about the change in
value of "Staff ID.":
SELECT [Tabel1].[Staff ID], [Table1].[Effective Date] AS DateStart,
[Table1].[Wage], nz(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "#"), Date()+1) AS DateEnd
FROM [Table1]
ORDER BY [Table1].[Staff ID], [Table1].[Effective Date];
Can this be done in an SQL query?
Thank you!
and "Wage." If the table is in ascending order by "Staff ID" then "Effective
Date", I want to create a query that adds an "End Date" that would be the day
before the "Effective Date" in the next record, but making the last record
for each "Staff ID" Date()+1. Then start again for the change in the value
of "Staff ID."
Here's what I have so far, but I don't know what to do about the change in
value of "Staff ID.":
SELECT [Tabel1].[Staff ID], [Table1].[Effective Date] AS DateStart,
[Table1].[Wage], nz(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "#"), Date()+1) AS DateEnd
FROM [Table1]
ORDER BY [Table1].[Staff ID], [Table1].[Effective Date];
Can this be done in an SQL query?
Thank you!