Keeping a Record of Changes

[Mae]

I use Access to track charges made on company credit
cards. When I set up the tables, I considered linking
each card with a particular user, but then decided against
that because the cards can change hands as employees are
added, terminated, moved around, etc. If I linked a
specific card to a specific user, when a card was re-
assigned and I changed the user, it would update all
records for that card and show the new user for all
previous transactions, correct? I wanted to have the
correct card-user's name listed for each transaction,
including past transactions.

.... I chose instead to have a lookup table to employee
names, and select the current user from that list. But
now I am getting tired of having to select the name for
every single transaction for every single card. With
about $50,000 per month on 14 cards with a dozen users
it's very tedious! The cards don't change hands very
often, so it is really wasteful to not have it automated,
but then again, I can't just have a bunch of inaccurate
records when they DO change hands.

Is there a way that I can link a card to it's current
user, but also keep track of who had it before? So that
when I go to enter Transaction# 119 for Card01, John
Smith's name automatically fills in, but in 2 months when
Jane Doe is carrying the card, I can change something
somewhere so that her name fills in when I enter her
transactions, but Transaction# 119 still shows John
Smith? I am open to any and all suggestions, although I
have to ask, "please be gentle" -- I am pretty good with
tables, forms, and reports, but know almost nothing about
Access programming or macros.

Thank you!!

 

[Tim Ferguson]

Is there a way that I can link a card to its current
user, but also keep track of who had it before?

Yes: either use a Max() function in the query to get the most recent
DateCardIssuedToUser record; or use

WHERE DateCardReturnedFromUser IS NULL

whichever suits your database design better.

HTH


Tim F

 

[Mae]

Thanks Tim. I'm more inclined towards the second option,
but I'm still not 100% clear. This is what it looks to me
like you're saying:

1) In my CardInfo table, add a field called
DateCardReturnedFromUser
2) When a different user begins carrying a card, enter a
date into that field

I'm with you up to there. If the DateCardReturned field
is null, then it means that the card still has the user
indicated in that record. If it is NOT null, then the
card has changed hands. Got that.

It's not specified, but I'm assuming that next I would add
a new record for that card, with the new user's name in
the User field and a blank DateCardReturned field. Is
this correct? Because if so, then I need to get a new
Primary Key. I have been using the card number, because
it will never duplicate. Easy enough, I'll switch to an
autonumber, which I'm beginning to think should always be
included in every table, whether it's used or not, because
you never know when you MIGHT need it. *S*

So then I query the CardInfo table, specifying the null
DateCardReturned field, and use that query to create a
form for entering data into my Transactions table? I have
been entering the transactions directly into the table
because I prefer the layout, and since I am the only one
using this database, I'm not worried about people screwing
up my tables. But I see that by using a form that's based
on that query of the CardInfo table, it will put the
current user into the Transactions table, while still
leaving in place the correct user for earlier
transactions. ... At least, I think I've got that
right ...

Am I picking up what you're laying down?

 

[John Vinson]

I use Access to track charges made on company credit
cards. When I set up the tables, I considered linking
each card with a particular user, but then decided against
that because the cards can change hands as employees are
added, terminated, moved around, etc.

ummm... that may have been a mistake.

If I linked a
specific card to a specific user, when a card was re-
assigned and I changed the user, it would update all
records for that card and show the new user for all
previous transactions, correct? I wanted to have the
correct card-user's name listed for each transaction,
including past transactions.

The best way IMO to handle this is to have three tables:

Employees
EmployeeID
<bio information>

Cards
CardNo
<any info about the card itself>

CardAssignment
CardNo <link to Cards>
EmployeeID <link to Employees>
EffectiveDate <when this employee got this card>
EndDate <when they relinquished it, NULL if they still have it>

You'll then be able to link transactions to CardAssignment by the card
number, selecting those records where:

TransactionDate >= EffectiveDate
AND
(TransactionDate <= EndDate OR EndDate IS NULL)

to find out the employeeID as of the time of the transaction.

 

[Tim Ferguson]

Am I picking up what you're laying down?

Not really: I don't think you have enough tables at the moment. See John
Vinson's post. You have a many-to-many relationship between People and
Cards, so you'll need a third table called AssignedTo.

People looks like
PersonID Autonumber (PK)
FName
LName
etc. etc.

Cards looks like
CardNumber Text or whatever your card number is (PK)
CurrentOutstandingAmount
DatePrinted
etc.

and AssignedTo is like this:
Person Long Integer referencing People.PersonID
Card Text referencing Cards.CardNumber
DateIssued DateTime
DateReturned DateTime
(PK made up jointly of Person, Card, DateIssued)

Now, unfortunately in Access you can't enforce a rule that says only one
AssignedTo.DateReturned can be Null for each value of Card (i.e. force a
particular card to be Returned before it's Issued again) so you have to
build the logic into the form. For that reason, Max(DateIssued) is the
safest way to get exactly one current holder.

Hope that makes a bit more sense.

Tim F