Need to merge everchanging databases into one on access......

[Huckleberry]

Hi, I'm totally new to access, so please forgive my limited knowledge. I have to coordinate a dbase in the US, one in the UK, and one in the KSA. I have multiple documents that may, or may not, be in each of the three dbases. I need to merge all three, and maintain them so that the document additions, as well as any changes associated with that document, (i.e. "revisions"), are updated. Having looked at access and having read through some of the Access forum questions, I understand that it can do that, but after having identified the information from the dbases that will go into access, I'm trying to establish a consistant process for maintaining and updating it. Can I export my information from each of the dbases, into an excel spreasheet, (1 for US, 1 for UK, and 1 for SA), and have the access dbase search for the document number, compare the data following the document number, and if it's the same ignore it, and if it's different, update it?



Is that possible? (Between the three dbases, we're talking of approximately 80,000 documents.)

I'm trying to muddle through this and get access and excel to do as much of this as possible, as it's a very tedious and time consuming process to manually check each one, one or two times a week.

Any information, suggestions, or condolenses would be appreciated!

[June7]

What do you mean by 'multiple documents that may, or may not, be in each of the three dbases'? Are these documents embedded in attachment field?

What do you mean by 'compare the data' - what is the 'data' - a name, a number, a date, an attached file?

[Huckleberry]

Hi June,

well.....lol....where to begin.....

What do you mean by 'multiple documents that may, or may not, be in each of the three dbases'?

This means that the document does not appear in all of the databases, in some cases it's only in one, and in the case of a different document, it might be in three of them. I work for an engineering firm. Some of the engineering work is done in the clients home nation of the UK, and some of the engineering and support work is done in the US, but the physical project is being built in the Middle East. So depending upon what work is being done, affects which documents are in which database. Does that make sense? :-)

Are these documents embedded in attachment field?

Each of our firms has their own different networks, and none of them are compatible with one another, so each firm is doing their work on their own networks. I have been given the task of bringing all of the data reflected in each network up to the most recent document. So if document A in UK, is revision C, but is a revision A in the US, and a revision B, in the KSA, I'm expected to identify these confilicts, and then inform the appropriate groups that their version of the document can be brought up to the most recent revision. (if it exists in their networks, in this case the US would need action, and the KSA would need action). I'm currently running a report from each of the databases via excel, (three different systems, which are each able to export the information into an Excel format), I was hoping to combine the Excel data within Access, to be able do the analysis on a weekly basis. I was utilizing just Excel, but it is just so slow, and once the formulas and data macros are processed and saved, I have a 45-50mb size document that takes literally hours to process. I was hoping to be able take the Master Worksheet I am currently utilizing in Excel, and import it into a table in Access. Then go into each of the networks, export the excel report from each database, take that data, and compare it to the "established" Master Worksheet Database that would exist in Access, (each week), have it compare the data sets from each network, and if the data exists in access, exclude it. If the data is mismatch, or does not exist in any of the categories used to identify it, (via a form, query or report), I can then make sure all of the networks are as close to updated to one another as possible. Does any of that make sense? (again)? LOL

(Here's an example of my Excel data worksheet)

27653-SYS-01	SYSTEM ISOMETRIC JACKETED PIPING - CARBON STEEL	RG01	M02	D015	8	3/2/2012	JPI	I	DOCUMENT EXISTS IN JPI & B1, BUT TITLES ARE DIFFERENT, AND REVISIONS ARE DIFFERENT.
27653-SYS-01	JACKETED TRANSFER LINE ISOMETRIC FROM FINISHER 24X440002 TO DIE 24Z440000	RG01	M02	D015	7	4/17/2012	B1	I	DOCUMENT EXISTS IN JPI & B1, BUT TITLES ARE DIFFERENT, AND REVISIONS ARE DIFFERENT.
27653-SYS-02	SYSTEM ISOMETRIC JACKETED PIPING - CARBON STEEL	RG01	M02	D015	8	3/2/2012	JPI	I	DOCUMENT EXISTS IN JPI & B1, BUT TITLES ARE DIFFERENT, AND REVISIONS ARE DIFFERENT.
27653-SYS-02	JACKETED TRANSFER LINE ISOMETRIC FROM FINISHER 24X460002 TO DIE 24Z46000	RG01	M02	D015	7	4/17/2012	B1	I	DOCUMENT EXISTS IN JPI & B1, BUT TITLES ARE DIFFERENT, AND REVISIONS ARE DIFFERENT.
50S865008 / 011	Hot Side Chilled Water Sump / Cold Side Chilled Water Sump	RG12	D01	D930	A4	9/1/2011	JPI	X	CORRECT - BUT OMIT FROM MTR - NO B STATUS DOCUMENT EXISTS.
58KG28YY-RG01-M02-D030-00001	Process Structure Design Basis CP Structure	RG01	M02	D030	A	3/12/2011	JPI	X	CORRECT - BUT OMIT FROM MTR - NO B STATUS DOCUMENT EXISTS.
58KG8222-0001-001	DCS Block Diagram	General	F02	E030	C	6/9/2011	JPI	X	CORRECT - BUT OMIT FROM MTR - NO B STATUS DOCUMENT EXISTS.
58KG8222-0001-003	BLOCK DIAGRAM - WIRING PHILOSOPHY SH-1_ FOUNDATION FIELDBUS, MCC WIRING SH-2_ DCS WIRING PHILOSOPHY SH-3_ GEN. AREA WIRING FIRE ALARM INTERFACE SH-4_ PLC WIRING PHILOSOPHY	General	F02	E030	C	6/9/2011	JPI	X	CORRECT - BUT OMIT FROM MTR - NO B STATUS DOCUMENT EXISTS.
58KG8222-0001-006	BLOCK DIAGRAM - WIRING PHILOSOPHY DCS WIRING INTERFACE	General	F02	D055	DEL	8/6/2012	JPI	D	DELETED DOCUMENT.

I do so hope that I am able to make this make sense. LOL

Thanks for the help!

[June7]

You don't show column heads for the worksheet. Which field indicates the version?

You can build queries that will compare values in corresponding fields of two tables, as long as there is a unique ID field common to both tables to create a JOIN on. An expression in the query could be:

IIf(Table1.fieldname = Table2.fieldname, "Match", "NoMatch")

So the documents are NOT embedded in attachment field? You just have data about the document, not the document itself?

[Huckleberry]

Sorry, the column titles are as follows, from Left to Right;

Document Number
Title
Group
Discipline
Type
Revision
Revision Date
Dbase Source
Status
Comments

No, the documents themselves are within each of our networks, and this is comparative data from each of our networks, so we can understand what issues we are currently having on the document control side.

[June7]

The Revision field is a mix of alpha/numeric - some use numbers, some use letters, some use combination. It's the combinations that really complicate because A11 would sort (ascending order) before A4, even though 11 might indicate the later version. This is a mess.

Then there is a RevisionDate. Could the date be relied on to determine the most up-to-date version? Would version 7 of 27653-SYS-01 be the up-to-date version as opposed to version 8 because of the dates?

I see repetition of document numbers but the titles are inconsistent.

So how do you run the report using each database? How do you connect to 3 so far flung databases? Access cannot set links to the databases? Access can set links to Excel spreadsheets. These links can be used in queries just like any table or query in Access.

[Huckleberry]

Sorry it took so long to get back to this issue, and thanks for your patience June7! :-)

Well to answer your question, I can't link Access to the different Dbases, as they are LotusNotes based, and according to our IT department, they haven't been able to get Access and our network to function properly with any LotusNotes based database. So apparently that is not an option. I had hoped it would be, but alas, it shall never be.....lol

Currently, I'm running the exported reports out of each dbase into Excel spreadsheets, formatting the column data, to align the data. I then take the individual dbase worksheets, and merge them with a "Status" worksheet, where I've identified the issues already, manually. Very cumbersome and time consuming! :-(

Once I merge them with the Status sheet, I end up with approximately 80,000 line entries, I then run the "Delete Duplicates" function, on the ribbon. This currently leaves approximately 48,000 entries. I then I manually go through the worksheet, and begin identifying any "new" conflicts that arise, such as revision mismatchs, data entry errors, where the syntax varies just a bit, but enough to say it's an isssue.

I was hoping to be able to take my "Status" Sheet, build the dbase in access, run my reports once or twice a week, import the "fresh" data, have it compared to the existing records in access. If the "fresh" record already exists in Access, then do nothing, if it does not exist then enter that row of data, as a "new" record.(utilizing the document number, revision, and revision dates together as the unique record ID's) I am then planning, (based on what I'm learning), to set up the workscreens in Access to be able to pull up all the data associated with any drawing number, (or for that matter, by RG, Discipline, or Type), view the entries, and then print into a pre-structured report, or notification, and then send the conflicting information to the appropriate discipline manager, for action/correction in their appropriate dbase. (Does that make sense?)

For example; (using the examples above)

We know that there is a conflict with document 27653-SYS-01 in that the titles, and the revisions, and the revision dates do not match one another.

I was hoping I could do a query in Access, to provide a comparision on all entries, by document number. Any comparision of the data associated with the DBase Source, in this case JPI & B1, that does not match all the way accross, get's identified as an issue. It takes the record information, for the discipline, (in this case it's MO2, and prints the records information for JPI & B1 into a report) and I can then forward that report by email to the manager for M02, and have either their responsible engineer or document group, investigate and correct the conflicted infomation in the source dbase. If their group has 25 conflicted records, they get all 25 at the same time, but this query would also, provide any conflicts for the other disciplines as well. (I have about 55 disciplines.)

Then the next time I updated the Access dbase, and if they have made the corrections, then the issue is resolved, and would not show up again.

That's the goal anyway.... based on your experience, is this achievable with Access?

Thanks for your time!!!

[June7]

Okay, LotusNotes, yes as far as know, is not compatible with Access, hence the need for a 'middleman' like Excel.

Access should be able to work with the 'Status' sheet to manipulate data and produce the output you describe. Sounds like a complicated process and expect will need substantial use of VBA. For a start, explore the Find Unmatched query wizard also INSERT SELECT sql action.

[Huckleberry]

Will do! Thanks for the eval and suggestions!