Avatar of 911bob
911bobFlag for United States of America

asked on 

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?
Microsoft AccessMicrosoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
911bob
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of 911bob
911bob
Flag of United States of America image

ASKER

OK Thanks. It is a solution under development so I am actually back feeding old historical data, but I can probably make the adjustments in Excel easier.

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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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 & "#)")
Avatar of Luke Chung
Luke Chung
Flag of United States of America image

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.
Avatar of 911bob
911bob
Flag of United States of America image

ASKER

Thanks
Microsoft Access
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo