A clien

[Design by Sue]

A client has a poorly designed Access database where they work directly on
the table (no forms) and all info in in the one table (yes there is so much
room for improvement - but that aside) Somehow someone made a copy of the
entire Database and worked on it while the others used the correct file - the
result is 2 columns were updated with information in each database - I need
to create a query to clean this up. First of all there is a continuously
numbering primary key so I have something to work with. The two columns
which need to be merged are a date column and an hours column. For each I
need to compare the date column for the particular key number and use the
most recent date, and in the hours column, use the larger number of hours.
My thoughts are to combine the two tables, giving each two fields for date
and hours, one field each for the information from each database (ie. date
a, date b, and hours a hours b). Then create another 2 fields, perhaps
called date merged, and hours merged. I would then need to create a query
that would do as described, compare the two fields and enter in the merged
field either the more recent date and the larger number.

My question is does this sound like a good way to accomplish this and how
would I write such a query?

I am just trying to get an idea of the time this would take me so I an give
a quote for the work. On the surface this doesn't seem too difficult to me
but I have gotten caught in that trap before!

Any response, especially quickly, will be appreciated!

Sue

 

[Design by Sue]

Forgot to complete subject line in post - sorry

 

[Design by Sue]

Forgot to correct subject when posting this message - sorry

 

[Phil Smith]

Assuming you do not have a case where they added rows to either
database, but simply updated fields, I would build make table query,
which joined the two tables together, and recreated that structure into
the new table, grouping by all of the other data, and using MAX on the
date and hours coloum.

I could build that query in a few minutes, and I am not an expert.
However, This assumes things are exactly as you have portrayed, and
other issues may not show themselves until after you get into it. Make
sure that you put "extra time if needed" in your quote.

One quick test is to do a record count in each table, then join the two,
display something, and do a record count in the results. If all three
are teh same count, you are probably good to go for a quick fix.

 

[Design by Sue]

Thanks Phil for the prompt response - if there are additional records in one,
as long as the primary key numbers match wouldn't there be a way to write the
query or the join of the tables to match up the records?

 

[Phil Smith]

Certainly. If it was only one table which had additional records, you
would append those that did not have a match on the other side, or
simply build your query to give you all records from the one, and only
matching on the other.

The big problem scenario would be when additional records were entered
in both. Say it was a customer database. You would have two different
customers with the same primary key. It is this type of thing that will
start to take extra time and hassle. It is certainly doable, but you
are now dealing with another complication, and more time to deal with it.

It is the unknowns that bite you. Make sure you have the tme to devote
if the uglies come out, and make sure you are gonna get paid for it if
you devote your time.

 

[Design by Sue]

Thanks for that insite - I am sure that the additional customers (that would
add a primary key number) were added only from the one computer - but it will
need checking. Really appreciate your help - hopefully I'll get the job
because I hope it leads to an entire and correct rewrite of the application!

 

[John Spencer]

ASSUMING that no new records were added to the copy and only updates or
deletes were made in the copy I would use two queries like the following.

UPDATE Original INNER JOIN Copy
ON Original.PrimaryKey = Copy.PrimaryKey
SET Original.DateColumn = [Copy].[DateColumn]
WHERE NZ(Original.DateColumn,#1/1/1800#) < NZ(Copy.DateColumn,#1/1/1800#)

Then do a similar update query for the Hours column

UPDATE Original INNER JOIN Copy
ON Original.PrimaryKey = Copy.PrimaryKey
SET Original.Hours = [Copy].[ours]
WHERE NZ(Original.DateColumn,0) < NZ(Copy.DateColumn,0)

IF there were records added to copy then you will need to determine the
highest primary key at the time to the copy was made and add criteria to
the above query to limit the updates to less than or equal to that
primary key value.

THEN you would need to work out how to handle the new records that were
added to the copy. IF they are guaranteed to be different, then you
would need to append the new records from the copy while giving the
appended records a NEW primary key value.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Design by Sue]

Thank you - you have been very helpful - it definitely sounds like I will be
able to do this via a query!

Sue