Unique reference

[hughess7]

Hi all

We use Access as a standalone system out in the field, each auditor has
their own copy (clone) of the database and we send them small mdb 'transfer'
files containing a subset of data they need to work with (we hold the master
data in huge mdb files at the office). Once they have audited they send this
back to us and we import the results, which will be a cutdown number of the
records we sent them initially.

The key for the related tables was Country, Dealer, ClaimNo, ReviewDate,
AuditNumber but I want to simplify this to one unique ref [AuditClaimRef]. I
can't use an Autonumber as the data is deleted and appended each time, to
Claim Data in the transfer mdb, and we claim the data for all of them.

I was thinking of having a new table that contains just one field which
holds a count. Every time data is produced in [Claim Data] this amount would
increase by the number of records claimed, and each row in [Claim Data] would
be assigned a number sequentially from within this range (old to new number).
Would this work, should I be doing this differently?

Sorry if the above is not clear, I know using Access in this manner is not
the best way of doing this, and we should move to a web based system, but
this is what we have to work with for now...

Hope it makes sense!

Thanks in advance for any help.
Sue

 

[Dorian]

Simplicity is the way to go. Your solution seems overly complex. What is the
purpose of this key? Do you even need it at all?
Why are they returning only a 'cut down' number of records? Why not just
send them the records they will be returning? How are you going to assign
keys to already existing records?
Is there something inherent in each record which determines which auditor it
belongs to or is it a completely arbitrary decision who gets what?
I guess I have more questions than answers
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[hughess7]

The key forms part of the one to many relationship for other tables eg ONE
claim (Claim Data) can have MANY issues (Claim Data Issues) so instead of
using a key made up of four fields I am trying to get it to just have the one
[AuditClaimRef] ie AuditClaimRef will have many IssueCodes assigned to it in
Claim Data Issues. This is customer data we work with that we are sent on a
monthly basis, the auditor tells us which dealer they are going to and we
send them the last three years worth of data for that particular dealer. It
is an audit, they choose which claims to audit on the day depending on set
criteria etc and how many they do depends on the time taken to audit each
claim - we go for quality not quantity. They may only get around to looking
at 10 claims out of at least 1000 etc. We only need the 10 claims back with
the results stored in relational tables eg Claim Data Issues.

I could explain more but it would probably just confuse you further unless
you know the ins and outs of our business methods etc ;-)... Don't worry
about the working logic of things like that, as I said it isn't the best way
of doing things but it does work. I just want to simplify the table
structures to make developing it further easier... the complication lies with
the fact that all the auditors database systems are standalone, not linked to
each other or the master back at the office. When we claim the data it is
stored in a temporary table which gets overwritten next time someone else
claims some more data, so an autonumber is not an option.

I may just stick to the old key which doesn't require any unique numbers, it
just seems more complicated for a primary / foreign key scenario...

Sue

Simplicity is the way to go. Your solution seems overly complex. What is the
purpose of this key? Do you even need it at all?
Why are they returning only a 'cut down' number of records? Why not just
send them the records they will be returning? How are you going to assign
keys to already existing records?
Is there something inherent in each record which determines which auditor it
belongs to or is it a completely arbitrary decision who gets what?
I guess I have more questions than answers
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Hi all

We use Access as a standalone system out in the field, each auditor has
their own copy (clone) of the database and we send them small mdb 'transfer'
files containing a subset of data they need to work with (we hold the master
data in huge mdb files at the office). Once they have audited they send this
back to us and we import the results, which will be a cutdown number of the
records we sent them initially.

The key for the related tables was Country, Dealer, ClaimNo, ReviewDate,
AuditNumber but I want to simplify this to one unique ref [AuditClaimRef]. I
can't use an Autonumber as the data is deleted and appended each time, to
Claim Data in the transfer mdb, and we claim the data for all of them.

I was thinking of having a new table that contains just one field which
holds a count. Every time data is produced in [Claim Data] this amount would
increase by the number of records claimed, and each row in [Claim Data] would
be assigned a number sequentially from within this range (old to new number).
Would this work, should I be doing this differently?

