Update one table from another?

[jpardi]

It sounds easy enough, but I guess I am missing something.

I have two tables, Purchase Order Receipts and Market Prices. I joined the two tables based on part number, with join property as "select all from receipts".

Market prices change every month, and not all parts have a market price. Receipts table has field "Purchase Price" and every receipt has a purchase price.

I would like the market price to update the field "good price" in receipts table based on date criteria, i.e. the receipt date falls in the respective market price date range, and if there is no market price, then update the field "good price" with "purchase price"

I used this in the update to "good price" field
IIf([Receipts]![Recd date] Between ([Market Price]![start date]) And ([Market Price]![end date]),[Market Price]![Market Price],[Receipts]![Purchase Price])

and I either get all records updated multiple times - I think it took a one-to many and showed all??
or it does not update the "purchase price"

Did I miss something? sorry, I dont know VBA or SQL, so I am looking for something easy, if possible.

Thanks for your help

Joanne

 

[Joe4]

I kind of got lost in your details, but maybe this will help.

If you can create a query to just select the records you want to update (by using Criteria), then change the query type to "Update Query", then you can enter formula/value you want to change your records to.

Check out "Create an Update Query" in Access Help for more details.

 

[jpardi]

Thanks for the reply. I tried that but still not getting all records.

I will try and make it brief. I want to update "price" field for all records in the receipts table. The source "price" will be in its own table if it cannot find a matching record (depending upon date) in the other table.

Thanks for trying.

 

[Joe4]

jpardi,

I tried outlining the structure from your two posts, and am having difficulty seeing exactly how everything is related (there seem to be a lot of differrent "price" records -- Price, Purchase Price, Good Price, etc).

It would help if you actually list the 2 or 3 tables involved, and list out the relevanet fields in each. Then, work out an "concrete" example, showing what you expect to happen.

 

[jpardi]

Hi Jmiskey, thanks for your persistence!

Table:Receipts
Part # (Outer join)
Received Date
Purchase Price
Price Should Be (this is the field to be updated (used to be Good Price!))

Table: Market Price (may have a different price every month)
Part #
Market Price
Start Date
End Date



What I am looking for is where we make mistakes, i.e. market price if there is one, should prevail over purchase price. So if "receipts" received date falls between "Market price" start & end date, then update the "Price should be" field with the market price. If there is no market price then the "price should be" field should be the purchase price.

One result returned 4 records, one record for each month.

Hope I haven't confused you more...

 

[Joe4]

OK, here is what I did. The price range was making things confusing, so I used a query to simplify it.

From your posts, it looks like you prices can change monthly. So I created a query for each table with the new calculated field:

Query: qry Receipts
New Field: MyID: Year([Received Date]) & Month ([Received Date])

Query: qry Market Price
New Field: MyID: Year([StartDate]) & Month ([StartDate])

Now, I create a new query from these two queries, connecting the two on Part # and MyID (and selecting ALL the records from qry Receipts).

In this query, select the fields to display (make sure Price Should Be is one of them). Now, change the query to an update query, and under Price Should Be in the Update To row, enter [qry Market Price]![Market Price]. Run the query.

This should update the fields, and you can run a query on your original Receipts table comparing the Purchase Price and Price Should Be columns for differences.

Note, that my method of creating the new IDs will work only if there is no overlap between months (i.e. Start Date and End Date occur in same month, and there aren't multiple prices for the same part number in the same month). If this does not hold true, you will have to think of a different way of comparing the queries.

At the very least, I hope this gives you a few ideas.

 

[jpardi]

jmiskey, thanks for your help, this place has gone crazy, will have to try later.