Design Issue

[ddrew]

Im having a bit of trouble with the design of my DB.

The DB is a dog training DB. Each dog has a profile with its breed, Name etc and a DogID (this is an autonumber and is the Primary key)

Each dog undergoes training each day and that is recorded on a subform. The subform holds a TrainingID (this is an autonumber and is the Primary key) and a DogID to link it to the dog of course.

Now the problem, I am building the DB for a dog trainer who takes dogs into his kennel from clients to train them. His clients will also have a copy of the DB, holdinginformation about their dogs and only their dogs. He wants to be able to email them the dogs record of training on a daily basis.

I have created an export function which exports a record to a spreadsheet. The problem comes when the client imports, the reason their is a problem is that the dogID is likely to be different. i.e. he may have 50 dogs in for training with DogIDs of 1-50, his client may only have 2 dogs. So Casper who is dog 20 on his DB may only ge DogID 2 on his clients.

So my question is, how can I import a record and link it to a dog when the Primary Keys dont match.

I cann export the record without the DogID but I still need to be able to link it! :banghead:

Hope that makes sense.

 

[burrina]

Each Dog rather than just the PK should be unique. You would need to check for duplicates and also do a update and or append query to sync the db's.
HTH

 

[ddrew]

Each Dog rather than just the PK should be unique. You would need to check for duplicates and also do a update and or append query to sync the db's.
HTH

o ou mean each dogs name should be unique as thats not always going to be possible. The person is a trainer and he has dogs come to him for training from different people, so people could call there dogs the same name.

 

[burrina]

NO, the Dog could have a unique Identifier like dRover101. A 10 digit field in your table set to No Duplicates. You could also perform a DLookup for the TrainingID and Owners first and last initials or something as an example. There is NO substitute for a good pre-search.
Owner Tom Jones has several Dogs, some named the same! So, use a combo of name,dog tag!

HTH

 

[ddrew]

NO, the Dog could have a unique Identifier like dRover101. A 10 digit field in your table set to No Duplicates. You could also perform a DLookup for the TrainingID and Owners first and last initials or something as an example. There is NO substitute for a good pre-search.
Owner Tom Jones has several Dogs, some named the same! So, use a combo of name,dog tag!

HTH

Thanks for your reply and sorry for getting back to you so late.

The problem is that the dog may already be in the clients DB with his unique Identifier, but not in the trainers. The trainer then may crate the dog in his DB and put a different uniique identifier with it. I think this is always going to be a problem, its going to mean some kind of liason between the client and the trainer, which I was hoping to avoid.

Either way thanks for your help.

 

[burrina]

NP. Maybe Sharepoint would be a good idea. Either way, it can be managed by some thought and code. That's why a BeforeUpdateEvent is used.

Good Luck!