Sorry if the above is not clear, I know using Access in this manner is not
the best way of doing this, and we should move to a web based system, but
this is what we have to work with for now...

Hope it makes sense!

Thanks in advance for any help.
Sue

 

[Mark Andrews]

I do a very similar thing (having seperate databases at each office that get
merged into a central database) for a client I have in Haiti. In Haiti it's
difficult to get some of the normal networking senerios. However Skype
works well for communication (never call Haiti from the states over normal
long distance).

We use a method that the office clicks a button to generate a CSV file which
gets emailed to the central office, but they send the entire mdb back out to
all the offices every month.

I'm not sure I understand exactly what you are doing but in my system we use
autonumbers with "tblgrants" being the main table. If the office adds new
grants
and related records an autonumber is used but when it gets merged in to the
central database and these records and related records get added the
autonumber could change on all the records. Then you send them back a new
copy of the master (or subset of the master). NO changes between when
offices send in data and they get the new database.

So the logic on the merge into the master involves importing into temp
tables and some logic to change some keys around.
The logic to distribute is easy (well we send the entire db, but you would
just create a db with a subset of the records as you do now)

Perhaps that will shed some light or perhaps it was no help at all and I
missed something?

I don't think your logic for key creation is the way to go (at first
glance).

HTH,
Mark
RPT Software
http://www.rptsoftware.com

Hi all

We use Access as a standalone system out in the field, each auditor has
their own copy (clone) of the database and we send them small mdb
'transfer'
files containing a subset of data they need to work with (we hold the
master
data in huge mdb files at the office). Once they have audited they send
this
back to us and we import the results, which will be a cutdown number of
the
records we sent them initially.

The key for the related tables was Country, Dealer, ClaimNo, ReviewDate,
AuditNumber but I want to simplify this to one unique ref [AuditClaimRef].
I
can't use an Autonumber as the data is deleted and appended each time, to
Claim Data in the transfer mdb, and we claim the data for all of them.

I was thinking of having a new table that contains just one field which
holds a count. Every time data is produced in [Claim Data] this amount
would
increase by the number of records claimed, and each row in [Claim Data]
would
be assigned a number sequentially from within this range (old to new
number).
Would this work, should I be doing this differently?

Sorry if the above is not clear, I know using Access in this manner is not
the best way of doing this, and we should move to a web based system, but
this is what we have to work with for now...

Hope it makes sense!

Thanks in advance for any help.
Sue

 

[Mark Andrews]

My other thought is it sounds like you are trying to create a key similar to
a GUID in SQL server. Try searching on
different way to generate a GUID in Access.

Mark

 

[hughess7]

Yes I guess I am trying to create a GUID. Not against the original data but
against the subset created which is sent to the auditors, as the same claims
can be sent time and time again depending on how often the dealer is audited.
I thought it would be too hard to explain what I am trying to do, it is kind
of similar to what you do Mark. I think maybe sticking with the old method we
have used for years, which works, might be best!

Thanks anyway...

My other thought is it sounds like you are trying to create a key similar to
a GUID in SQL server. Try searching on
different way to generate a GUID in Access.

Mark

Hi all

We use Access as a standalone system out in the field, each auditor has
their own copy (clone) of the database and we send them small mdb
'transfer'
files containing a subset of data they need to work with (we hold the
master
data in huge mdb files at the office). Once they have audited they send
this
back to us and we import the results, which will be a cutdown number of
the
records we sent them initially.

The key for the related tables was Country, Dealer, ClaimNo, ReviewDate,
AuditNumber but I want to simplify this to one unique ref [AuditClaimRef].
I
can't use an Autonumber as the data is deleted and appended each time, to
Claim Data in the transfer mdb, and we claim the data for all of them.

I was thinking of having a new table that contains just one field which
holds a count. Every time data is produced in [Claim Data] this amount
would
increase by the number of records claimed, and each row in [Claim Data]
would
be assigned a number sequentially from within this range (old to new
number).
Would this work, should I be doing this differently?

Sorry if the above is not clear, I know using Access in this manner is not
the best way of doing this, and we should move to a web based system, but
this is what we have to work with for now...

Hope it makes sense!

Thanks in advance for any help.
Sue