SQL statement to update a field based on criteria

  • Thread starter Thread starter Shawna
  • Start date Start date
S

Shawna

I am trying to update a field in an access table based on
certain criteria. What I need to do is update a
description field with the appropriate text from other
tables based on the part number field. I have the sql
statement written as I need it but I am having trouble
with how to set the criteria to only update the
appropriate records.
Specifically here is what I need to do.
I have a part number ie. A15PSIBC - each letter/number in
this code represents part of the description - i.e A is
Aluminum, 15 is the size, P is a labour type, etc. I have
all the tables set up to 'build' the description for this
part number and I have the SQL statement (in VB) to
update the parts table with the description - that part
is working fine. Where I am running into trouble is I
need the description built based on different tables
based on the Item Type (the first letter of the Part
number)

What I thought would work is to open the item table as a
recordset (table I need to update).

Do while not rst.eof
'get first letter from Item no to set Item Type

sItemType = Left(ItemMaster!ItemNo, 1)

if sItemType = 'A'

then

'perform SQL statement to update description field only
where item number starts with A

then perform if to go to next item type and update those
records

'move to the next record to see what the Item Type for
that part is

rst.movenext

loop

What the above is doing though is Updating all the
records, not only the ones where the Item number starts
with A

Any help is appreciated. Thanks in advance.
 
Shawna said:
I am trying to update a field in an access table based on
certain criteria. What I need to do is update a
description field with the appropriate text from other
tables based on the part number field. I have the sql
statement written as I need it but I am having trouble
with how to set the criteria to only update the
appropriate records.
Specifically here is what I need to do.
I have a part number ie. A15PSIBC - each letter/number in
this code represents part of the description - i.e A is
Aluminum, 15 is the size, P is a labour type, etc. I have
all the tables set up to 'build' the description for this
part number and I have the SQL statement (in VB) to
update the parts table with the description - that part
is working fine. Where I am running into trouble is I
need the description built based on different tables
based on the Item Type (the first letter of the Part
number)

What I thought would work is to open the item table as a
recordset (table I need to update).

Do while not rst.eof
'get first letter from Item no to set Item Type

sItemType = Left(ItemMaster!ItemNo, 1)

if sItemType = 'A'

then

'perform SQL statement to update description field only
where item number starts with A

then perform if to go to next item type and update those
records

'move to the next record to see what the Item Type for
that part is

rst.movenext

loop

What the above is doing though is Updating all the
records, not only the ones where the Item number starts
with A

Any help is appreciated. Thanks in advance.
Click to expand...

Here is a way to do what you want without updating the description.
It's not good practice to have calculated fields or data in a record. It
will lead to incorrect data. Some day a user will change the part no or
the description and the data will be incorrect until you or someone runs
an update to correct it.

Create a query that looks something like this using your parts table.
In this query you will expand the part number so that each item is in a
separate field. Look at the Left and Mid instructions to see how I expanded
the part no.
Call This Query A.

SELECT tblParts.PartNo, _
tblParts.Description, Left([PartNo],1) AS Item, _
Mid([PartNo],2,2) AS Size, _
Mid([PartNo],4,1) AS Labor _
FROM tblParts;

If you haven't already, put each of the sub items in a table with the code
and a description. an example is:
ItemCode ItemDescription
A Aluminum
Do this for Item, Size, and Labor and any other sub descriptors needed.

Then Create a second query, call it B, and add query A to it. Also add the
tables
for the sub descriptors and join them on their code and add the description
field
to the new query. Then add a new field, call it FinalDescription to the query
and concatenate the new descriptions like this.
FinalDescription: tblItem.Description & " " & tblSize.Description & " " &
tblLabor

That query will look something like this.
SELECT A.PartNo, A.Description, tblItems.ItemDescription, _
tblSize.SizeDescription, tblLabor.LaborDescription, _
[ItemDescription] & " " & [SizeDescription] & " " & [LaborDescription] AS
[FinalDescription] _
FROM ((A INNER JOIN tblItems ON A.Item = tblItems.ItemCode) _
INNER JOIN tblSize ON A.Size = tblSize.SizeCode) _
INNER JOIN tblLabor ON A.Labor = tblLabor.LaborCode;

This way when ever you need the complete desctiption of a part you can use a
query
like this for a form or report and the information will always be correct.

HTH,

Ron
 
Thank you for your idea, I think I will do this intstead
of writing to the table.

Shawna
-----Original Message-----
Shawna said:
I am trying to update a field in an access table based on
certain criteria. What I need to do is update a
description field with the appropriate text from other
tables based on the part number field. I have the sql
statement written as I need it but I am having trouble
with how to set the criteria to only update the
appropriate records.
Specifically here is what I need to do.
I have a part number ie. A15PSIBC - each letter/number in
this code represents part of the description - i.e A is
Aluminum, 15 is the size, P is a labour type, etc. I have
all the tables set up to 'build' the description for this
part number and I have the SQL statement (in VB) to
update the parts table with the description - that part
is working fine. Where I am running into trouble is I
need the description built based on different tables
based on the Item Type (the first letter of the Part
number)

What I thought would work is to open the item table as a
recordset (table I need to update).

Do while not rst.eof
'get first letter from Item no to set Item Type

sItemType = Left(ItemMaster!ItemNo, 1)

if sItemType = 'A'

then

'perform SQL statement to update description field only
where item number starts with A

then perform if to go to next item type and update those
records

'move to the next record to see what the Item Type for
that part is

rst.movenext

loop

What the above is doing though is Updating all the
records, not only the ones where the Item number starts
with A

Any help is appreciated. Thanks in advance.
Click to expand...

Here is a way to do what you want without updating the description.
It's not good practice to have calculated fields or data in a record. It
will lead to incorrect data. Some day a user will change the part no or
the description and the data will be incorrect until you or someone runs
an update to correct it.

Create a query that looks something like this using your parts table.
In this query you will expand the part number so that each item is in a
separate field. Look at the Left and Mid instructions to see how I expanded
the part no.
Call This Query A.

SELECT tblParts.PartNo, _
tblParts.Description, Left([PartNo],1) AS Item, _
Mid([PartNo],2,2) AS Size, _
Mid([PartNo],4,1) AS Labor _
FROM tblParts;

If you haven't already, put each of the sub items in a table with the code
and a description. an example is:
ItemCode ItemDescription
A Aluminum
Do this for Item, Size, and Labor and any other sub descriptors needed.

Then Create a second query, call it B, and add query A to it. Also add the
tables
for the sub descriptors and join them on their code and add the description
field
to the new query. Then add a new field, call it FinalDescription to the query
and concatenate the new descriptions like this.
FinalDescription: tblItem.Description & " " & tblSize.Description & " " &
tblLabor

That query will look something like this.
SELECT A.PartNo, A.Description, tblItems.ItemDescription, _
tblSize.SizeDescription, tblLabor.LaborDescription, _
[ItemDescription] & " " & [SizeDescription] & " " & [LaborDescription] AS
[FinalDescription] _
FROM ((A INNER JOIN tblItems ON A.Item = tblItems.ItemCode) _
INNER JOIN tblSize ON A.Size = tblSize.SizeCode) _
INNER JOIN tblLabor ON A.Labor = tblLabor.LaborCode;

This way when ever you need the complete desctiption of a part you can use a
query
like this for a form or report and the information will always be correct.

HTH,

Ron
--
Ronald W. Roberts
Roberts Communication
(e-mail address removed)
To reply remove "_at_robcom_dot_com"


.
Click to expand...
 
Back
Top