add end dates to query by start date

[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!

 

[John Spencer]

IF you just want to do this in a query (no update) then try the following. It
assumes that Staff Id is a text field

SELECT [Tabel1].[Staff ID]
, [Table1].[Effective Date] AS DateStart
, [Table1].[Wage]
, NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND StaffID =""" & [Staff ID] & """"), Date()+1) AS
DateEnd
FROM [Table1]
ORDER BY [Table1].[Staff ID], [Table1].[Effective Date];

If Staff Id is a number field, then
NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND StaffID =" & [Staff ID]), Date()+1) AS DateEnd

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Julie]

John:

Thanks so much for your reply. I have tried the code you suggested ([Staff
ID] is a number field). The query runs, however, I get "#Error" in each of
the DateEnd cells. I can't figure out what might be wrong.
--
Julie

IF you just want to do this in a query (no update) then try the following. It
assumes that Staff Id is a text field

SELECT [Tabel1].[Staff ID]
, [Table1].[Effective Date] AS DateStart
, [Table1].[Wage]
, NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND StaffID =""" & [Staff ID] & """"), Date()+1) AS
DateEnd
FROM [Table1]
ORDER BY [Table1].[Staff ID], [Table1].[Effective Date];

If Staff Id is a number field, then
NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND StaffID =" & [Staff ID]), Date()+1) AS DateEnd

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

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!

 

[John Spencer]

I typed StaffID in one place where it should have been [Staff ID]. I
automatically took out the space, since I try to never use spaces in table and
field names. Every once in awhile, I slip.

Try this and see if it works.

NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND [Staff ID] =" & [Staff ID]), Date()+1) AS DateEnd

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Julie]

I wondered about that..... I just now tried it and it worked perfectly!

Thank you SO MUCH for your help.
--
Julie

I typed StaffID in one place where it should have been [Staff ID]. I
automatically took out the space, since I try to never use spaces in table and
field names. Every once in awhile, I slip.

Try this and see if it works.

NZ(DMin("[Effective Date]","[Table1]","[Effective Date] >#"
& [Effective Date] & "# AND [Staff ID] =" & [Staff ID]), Date()+1) AS DateEnd

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John:

Thanks so much for your reply. I have tried the code you suggested ([Staff
ID] is a number field). The query runs, however, I get "#Error" in each of
the DateEnd cells. I can't figure out what might be wrong.