Ok thanks geekgirl, I don't have real data to test this query file now..I will check it out at work tomorrow and get back.
Ok thanks geekgirl, I don't have real data to test this query file now..I will check it out at work tomorrow and get back.
sorry. double post
Last edited by lazyme; 10-26-2009 at 03:51 PM.
Hi geekgirl, the append query works great. However, if some field in a record is null and the record already exists in both the tables, it is still not considered a duplicate but is copied again to the main table every time the append query is executed. How can I get around this problem? I have attached a database having a record that duplicates this problem.
Also, is there any way to "call" this import unique query through code?
Last edited by lazyme; 10-26-2009 at 03:50 PM.
sorry. double post
Last edited by lazyme; 10-26-2009 at 03:51 PM.
Okay, I've created an interim step. Each table has been added to a query that creates a "key" field, which contains all the values from every field in the record. The append query compares this key, and only appends those records that don't have a matching value for the key field.
This is not recommended for large tables with either lots of fields or fields containing lots of data, but it will work okay in your situation.
Thanks geekgirl. This works great. I tried to create another unique key query for another database but couldn't get it to work.
I have attached my database file here. Could you please check what is wrong with the query?
Thanks.
I couldn't see anything wrong, but it didn't work for me either!
I've modified the query. Instead of having a join between the queries to compare the key field, I've set the criteria for the key (temporary table) to:
[vba]Not In (SELECT Key FROM Qry_Key_Labs)[/vba]
That works great. Thanks geekgirl.
Don't forget to mark the thread as solved - under "Thread Tools" at the top of the page.
Hi geekgirl,
I am not sure if I should post in this thread or open a new one.
I am running into performance issues with the append query where I have around 15000 records in my main table and 10000 in my temporary table. Now the append query is taking over 40-45 minutes to run. Is there some way I can improve the performance of this append query.
Perhaps the number of joins is creating a performance issue. Are you deleting all the records from the temporary table after appending the data?
I would recommend that you consider the concept of having a primary key. Users wouldn't even see the primary key unless you added the field to the forms they work with.
Hi,
Yes I am deleting all the record from the temporary table after appending the data. Can I make the key field(the one that has all columns appended) as a primary key?
If yes, how can I add they key field to the main table?
I'm assuming the MNU is unique for each customer - is this correct? With the product, your sample was pretty generic. Is there a way of uniquely identifying the product on each record?
Yes each customer has a unique MRUN number.But a customer can have multiple entries in the database so duplicate MRUN's are also allowed.
So I basically will have to use the same technique to import data(the append query that you suggested) with the only difference that my table will have a primary key.
Will this make any difference to the performance of my utility?
Yes, I understand the relationship of the MRUN. My question is about identifying a key for your import records - I think we can probably do something around a combination of MRUN, product and date. Which brings me back to my original question about the product - in your actual data, what information do we have about the product. Is there a unique identifier for each product?
No there isn't any unique identifier for a product. This is how the database has been since years. The only way to identify a record is unique is to compare all the entries in the record.
To go back to your question about how to improve the performance, the answer is to normalise the data. It is not a pain-free solution, but it will dramatically improve the performance and remove not only the current issue but lots of future ones as well.
In an ideal world the structure would be normalised from the start, however in real life we often inherit poorly-designed databases. It is going to take a bit of work to fix the structure, but it is worth it in the long run.
There is lots of information available on data normalisation, so I suggest you start reading. Also I'd prepare yourself for the fact that you either have to live with and work around the current performance (can you set the update to occur overnight for example?), or fix the database.
Ok. I will look into data normalisation. The utility will be run just once every fortnight so running it overnight is also a good temporary fix.
Thanks.
Posting Permissions
Reply With Quote
