Query to find changes from one table to the next 1

[Kallen]

I am using Access 2000.

This has been driving me crazy. I know there has to be a way to do it, I just don't know how.

Here is the scenerio. We have thousands of accounts. Each account is owned by a sales officer. In January we re-assigned a bunch of accounts to different sales officers.

I need to build a query to see which accounts were transferred to new officers. In the query I need to show the Account Number, the account name, the old officer, the new officer and their balances.

I figured the first thing I should do is build an account table. This would show the account number and account name. I may also want to have the original Sales Officer in this table.

I will be using the account table as my main source for account info. What I need to do is take monthly activity tables (that correspond with my main table) and do some kind of query that will show if there was any change of sales officer.

I am at a loss here and don't really even know where I should begin. Any suggestions would be SOOOOOO appreciated. If I can get this right it will save me a lot of work!

If I am not making myself clear (it is hard to explain), or if I should provide more info, please let me know.

Thanks in advance!

 

[r937]

yes, please provide relevant column names in existing table (the one where the accounts were re-assigned)

you said you're going to build a special new table just for the accounts that were re-assigned, and that might be a good idea, but the need is not yet established, maybe it can still be done with a query only

are you saying that the only way to find out which accounts were re-assigned is to compare December's accounts to January's?

rudy

http://r937.com/

SQL Consulting

 

[Kallen]

Thanks for the quick response.

All of the column names are the same in both tables. They are essentially the same, but contain different balance-$ info to reflect the end of each month. I name them DecBalance, JanBalance, etc.

The column names that are the same and I will be using go as follows:

OblgNum (will never change)
OblgName (will never change)
Officer (could change, it is what I am tracking)
Unit (could change)
EndingBalance (this changes monthly)

And yes, I do believe the only way I can logically find out which accounts have been re-assigned is to compare December to January. If there is a simpler way to get my end results, I would be thrilled. I have a tendency to over complicate things so any suggestions would be appreciated.

Thanks again.

 

[r937]

both tables? how many tables are involved here?

i thought there was only an activity table, with some rows for December, some rows for January...

rudy

http://r937.com/

SQL Consulting

 

[Kallen]

To start there will only be two tables. One for December and one for January. This will be an ongoing project so I will take the tables for each month and compare them to the December table to see if any accounts were re-assigned from December to the current period.

Sorry for the confusion.

 

[r937]

match the december table to the current table by account number and name

select accounts where officer is different

select Dec.OblgNum 
     , Dec.OblgName
     , Dec.Officer       as DecemberOfficer
     , Dec.Unit          as DecemberUnit
     , Dec.EndingBalance as DecemberBalanace
     , Cur.Officer       as CurrentOfficer
     , Cur.Unit          as CurrentUnit
     , Cur.EndingBalance as CurrentBalanace
  from DecemberTable as Dec
inner
  join MarchTable  as Cur
    on Dec.OblgNum = Cur.OblgNum 
   and Dec.OblgName = Cur.OblgName         
 where Dec.Officer <> Cur.Officer

rudy

http://r937.com/

SQL Consulting

 

[Kallen]

Thanks!

I will give it a try and let you know how it goes. I am on my way to a meeting and will try it after!

Thank you so much for all of your guidance.

 

[Kallen]

Thank you so much. This worked perfectly and it wasn't half as difficult as I thought it would be.

You made my week!

 

[r937]

and it's only tuesday!

thanks for the kind words