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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
als315Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.