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

Update Query or VBA?

P: n/a
I have the following table with fourfields:
Inc Length Width Cost
1 5 5 2.10
2 5 10 4.20
3 5 5 1.70
4 5 5 1.10
5 5 10 2.10

I need to get to this:
Inc Length Width Cost
1 5 5 1.10
2 5 10 2.10

It's a small table and will always be small. I was thinking about
using VBA to compare the fields in each record and the update the cost
field with the lost matching record's cost and then delete the other
records but I thought there might be an easy way to do this with an
update query.

Any thoughts on how this would best be accomplished? No need to worry
about the Inc field-it's value is not important.

--
Greg
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Greg Daniels wrote:
I have the following table with fourfields:
Inc Length Width Cost
1 5 5 2.10
2 5 10 4.20
3 5 5 1.70
4 5 5 1.10
5 5 10 2.10

I need to get to this:
Inc Length Width Cost
1 5 5 1.10
2 5 10 2.10

It's a small table and will always be small. I was thinking about
using VBA to compare the fields in each record and the update the cost
field with the lost matching record's cost and then delete the other
records but I thought there might be an easy way to do this with an
update query.

Any thoughts on how this would best be accomplished? No need to worry
about the Inc field-it's value is not important.

--
Greg


I would first make a copy of your current table.

Next, I might create a Totals query and seach for the Min() of Inc and
Cost and make this query a MakeTable query (look under the Query pad in
the menu)

Then I would create another query. Select Query/New/FindUnMatched.
Select your current table and the table from the MakeTable query. See
if this query returns the correct results. If so, set the query type to
Delete and run.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.