Structure for using input forms to main database

[ptaylor-west]

I have a single table for storing the main data, this is fed by input from 3 forms. I have been asked if the forms can be used independantly and remotely as input forms.

My solution was to simply send out a database with just one form and created an append query so that when it is returned with completed information it would simply append the database to the main database. In itself this works fine however, what is happening, of course, is that when I get the other forms back I end up with triplicate records, that is instead of the information from the 3 forms being stored as one record it is now split over 3 records.

I have to be careful in what I do as the original database has been running for over 6 months now and has a lot of stored data and because of established queries/calculations/reports the last thing I want to do is change or split the main data table.

Is there anyway I can get the three records to concantenate on one line. The three forms all have separate fields as they were taken from the original database.

 

[KenHigg]

I never used it, but isn't this what the 'Replication' feature in Access does?

Ken

 

[ptaylor-west]

I have used synchronising replicas before but that is for direct copies of the main database which I don't want to release to third parties which is why I came up with the 3 individual input forms to collate information.

Unless it is possible to create a replica with minimal information - I can try that, but merging the data may be a more practical solution.

 

[ptaylor-west]

Thought so, you can create a partial replication but this is just a filter, you can't take out any tables etc form a replica. Therefoer, this is not the route unfortunately as the replication would have been ideal.

There is some commercial value in the database so I cannot afford to let the structure out even if it was read-only.

 

[neileg]

Presumably, there's a key identifyer that allows you to link the info from the three sources, job number or something like that? Could you create three new tables that correspond to the three sets of input? You could then use an append query to combine the data from the three tables and insert it into your main table.

 

[ptaylor-west]

Good idea, however the data will come back at differing times. The 3 forms I'm using are surveys on salespeople and their customers. So a telephone survey is to be outsourced to one company, a mystery shop survey by another and a cusotmer follow up survey by another.

It would be fine to append the tables from each of these surveys into a separate table in the main database and then I suppose I append all the 3 tables into the main table but that is providing I receive all 3 back at the same time and that they are completed for the same people, but this is highly unlikely.

I will look at it though, thanks.

 

[ptaylor-west]

This will only work if you have all the information at the same time, however it will not append data on a piecemeal basis, it simply creates a new record.

Any ideas anyone?

 

[neileg]

Consider appending the data when the first bit is returned. After that it will need to be updated.

 

[ptaylor-west]

Thanks for your suggestion, it appears to be the right solution.