?
Solved

MS Access Sql Queto associate values from different rows

Posted on 2016-09-15
3
Medium Priority
?
76 Views
Last Modified: 2016-09-26
Experts,

I'm converting Payroll data from our old system to a new one. In the old system, when an employee moved to a new state, the State tax was recorded in the new state, but the YTD State Tax balance from the old state was coded as Previous State YTD tax (PRVST). In the new system, I need to map the PRVST to the state that the YTD balance was actually in, so in this case, to ST1MD


Old System                                                                                     Maps To in New System:
DateEnd           EmpID     Tax Code        Curr Tax    YTD Tax      Tax Code
8/31/2015        05961        ST1MD           $500        $3500            MD
9/15/2015        05961        ST1VA             $500        $ 500             VA  
9/15/2015        05961        PRVST                  $0       $3500            MD (How do I associate this $3500 with MD?)

Attached is some example data. There's about 870,000 lines on this table, but I think there are only about 250 PRVST codes

Thanks in advance
EE-Question-PRVST.xlsx
0
Comment
Question by:Ron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
John Tsioumpris earned 2000 total points
ID: 41801209
Well i have some kind of solution but i am not sure if this what you want.
Take a look at the attached accdb(zipped) -- Access 2013...if you have earlier version tell me to convert it.
I have imported your sheet. and then i have found the entries where previous tax is 0 and the amount of current tax(probably i am wrong about the definitions)
Then i get the entry from the previous state according to the amount and finally i update the data to fill with the previous state---> take a look at table FinalTest....
Database5.zip
0
 

Author Comment

by:Ron
ID: 41806481
That works. Thanks
0
 

Author Closing Comment

by:Ron
ID: 41816690
thanks
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question