Update table with a max value from another table

[Bigdwg]

I am a new Access user and trying to update a field based on the max value from another table where the keys are equal.

Table A

sales Id, Units sold



Table B

sales id, month, Units sold

I need to update table A with the Max Units Sold for that Sales Id from Table B

Thanks for any help

[June7]

Why do you need to do this? Saving calculated data, especially aggregate data, is usually a bad idea. Save raw data and do calcs in queries and reports.

[Bigdwg]

The data is not calculated data, it is historical data. I want to select the month where the sales person had to most units sold.

[June7]

The calculation that would be saved is the max value returned from Table B. The maximum value can be retrieved in a query. There is no need to then save it to another table. The query to return max value is the same that would have to be run to capture the value for saving to other table. The extra step of saving the value is unnecessary and ill advised.

This is what GROUP BY (Totals) aggregate queries are for.

However, retrieving the maximum Units Sold for each ID is easy, returning the month associated with that maximum is trickier. The TOP N qualifier might work for you, review http://allenbrowne.com/subquery-01.html#TopN

[Bigdwg]

I appreciate you taking the time to respond. Yes, I agree that finding the maximum units sold is easy. I also well know what Group By is for.
I am having difficulty incorporating that into an update statement.

The extra step of saving the value is neither unnecessary or ill advised given the over all goal of what I am trying to accomplish.

[June7]

Do you want to insert new records in Table A for every SalesID?

Table A doesn't show a month field but you said you want to select the month. I presume that also means you want to save the month.

Does the TOP N approach return the desired records? Will probably need a unique ID field for that, autonumber will serve. A DLookup might be another approach.

Get a SELECT query to return the correct data and that query can be the source for the INSERT query.

I don't know enough about your data structure to be more specific.

What is the exact difficulty you are having?

Do you want to tell us more about the overall goal?