Update Query or VBA?

[Greg Daniels]

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

[Salad]

Re: Update Query or VBA?

Greg Daniels wrote:
[color=blue]
> 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[/color]

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.