data merge

[rfcomm2k]

It is becoming painfully obvious that I am not an Access programmer, although I am trying. I am filling a need where nobody else has a desire to go.

So let me lay all the cards on the table and see what suggestions I get.

I have 2 PBX systems on this campus. One is an NEC 2400. It spits out the call records in a specific format. I have developed an Access database to price these calls, add division/department/names, etc to report in a format the end users will recognize.

Recently a Cisco Call Manager was added to the campus and i have been trying to retrieve the records from it to merge with the other records.

With the help of some of you I have succeeded in retrieving the Cisco data and can pick out the various parts of the date/time fields, as well as other parts I need.

My problem now is how to merge the Cisco data with the 2400 data.

1. Should/can I merge the Cisco data directly into the 2400 table? If so, how can I xref the cisco field names with the corresponding 2400 field names, and how do I put the data into the table without disturbing the original contents?

2. Should I bring the Cisco data into a separate table (as I have already started to do) and then try to merge it? If so, again, how do I put the data into the table without disturbing the original contents?

I am going to try to attach a sample of each.
2400.xls is an Excel file exported from the original table of the 2400 records
cisco.xls is an Excel file exported from the original table of the cisco records
ciscoCDR.txt is a csv text file exported directly from the Cisco CM.

The ciscoCDR date/time is number of seconds since Epoch (1/1/1970) in UTC.

Again, my goal is to retrieve the various parts of the ciscoCDR files and place them into the correct fields of the 2400 table.

I thank all of you in advance for any helpful advice you may provide.

 

[The_Doc_Man]

In general, when you have disparate record sources, it helps to do a separate import to do your data massaging and then merge the result either of two ways.

1. For working purposes, create a separate table that is the sum of the two tables, then do append queries from each. That way, you get to match field names from disparate sources if necessary. THIS IS NOT NECESSARILY THE PREFERRED SOLUTION! (Technically, it could violate normalization concepts.) I'm a pragmatist and this might be right IF each time you import, you get only a date-limited subset of the accounting data you want. I.e. two successive imports from the same source don't contain (or don't need to contain) records from the same time period. If you then append the two subsets to a master table and delete the temporary imports, this DOES NOT violate normalization rules and you are probably OK with this approach.

2. Look up UNION queries as a way to dynamically, through a query, join two tables with similar structures but different element names. Then base your reports on the query, not the original table. Some purists will tell you this is the preferred method, and if each import table must be retained beyond the initial import action, it is probably safer.

In the case where both data sources have overlapping time ranges so that each import contains some potentially duplicate data, you would do yourself the greatest of favors to eliminate the overlap first before using EITHER of the above.

For example, if you import data once per day but your data sources keep two days' worth of data, you have a potential one-day overlap from one day to the next. That will be an issue to be managed before you can do meaningful billing.

 

[rfcomm2k]

Thanks Grandpa! LOL

The data is imported once per month, and the first thing I do after import is to delete invalid records, i.e. records that are incomplete because the daily FTP from the host cut off in the middle of the PBX sending a record. This cannot be avoided, but the loss of 30-40 records per month out of as many as 250,000 is not something I care to worry about right now.

The 2 datasets will never have duplicate data as the data is collected from 2 separate sources. Yes, there can be many records with duplicate date/time stamps, but other fields make each record unique.

It looks like I already have solution 2 under way. Can I map different field names from different tables to the same field in a query? Example, I have a 2400 field called 'extension'. The same field in the cisco table is called 'callingPartyNumber'. I would want to produce reports that treat these two fields equally, as if the data was all following the same table structure. Besides, I already have a lot of reports and queries that follow the 2400 structure and would like to avoid modifying all of these.

Can an append query start with a populated 2400 table and then append the data from the cisco table, placing the cisco data into the appropriate fields in the 2400 table? (i.e. can I append records to the 2400 table from the cisco table and have the cisco 'callingPartyNumber' data placed into the 2400 'extension' field?)