Update Query based on a value between two fields

[Guest]

Hi there,

I'm trying to update a column in my data which will add a name based on what
weight bands it falls into.

I have one table which contains three columns: weight start and a weight end
value (both as long integers) and a name for each weight band (eg Name of
Supplier: Swisspost, WeightStart = 0, WeightEnd = 100, then the next row
would be Name of Supplier: La Poste, WeightStart = 101, WeightEnd = 500 and
so on) and one table which contains a weight of item and Supplier.

I want the Supplier field to be updated with the correct supplier name based
on the weight band it falls into.

How do I go about doing this?

Thank you ind advance for your help.

K

 

[John Spencer]

I would try the following. It may not work due to the non-equi join
criteria

UPDATE TableA INNER JOIN TableB
ON TableA.Weight >= TableB.WeightStart
AND TableA.Weight <= TableB.WeightEnd
SET TableA.Supplier = [TableB].[Supplier]

If that doesn't work, then you could create and populate a temporary table
with TableA supplier and weight and table b supplier. And then use the
temporary table to do the update.

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I think John has his >< in the wrong spots in the DLOOKUP

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)

I think it should be
DLOOKUP("Supplier", "TableB",
"WeightStart<=" & TableA.Weight & " AND WeightEnd >= " & TableA.Wieght"

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I would try the following. It may not work due to the non-equi join
criteria

UPDATE TableA INNER JOIN TableB
ON TableA.Weight >= TableB.WeightStart
AND TableA.Weight <= TableB.WeightEnd
SET TableA.Supplier = [TableB].[Supplier]

If that doesn't work, then you could create and populate a temporary table
with TableA supplier and weight and table b supplier. And then use the
temporary table to do the update.

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi there,

I'm trying to update a column in my data which will add a name based on
what
weight bands it falls into.

I have one table which contains three columns: weight start and a weight
end
value (both as long integers) and a name for each weight band (eg Name of
Supplier: Swisspost, WeightStart = 0, WeightEnd = 100, then the next row
would be Name of Supplier: La Poste, WeightStart = 101, WeightEnd = 500
and
so on) and one table which contains a weight of item and Supplier.

I want the Supplier field to be updated with the correct supplier name
based
on the weight band it falls into.

How do I go about doing this?

Thank you ind advance for your help.

K

 

[Guest]

Thank you guys,

I'll take a proper look at this when I have a moment!

I think John has his >< in the wrong spots in the DLOOKUP

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)

I think it should be
DLOOKUP("Supplier", "TableB",
"WeightStart<=" & TableA.Weight & " AND WeightEnd >= " & TableA.Wieght"

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I would try the following. It may not work due to the non-equi join
criteria

UPDATE TableA INNER JOIN TableB
ON TableA.Weight >= TableB.WeightStart
AND TableA.Weight <= TableB.WeightEnd
SET TableA.Supplier = [TableB].[Supplier]

If that doesn't work, then you could create and populate a temporary table
with TableA supplier and weight and table b supplier. And then use the
temporary table to do the update.

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi there,

I'm trying to update a column in my data which will add a name based on
what
weight bands it falls into.

I have one table which contains three columns: weight start and a weight
end
value (both as long integers) and a name for each weight band (eg Name of
Supplier: Swisspost, WeightStart = 0, WeightEnd = 100, then the next row
would be Name of Supplier: La Poste, WeightStart = 101, WeightEnd = 500
and
so on) and one table which contains a weight of item and Supplier.

I want the Supplier field to be updated with the correct supplier name
based
on the weight band it falls into.

How do I go about doing this?

Thank you ind advance for your help.

K

 

[John Spencer]

Gollleeee, he sure does.

Thanks for the catch.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I think John has his >< in the wrong spots in the DLOOKUP

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)

I think it should be
DLOOKUP("Supplier", "TableB",
"WeightStart<=" & TableA.Weight & " AND WeightEnd >= " & TableA.Wieght"

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I would try the following. It may not work due to the non-equi join
criteria

UPDATE TableA INNER JOIN TableB
ON TableA.Weight >= TableB.WeightStart
AND TableA.Weight <= TableB.WeightEnd
SET TableA.Supplier = [TableB].[Supplier]

If that doesn't work, then you could create and populate a temporary
table
with TableA supplier and weight and table b supplier. And then use the
temporary table to do the update.

Or you could use a DLookup function in the update query

UPDATE TableA
SET TableA.Supplier =
DLOOKUP("Supplier", "TableB","WeightStart>=" & TableA.Weight & " And
WeightEnd <=" & TableA.Weight)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi there,

I'm trying to update a column in my data which will add a name based on
what
weight bands it falls into.

I have one table which contains three columns: weight start and a
weight
end
value (both as long integers) and a name for each weight band (eg Name
of
Supplier: Swisspost, WeightStart = 0, WeightEnd = 100, then the next
row
would be Name of Supplier: La Poste, WeightStart = 101, WeightEnd = 500
and
so on) and one table which contains a weight of item and Supplier.

I want the Supplier field to be updated with the correct supplier name
based
on the weight band it falls into.

How do I go about doing this?

Thank you ind advance for your help.

K