Unique References in Multiple copies of same database

[Guest]

Hi all

I have created an offline version of our expenses system for our field
staff. They all use laptops which are never connected to the network. They
will be sent a database containing two tables as transfer files. One will
contain the Itinerary data we send them and the other will contain the
Expenses data which they send us (related to the Itinerary table which has a
unique autonumber). When we import their expenses back in to the live system
in the office we need to ensure the data is not being duplicated. The key on
the Live Expenses table is an autonumber (ExpID), no other key can be created
as other duplication could be valid. Basically I think what I need is an
additional unique reference against their expense record in the offline
system which is transferred to our live system, that way the system can check
it doesn't already exist before the import.

Hope this is making sense?

Just want to ask the best way of doing this - maybe on 'before update'
concatenate the PurchasedBy (unique person ID) AND Autonumber (ExpID) as a
unique reference and store in a new field ExpRef ?

Thanks in advance for any help.
Sue

 

[John Vinson]

Hi all

I have created an offline version of our expenses system for our field
staff. They all use laptops which are never connected to the network. They
will be sent a database containing two tables as transfer files. One will
contain the Itinerary data we send them and the other will contain the
Expenses data which they send us (related to the Itinerary table which has a
unique autonumber). When we import their expenses back in to the live system
in the office we need to ensure the data is not being duplicated. The key on
the Live Expenses table is an autonumber (ExpID), no other key can be created
as other duplication could be valid. Basically I think what I need is an
additional unique reference against their expense record in the offline
system which is transferred to our live system, that way the system can check
it doesn't already exist before the import.

The recommended way to do this is to use "Replication". This is
unfortunately rather complex to implement, but this requirement is
exactly what it's designed to do.

Get the Microsoft Whitepaper on replication, and study it carefully.
There's a microsoft.public.access.replication newsgroup with good
advice on the subject, and MichKa (www.trigeminal.com) is the
recognized master of the subject.

If you don't want to use replication you can cut down on the
duplication by setting the ExpID's Autonumber property to Random
rather than Sequential, or use a GUID autonumber rather than an
Integer. This won't protect against conflicts in other areas, though.

John W. Vinson[MVP]

 

[Guest]

Hi thanks I had already looked at replication but it did look quite
complicated and not sure it is required for this instance? The users laptop
will never be connected to a network to resync. They will send me data via
email to be imported.

They will only create new expenses and when I import back into my live
system I will just generate a new ExpID and not use theirs, as it doesn't
link to anything else there is no problem. The Itinerary links to the
Expenses on a one to many relationship but I am not allowing them to amend
the Itinerary so their expense record will always contain an ItineraryID
which links to an existing record in the live system.

I just want some method of making sure that their expenses claims are not
submitted more than once by using a unique reference different from ExpID.

Cheers
Sue

 

[John Vinson]

Hi thanks I had already looked at replication but it did look quite
complicated and not sure it is required for this instance? The users laptop
will never be connected to a network to resync. They will send me data via
email to be imported.

They will only create new expenses and when I import back into my live
system I will just generate a new ExpID and not use theirs, as it doesn't
link to anything else there is no problem. The Itinerary links to the
Expenses on a one to many relationship but I am not allowing them to amend
the Itinerary so their expense record will always contain an ItineraryID
which links to an existing record in the live system.

I just want some method of making sure that their expenses claims are not
submitted more than once by using a unique reference different from ExpID.

ok... sounds like overkill to use Replication then.

What "unique reference" comes to mind? What information exists *in the
EMail message* which would flag a record as a duplicate (in a way that
Access could automatically detect)?

I'd guess the ItineraryID and the payee... but even that is iffy; I've
filed (legitimate!!) expense reports where a hotel gave me a second
bill for (IIRC) telephone service after I'd already submitted the
lodging bill, so that would be two bills on the same payee. OTOH, if
you're trying to catch fraudulent (rather than accidental) claims, it
might be difficult for even a human to detect.

John W. Vinson[MVP]

 

[Guest]

Yes I can't use Itinerary ID as the way the system works is that all expenses
are recorded against an activity (itinerary) and sent in weekly. In the
following subsequent week(s) they may do some more work on the same activity
hence the same ItineraryID.

Each record they enter though has a unique ExpID and a unique PurchasedBy
(person ID). Should I create a new field ExpRef and store the PurchasedBy &
ExpID as a unique reference? At least this way it will capture the same
record being submitted more than once. I am trying to stop accidental rather
than fraudulent duplication. Obviously if they create a new record for the
same expense this is hard to capture but would be picked up from the checking
of the reports probably.

Thanks
Sue

 

[John Vinson]

Each record they enter though has a unique ExpID and a unique PurchasedBy
(person ID). Should I create a new field ExpRef and store the PurchasedBy &
ExpID as a unique reference?

If you're storing the ExpID and the PersonID, that's all you need:
there is CERTAINLY no need to create a reudundant field! A Unique
Index can be based on up to *ten* fields. Just select the Indexes tool
in table design; type some distinctive index name in the left column;
and type ExpID in the adjacent cell in the right column, and PersonID
in the cell beneath that one:

UniqueItem ExpID
PersonID

Check the Unique checkbox and you'll have protection against entering
the same ExpID for the same PersonID a second time.

John W. Vinson[MVP]

 

[Guest]

Sorry John you are either not reading my posts correctly or you have
misunderstood them. The EXPID is not retained when the import to the live
Expenses table is performed, a new one is assigned. I want a unique
reference to check on import that the combination of ExpID and PurchasedBy
in the external system does not already exist in the live system. I have
created ExpRef for this purpose and all working ok thanks.

Sue