Link to home
Start Free TrialLog in
Avatar of Metalteck
MetalteckFlag for United States of America

asked on

New and Previous Values in a Query

In my report, I have to have 2 fields, one that's called previous department and the other is called new department for an employee.
That database stores this value in a field called A_Value.
Ex:
Emp       Dept
12345    91204
12345    93000

My question is how can I have all this information displayed on 1 line?
Expected result:

Emp     Old Dept      New Dept
12345  93000           91204


Thanks for all you help guys
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Metalteck

ASKER

Not necessarily.  There are 3 fields that can help determine it.
Effect_Date and Sequence number help determine it.
Effect date is a date time field and sequence number is a number field.

Ex:               Seq
6/12/2016  1
6/12/2016  2
ok, so in your original table, you would have these fields:

  • Sequence_number
  • Emp
  • Dept
  • Effect_Date

perhaps the few last questions to ask before suggest a solution...

is that possible for an employee to have more than 2 records in this table?
do you only want to compare the last 2 records in your table per employee?
Yes, an employee can have multiple records in this table.
Ideally, I would want to compare the most recent effect date and the corresponding sequence number.
Any suggestions on how to resolve this with my last answer?
can you provide some sample data here? perhaps a screenshot wil do.

and what's the database you're using?
Question no longer needed.