Question Use single front end to pull data from multiple copies of same backend; how?

[jwleonard]

I have multiple copies of a back end of a database that are shared throughout different offices in my organization. Each office needs to have separate databases but I have a new requirement to generate reports for the organization. The issue I will have is the Primary Key is an auto number that while obviously not duplicated in the single database; the key will be used once in each database creating duplicates when all data is merged for reporting. Is there a way to handle this situation or am I looking at a complete redesign?

 

[Lissa]

Have you tried linking the tables from the copies sitting in different offices into your db? Then maybe creating using the find duplicates query wizard to filter out dupes?

 

[jwleonard]

Lissa - Unfortunately removing duplicates won't work in this case. The database was meant to be only used in one office when it was designed. Therefore, the auto number for the primary key was sufficient at that time. When multiple offices started using it there also wasn't a problem since all data was separate. Now that we want to pull all the data into one group for reports we have a problem.
Imagine two identical databases, structure wise and you want to merge data for reporting. So both databases have a record in table1 with a primary key of 1234 and of course that links to other tables by foreign keys that relate to record 1234. So since the auto number is only unique in its own database but I need to pull data from both and keep the relationships straight.
I am hoping someone can help me with this but I've got a feeling I will be a victim of my own success on this one and be starting from scratch!

 

[Lissa]

Oh okay I understand alittle bit more... I guess a slight redesign is needed. Or alot of code to determine differences between the records and appending those records to a master table.... sorry

 

[Mike375]

Perhaps in the other data base you create another field in the tables with an ID that might be a join of the ID and say 1000, so your 1234 becomes 12341000.

Then use the new ID fields in the joined DBs while the originals continue to do their job when the DBs operate as stand alones.

You might need to rename fields in the second DB for when they are joined. In other words if the original ID field is called FieldID then you might rename it and the new field is called FieldID, on a temporary basis.

 

[DCrake]

Another work around is to create a new blank database with the structured tables that are common to all back ends.

Add an addional autonumber field and a source field. The source field will be an indicator as to which back end the data came from.

Then run an append query from Table1 in BackEnd1 into MstTable1 in you new database. Then append Table1 in BackEnd2 in MstTable1 in your new database. Repeat this for all backends. Then import any queries/reports/forms etc from the main front end and run your reports from the underlying Master tables.

Setting up the append routines may take a bit of time but once complete you could automate the whole process using a wizard.

David