Web Development

Ask something
Question Locked

Update Queries Using Composite Keys

0 helpful
5 replies

by crownandring

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm having an issue trying to get an update query to run properly. I have a table which includes five (5) specific fields to note:
1) Customer Number
2) Customer Part Number
3) Our Part Number
4) Cost
5) Price
There are other fields, including an AutoNumber ID field for the primary key, but these five are the ones I'm concerned with right now.

In the future, Cost and Price will occasionally need to be updated. The update data will come from an outside source that is either not compatible with Access or not directly available to tie to this Access database. The update data will be available in Excel, though, and will contain the above 5 fields.

My intention is that the udpate data can be imported into a new table in Access (named "PricingUpdates"), run the update query, then delete all the data in that table we just imported. The reason is that there are only about 1500 records in my database, and there are about 50,000+ records in the update data source.

In order to make a unique combination, I need the query to find where the records are identical using: Customer Number + Customer Part Number + Our Part Number = unique record.

Every time I have tried to design an update query to do this, I get either an error message saying that it couldn't run because of "ambiguous outer joins" or it fails to update at all, and on a couple occasions it has actually apended one table or the other with blank rows of records.

My knowledge of SQL is nil. I have used VB very seldomly, and only when I couldn't find any other way to get something done. In those instances, I typically sought out what I needed to have my database do by searching online and then copied and/or tweaked existing code snippets. Most of the time I was able to do this without asking for any assistance whatsoever, but this time around (and it's been a few years since I've had to do ANYthing with Access) I am completely stumped.

Hopefully I'm just an idiot for forgetting something simple, but I'd be greatly appreciative for any assistance!

5 replies

by tony hopkinson

Is Customer Number + Customer Part Number + Our Part Number
unique in pricing updates

Select Distinct [Customer Number],[Part Number], [Our Part Number] From PricingUpdates
Should return the same number of rows as
Select * from PricingUpdates
if it is.

Second problem is update through a subquery

Update Parts Set Price = (Select Price From PricingUpdates pu where pu.[Customer Number] = Part.[Customer Number] and pu.[Part Number} = Parts.[Part Number] and pu.[Our Part Number] = parts.[Our Part Number])
Then same for cost will work given the key is definitely unique, it's wildy inefficient though.

You could add a unique index to Parts for this compound key (given it's unique in Parts as well !) which might improve things

You might also find it worthwhile to delete all the irrelevant parts out of pricingupdates before you do the update.

Another sneaky way to do it, might be to take price and cost out of parts and join to pricingupdates to get that part of the data.

Last but not least is Our Part Number Unique all by itself? Could it be made so if not?

HtHs

PS have you put a primary key (compound on pricing updates. If you do this in oparts and pricingupdates, the performaance improvment should be considerable

by crownandring

Unfortunately I can't remove the Cost or Price fields from the Existing Part Numbers table. Nor is the Our Part Number unique by itself or can it be made so.

Thankfully, the data I'm working with has very few repeats of Our Part Number or of Customer Part Number, so the added rows of data is so minimal that further normalization isn't really required. For ease of design and operation it was done in this fashion.

As for the three fields making a unique record in both tables, the answer is: Yes. The database I've designed is for three clients who have merged into one company. The database is to group parts that used to be sold seperately to each client individually and/or to multiple branches of one of the companies and consolidate them for future sales. Many of the parts are essentially the same part with different color schemes or only different packages. The pricing updates, however, will be coming out of a "main" data center that tracks information for hundreds or thousands of customers and hundreds of thousands of parts, which is why I intend for the information to be imported, updated, and then deleted. The key problem with making this any easier or more efficient is that the main data center will not be utilizing this new grouping information.

In essence, to understand the functionality (if that helps any), here is an example:
Our Part # "12345678" is sold in the exact same color and package to companies A and B. Company A has it coded as their part # "A0001" and Company B calls it "B1234." Additionally, we have the same part in a different color scheme for company C which has our part # "12345677" and their number is also "A0001." So, we now have three records that look like this:
12345678+CompanyA+A0001, 12345678+CompanyB+B1234, and 12345677+CompanyC+A0001.
As I said, it's a very minute chance that you'll see our part# or their part# again after this record set; however, what this database does is combine these three records in a subform and relate them to Our New Part # ABC12345678 and New Customer Part # AB0001.

The individual parts aren't going away any time soon, which is why the main data center will not yet be using this information. Service of these will continue for quite a while individually, but there will also be new "uniform" or "universal" parts made (the New Part #s) going forward.

It's an efficiency nightmare, I agree, but it's what I was charged with helping accomplish. Hey, this place is still using the same DOS program they've used for almost 30 years. They have a conversion to Oracle for certain things that is widely used in a corner of the building that has taken 6 years to move one department over...

by tony hopkinson

I posted should work then.
You'll need to do it once for price and once for cost, or do the subquery twice
Update ..Set Cost = (), price = ()
You can't do them both at the same time.

I can think of a few potential ideas to optimise it, but no point until you've run the chuffer and seen how bad it is.

Also when are you going to run it, out of hours is going to be best.

by crownandring

I think I got it.

I created a new table using the wizard so it would create a relationship for me.... I don't think I needed to do it this way, but I did.

PricingUpdate: Table
PricingID
Customer Number
Customer Part Number
Our Part Number
Cost
Price

A PricingID FK was added to the Existing Part Numbers table.

I created a new Query in design mode, added the two tables, deleted the 1-to-many join, added the three joins I needed and did not change their types, then changed the Cost and Price fields to read Table: Existing Part Numbers (instead of PricingUpdate) and in the UpdateTo: I had to put in [Existing Part Numbers.Cost] and [Existing Part Numbers.Price] ....

I think I was not keeping the 1-to-many join, and/or I may have been trying to enforce referential integrity and cascade or I was changing them from where they are equal to include all from one table or the other before...

In any case, I did a test with just five records using a new backup copy and it worked out just fine.

This discussion is closed to new replies.