Access Database
Migrating from an application that is using Excel Macros to manipulate and join data to MS Access.
I will be importing daily labor data into a table and all the data is there except for the Managers Pay.
I have a second table that has the pay rate. and the date the pay rates take effect.
Is it possible to have an update query to find the Manager & pay rate for the date listed in the Labor Data table?
The dates will probably have to be in ranges.. like at 1/1/2016 it was increased to $20.00/hr, and then on 1.1.2017 it was increased to $21.00/hr
Is this possible, or am I better doing this in excel and then import everything into access?
I will be importing daily labor data into a table and all the data is there except for the Managers Pay.
I have a second table that has the pay rate. and the date the pay rates take effect.
Is it possible to have an update query to find the Manager & pay rate for the date listed in the Labor Data table?
The dates will probably have to be in ranges.. like at 1/1/2016 it was increased to $20.00/hr, and then on 1.1.2017 it was increased to $21.00/hr
Is this possible, or am I better doing this in excel and then import everything into access?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What is your table structure?
If you have something that includes the managers name, a date and a rate, you could do something like:
SELECT T.ManagerID, Rate, Min(PayDate) as FromDate
FROM yourTable
GROUP BY ManagerID, Rate
ORDER BY Mn(PayDate)
There is always the chance that the rate could go back down after it goes up, but would it be exactly the same as on the way up? Probably not. That would get you the first part of what you need, Then you could create an update query which updates the ThruDate column with the minimum FromDate greater than the current FROM date.
Update ManagerPay
SET ThruDate = DMIN("FromDate", "ManagersPay", "([ManagerID] = " & ManagerPay.ManagerID & ") AND ([FromDate] > #" & ManagerPay.FromDate & "#)")
If you have something that includes the managers name, a date and a rate, you could do something like:
SELECT T.ManagerID, Rate, Min(PayDate) as FromDate
FROM yourTable
GROUP BY ManagerID, Rate
ORDER BY Mn(PayDate)
There is always the chance that the rate could go back down after it goes up, but would it be exactly the same as on the way up? Probably not. That would get you the first part of what you need, Then you could create an update query which updates the ThruDate column with the minimum FromDate greater than the current FROM date.
Update ManagerPay
SET ThruDate = DMIN("FromDate", "ManagersPay", "([ManagerID] = " & ManagerPay.ManagerID & ") AND ([FromDate] > #" & ManagerPay.FromDate & "#)")
Are you sure you need to save the data in the tables rather than just calculating it when you need to in a query?
Unlike Excel, Access lets you avoid saving unnecessary data, especially if you can join two tables to get the numbers you want for every detail record.
Unlike Excel, Access lets you avoid saving unnecessary data, especially if you can join two tables to get the numbers you want for every detail record.
ASKER
Thanks
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
In proving the Access solution I want to be able to tie out actual values they have gotten before in Excel.. makes it more believable
Thanks