Finding and moving out old records

[Judi]

Good morning (or whatever time you are reading this...)

Thank you to everybody who has helped me so far with my database, the rest
of you, don't worry, I will have enough issues and questions for you all to
help me out...

I have a table which links to my main table. This table holds the
information for my salesmen and the ID is their sales number, which is
unique. The main table records which salesman is the District Manager and
which is the Sales Service Manager. I have about 220 records in this table,
of which, only about 110 are still working for the company in a sales
position still. The rest have either been promoted, moved, or are no longer
working for the company.

I would like to clean out this table and keep only the salesmen who have
been active in the past 2 years. Can anyone think of a way to do this, maybe
with a query? I don't want to check each salesman seperately. The main table
uses 2 salesmen per record and I need to check each name against those
positions... Some people are in my table twice because they were an SSM, but
are now DM's. They are notated as DM or SSM, so that could make it easier.

If anyone has an idea that could help me, I would truly appreciate it!!

Have a great day!

 

[Ryan]

Ok, two things. First, unless your relationship between the salesman table
and the other table has cascading updates and deletes turned on, then when
you delete the salesman from the table you will have broken references in
your table. Second, why would you want to delete the salesman? Why not just
simply add one more column in your table called Active, make it a yes/no data
type, and then in all the forms you use lookups for the salesman in, set the
look up criteria to not show unchecked Active salesman?

 

[Fred]

Recapping, you said that you want to delete salesperson who have not "been
active in the last two years". Any queries etc. will act based on
available stored data. Do you have an data in your records that
refelcts on when they were last "active"? If so, the answer will be
straightforward.

You might want to put some fields in your tables to indicate when records
were created (easy, just a date expression for the default value in the table
design) and for when the records were last modified (a little harder do
through a form, and force all changes to be through that form). Not a
panacea for issues like this, but can be very helpful.

 

[Judi]

I didn't know about the cascading references, I am going to look that up.

Second, I want to delete some because they have been gone for years, are not
coming back, and are using up sales numbers that are needed for new salesmen.
They hold them as long as possible, but rather than create new ones, TPTB
recycle old ones so that they don't get too long.

This table was not maintained well, up until I received it, and it does have
an 'active' field, but it's not correctly maintained, and it hasn't been for
a while.

I could get by with just getting that right, but I have no external
reference for said salesmen that I can compare the list to, only seperate
lists by region that for some reason don't cover all current salesmen. This
is why I want to narrow the list to people who have been active (truly
active, not just marked as active) in the past 2 years. So, either for
deletion purposes, or for update purposes, I need to figure out who is
active....

Thank you for your input, I hope I explained myself a bit better now.

 

[Judi]

They are linked to records in the main table which are dated. Each record in
the main table refers to 2 salesmen and is dated as to when it was created.

I know by now that there is no cure-all for databases that are either badly
designed or badly maintained, but I do think that I am coming to the end of
the much-needed revamp of this one that I inherited about 3 months ago.

Thank you for your help, I truly do appreciate it.

 

[Ryan]

Say you have a salesman with number 100, and you delete that salesman, now
any record in your other table orphans that record. However, when you add
another salesman with number 100, all those old records will be associated
with the new salesman. I dont know how you use your data, but that is not
good in most cases. My suggestion is this. Create a new column in your
table called SalesmanID, set its datatype to autonumber, and then make it
your primary key. This insures unique IDs for your salesmen. Keep your
current ID as an internal salesman number, that way your can have duplicate
values because the relationships would be built on your SalesmanID. Then you
could add your Active yes/no column, and when a salesman leaves, the only
maintenence is to uncheck the box.

 

[Judi]

So, if I do end up re-using a number with this set-up would it refer to the
correct person? Or are you telling me to change all the record to match up
with the new autonumber ID?

 

[Ryan]

Here is what you would do. Copy your current salesman table, then paste it
and just add a 2 at the end of the name. Now you have tables Salesman, and
Salesman2 that are exactly the same. Open Salesman2 and delete all the
records. Next add the two new columns, SalesmanID with datatype autonumber,
and Active with datatype yes/no and set the defauilt value to yes. Make
SalesmanID your primary key. Now Salesman2 has two new fields with no
records. Open Salesman table, select all records, copy, open Salesman2,
paste. Access will auto assigne the SalesmanID to each salesman. Now make a
new relationship to your other table with Salesman2 based on the SalesmanID.
From this point on, you never need to see the SalesmanID, because it is
assigned automatically. For internal purposes you can put your companys
salseman number in ID field you were already using, and it can duplicate
because the relationship is now based on the SalesmanID. I hope this makes
sense. Let me know if you need more help.

 

[Judi]

I think I get it, I will try it and see if it works...

Thank you,

Judi <><

 

[Ryan]

The design I just gave you will not work. The numbers already in your other
table are based on your current ID field and if the new SalesmanID is
different it will not work. It is the proper way to addrress this issue, but
it will orphan many records. I would reconsider keeping your current setup
though. I could walk you through some VBA code to change the ID to the
SalesmanID if you want to make your data more reliable. Otherwise, every
time you delete a salesman and re-use his number for a new salesman, every
record in your other talbe will then belong to that new salesman.

 

[Judi]

O.K. I do already have a field for "active" so that's covered already... at
this point, I just want to know what is the best way to check the database to
see what sales numbers have been used in the past 2 years (actually, since
1/1/07) and then I can go in and mark those salesmen as active and the rest
as not... I can make any other changes later, but I want to move on and not
get bogged down on this one issue.

Do you have any thoughts on that part of the equation?

 

[Ryan]

Make a query named SalesmanQuery1, add both tables. In the query only add
the date field from your other table and one of the fields that have your ID
for your salesmen. Under the date add this criteria >01/01/2007. Right
click and add grouping. That way it will only list one record per salesman.
So then you will have a list of salesman from the field you selected that
have records in the other table with dates greater than 01/01/2007. Then you
would do the same thing in another query, name it SalesmanQuery2, for the
other field that has the ID for salesman. Then you would do one more query.
You would click new query on the database window, but dont add any tables.
Now you have a query designer without any tables, right click and select sql
view. Add this SQL Statement

Select * From SalesmanQuery1
Union All
Select * From SalesmanQuery2

This will take both queries and make them one result, and show you all
active salesmen.

 

[rabina akhtar]

Good morning (or whatever time you are reading this...)

Thank you to everybody who has helped me so far with my database, the rest
of you, don't worry, I will have enough issues and questions for you all
to
help me out...

I have a table which links to my main table. This table holds the
information for my salesmen and the ID is their sales number, which is
unique. The main table records which salesman is the District Manager and
which is the Sales Service Manager. I have about 220 records in this
table,
of which, only about 110 are still working for the company in a sales
position still. The rest have either been promoted, moved, or are no
longer
working for the company.

I would like to clean out this table and keep only the salesmen who have
been active in the past 2 years. Can anyone think of a way to do this,
maybe
with a query? I don't want to check each salesman seperately. The main
table
uses 2 salesmen per record and I need to check each name against those
positions... Some people are in my table twice because they were an SSM,
but
are now DM's. They are notated as DM or SSM, so that could make it easier.

If anyone has an idea that could help me, I would truly appreciate it!!

Have a great day!