By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,365 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Populate record with lowest match

P: n/a
Hello again. Thanks very much to those who helped me earlier. The advice
was spot on. I have one more thing to work out - then I can hang it up for
the week - and am hoping I am not dipping into the well too much.

In a single table I have:

Code | Price1 | Price2
1234 | 10 |
1234 | 10 |
1234 | 11 |
1234 | 12 |
5678 | 15 |
5678 | 16 |
5678 | 16 |

Very simply I need to look through all records and for every "set" where
the Code field is the same (4 entries for 1234 and 3 entries for 5678
above) I have to find the *lowest* numerical value in Price1 and put it in
*all* records of Price2

So the result of the above would be:

Code | Price1 | Price2
1234 | 10 | 10
1234 | 10 | 10
1234 | 11 | 10
1234 | 12 | 10
5678 | 15 | 15
5678 | 16 | 15
5678 | 16 | 15
As always, many thank yous.
Mar 3 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, 02 Mar 2007 19:11:33 -0600, NO****@comcast.net (Tony) wrote:

To find the minimum value per code, use a Totals query like this:
select Min(Price1) from SomeTable group by Code
This returns:
1234|10
5678|15
Save that as a query, say qryMinPrice.
Now create a new query, select both the table and qryMinPrice, turn it
into an Update query, and see if you can figure out how to populate
Price2.

Just trying to teach you how to fish :-)

-Tom.
>Hello again. Thanks very much to those who helped me earlier. The advice
was spot on. I have one more thing to work out - then I can hang it up for
the week - and am hoping I am not dipping into the well too much.

In a single table I have:

Code | Price1 | Price2
1234 | 10 |
1234 | 10 |
1234 | 11 |
1234 | 12 |
5678 | 15 |
5678 | 16 |
5678 | 16 |

Very simply I need to look through all records and for every "set" where
the Code field is the same (4 entries for 1234 and 3 entries for 5678
above) I have to find the *lowest* numerical value in Price1 and put it in
*all* records of Price2

So the result of the above would be:

Code | Price1 | Price2
1234 | 10 | 10
1234 | 10 | 10
1234 | 11 | 10
1234 | 12 | 10
5678 | 15 | 15
5678 | 16 | 15
5678 | 16 | 15
As always, many thank yous.
Mar 3 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.