Data update

[Ollie]

I currently have a master product catalogue (650000 lines) in access which is
updated from our oracle systems. I would like to update/ add customer
specific catalogues (70,000 lines) with certain information from the master
catalogue ie prices changes.
I have a unique field product code in both of the tables. I have tried using
a append query however which works but just duplicates the data and does not
update the existing product lines.
What in really looking for is a vlookup type function which will lookup the
product code on the customer catalogue table against the product code on the
master table and bring back various product information

Customer catalogue

1-Customer product ID
2-Annaul usage
3-Current price
4-Description
5-Product ID - unique field in both tables
6-Sytem description
7-system price


I have listed above some of the field in the customer table. I would like
lines 1 to 5 to be static but lines 6/7 to update based upon line 5 against
the Master catalogue (650,000 lines)

Any advice or suggestions would be great.

Thanks,

Ollie

 

[John Spencer]

Well, do you want to UPDATE the data (replace the value of specific fields) or
append the data (add new records)? Or are you just trying to "look up"
information in the Master catalogue?

To update the Customer Catalogue, you would need an update query that looked
like the following to update the Current Price field:

UPDATE [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]
SET C.[Current Price] = [M].[Current Price]
WHERE C.[Current Price] <> M.[Current Price]
OR C.[Current Price] Is Null
OR M.[Current Price] is Null

If you wanted to update the System Price and the current Price then you might
use a query like the following.

UPDATE [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]
SET C.[Current Price] = [M].[Current Price]
, C.[System Price] = [M].[System Price]
WHERE C.[Current Price] <> M.[Current Price]
OR C.[Current Price] Is Null
OR M.[Current Price] is Null
OR C.[System Price] <> M.[System Price]
OR C.[System Price] Is Null
OR M.[System Price] is Null

If you just want to lookup/use the information in the Master Catalogue then
just use a select query joining the two tables on the Product Id.

SELECT C.[Customer Product Id], C.[Annual Usage],
[C].[Description], C.[Current Price], [M].[System Description]
,[M].[System Price]
FROM [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]


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

 

[Ollie]

Hi John,

I initailly want to look for product id matches on the customer cat / master
cat and bring back price and description data from the Master catalogue table
to populate the empty fields in the customer cat table.
This table will them become the master for that particular customer. However
not all for the product Id's will match with the Master Catalogue (650.000
lines) leaving empty field. Through a sepate match exercise by eye (comparing
descirptions/brand etc) a product id will be identified.
I would then like to automatically enter this new product ID against the
product line in the Customer catalogue, which will then in turn reference
against the Master Product Catalogue and return price and description info.
At the same time as brining this data back i would like the price/
desrcription info in the cusomter cat to be refreshed as the Master catalogue
info may have changed.
Is it possible to do this all in one table.I have approx 15 customers I
would like to have this process for?

Thanks,

Ollie

Well, do you want to UPDATE the data (replace the value of specific fields) or
append the data (add new records)? Or are you just trying to "look up"
information in the Master catalogue?

To update the Customer Catalogue, you would need an update query that looked
like the following to update the Current Price field:

UPDATE [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]
SET C.[Current Price] = [M].[Current Price]
WHERE C.[Current Price] <> M.[Current Price]
OR C.[Current Price] Is Null
OR M.[Current Price] is Null

If you wanted to update the System Price and the current Price then you might
use a query like the following.

UPDATE [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]
SET C.[Current Price] = [M].[Current Price]
, C.[System Price] = [M].[System Price]
WHERE C.[Current Price] <> M.[Current Price]
OR C.[Current Price] Is Null
OR M.[Current Price] is Null
OR C.[System Price] <> M.[System Price]
OR C.[System Price] Is Null
OR M.[System Price] is Null

If you just want to lookup/use the information in the Master Catalogue then
just use a select query joining the two tables on the Product Id.

SELECT C.[Customer Product Id], C.[Annual Usage],
[C].[Description], C.[Current Price], [M].[System Description]
,[M].[System Price]
FROM [Customer Catalogue] as C INNER JOIN [Master Catalogue] as M
On C.[Product ID] = M.[Product ID]


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

I currently have a master product catalogue (650000 lines) in access which is
updated from our oracle systems. I would like to update/ add customer
specific catalogues (70,000 lines) with certain information from the master
catalogue ie prices changes.
I have a unique field product code in both of the tables. I have tried using
a append query however which works but just duplicates the data and does not
update the existing product lines.
What in really looking for is a vlookup type function which will lookup the
product code on the customer catalogue table against the product code on the
master table and bring back various product information

Customer catalogue

1-Customer product ID
2-Annaul usage
3-Current price
4-Description
5-Product ID - unique field in both tables
6-Sytem description
7-system price


I have listed above some of the field in the customer table. I would like
lines 1 to 5 to be static but lines 6/7 to update based upon line 5 against
the Master catalogue (650,000 lines)

Any advice or suggestions would be great.

Thanks,

Ollie