How to create a query that finds changes in amounts?

Hi everyone,

I currently have an Access query that audits a change log from our mainframe system.  I've created a table that lists department numbers in a row and a corresponding payment in other columns labeled week 1 through week 10.  What I need to do is show where the dollar amount changes based on the department number.

So if someone was in dept 3 and they paid 30.00 in week 4 but then in week 10 they paid 40.00.  I want to show that change.  
LVL 1
Anthony6890Asked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Test this sample (open qryPivot as pivot table).
You can add dates range to qry2 or filter in pivot table.
Changes are calculated relatively base - in this example first week of data.
Add <> 0 criteria and you will see only dates with changes

DBchanges.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Show the change as what?

Please post an example of the *exact* results you are looking for.
0
 
Anthony6890Author Commented:
I want to see those changes in the query itself.  So I want it to output the two rows that display the change in value.  

I.e.

ID Number       Dept           Date          Amt1
12347                3                11111           30
12347                3                11110           25
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jeffrey CoachmanMIS LiasonCommented:
I am still not understanding something...

If there are 3 records with 25 and then the 25 changes to 30 on the 4th record, the change should be visible...

ID Number       Dept           Date          Amt1
12347                3                11111           30  <-Change
12347                3                11110           25
12347                3                11109           25
12347                3                11108           25
12347                3                11107           31  <--Change
12347                3                11106           20
12347                3                11105           20

I don't see the need for any special query to "Show" the change.

Perhaps I am misunderstanding something simple...
0
 
Anthony6890Author Commented:
Sorry, I'm not explaining this correctly.  The file I'm working, which has the history of Amt1, lists every change from square 1.  In your example, I only want to see your first row and second row only.  I don't want the rest of them... hope that helps you.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Oh, OK, much clearer...
;-)

I am sure there is a way...


Pleas wait for another expert to chime in.

Also you may want to click the Request Attention button and ask that this Q be added to the SQL zone
0
 
Jeffrey CoachmanMIS LiasonCommented:
You can do this with a recordset too...

But wait, ...In my example I would think that you would want to see only the "unique change" values, 20,31,25,30
...Not just the 25 and the thirty as you stated above...?
0
 
Anthony6890Author Commented:
I just had to manipulate it to work in my db.  Thanks for your help.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks als315, I knew someone would be along with a query solution...

(SQL is not my strong point)

;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.