merging data

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am basically new to access, but very computer literate.

I have a business in which I need to be able to update
pricing in a "batch" kind of way.
In other words I have a list of 5000 products, I also
have lists of product groups that compose these 5000
products.
I would like to be able to take this list of 5000 (tab
delimited) products and update the prices of the small
list of 1000 products. The criteria I would like to use
is the "part number", it is the only unique identifier of
all 5000 products.

How do I update the prices of the 1000 products in a
batch/macro kind of way with the data from the 5000
products list.
I need to be able to tell Access to replace a particular
field (price) in the smaller (1000 product) list, based
on the "part number" only, with the new price from the
larger (5000 product) list.

I hope this makes sense.
Can anyone help me?
Thank you,
David
 
You can run an update query (warning: always make a backup copy of your
original data before doing this, just in case!). The query would be
something like this:

UPDATE SmallPriceListTable
INNER JOIN LargePriceListTable
ON SmallPriceListTable.PartNumber = LargePriceListTable.PartNumber
SET SmallPriceListTable.Price = LargePriceListTable.Price;
 
I hope you can add a bit more to this, Mr. Snell. I read
your two messages on merging data. One says to use an
append query, the other an update query. I want to add
data to a table that is a subform in another form -
subform2 (which in itself is a subform - subform1). I've
created a new table that has the data I want to merge. The
field names are the same as in the subform2. Subform2 does
not necessarily have the same number of records as in the
data table I want to merge. By this I mean the new table
has data just for 2003, but subform2 may only go to 2001
or 2002. Will either the append or update query accomplish
this merge?
Thank you.
 
Whether you use an append query (copy data from one table / query to another
table) or an update query (modify the data in a table without moving/copying
the data from/to that table) depends entirely on what you want to
accomplish.

By merge, I assume that you mean you want to copy data into the target
table? In that case, you'd use an append query.

You're referencing tables and subforms somewhat interchangeably, but they
are different things. Data are displayed in (sub)forms, but are stored in
tables. Forms can be used to directly manipulate data via the recordsources
of the forms, but you're still modifying the data in the table(s).

Can you present more information about the exact setup you are using/have?
What type of data are you wanting to "merge"? How do you want it to happen
(just "merge" it? or display it after you "merge" it?)?
 
Back
Top