Re: Query to merge
- From: Design by Sue <DesignbySue@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2009 14:39:01 -0800
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!
"Phil Smith" wrote:
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 wrote:
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" wrote:
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 wrote:
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
- Prev by Date: Query summing problem
- Next by Date: Re: Normalizing Monthly Sales Data
- Previous by thread: Query summing problem
- Next by thread: Modifying a field in one table with data from another table
- Index(es):
Relevant Pages
|
