Compare 2 Tables & update a Field

[Guest]

I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field in
Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl

 

[strive4peace]

Hi Anath,

here is how to update for the unmatched records:

make a new query

add tables --> [Supplier Master] and [Supplier Country]

link on [Supplier Code]

right-click on the link-line
choose to see ALL fields in [Supplier Master] and just those records in
[Supplier Country] where they match

make the query an Update Query
from the menu --> Query, Update

on the grid -->

field --> [Supplier Code]
table --> [Supplier Country]
criteria --> Is Null

field --> [Country Field]
table --> [Supplier Master]
updateTo --> “No country codeâ€

~~~~~~~~~~~~~~

To update for matches, change the link line to option 1 -- show records
from both tables where they match

then, on the grid:

field --> [Country Field]
table --> [Supplier Master]
updateTo --> [Supplier Country].[Country Field]


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks very much. It worked. But can it not be done in a single query.

Hi Anath,

here is how to update for the unmatched records:

make a new query

add tables --> [Supplier Master] and [Supplier Country]

link on [Supplier Code]

right-click on the link-line
choose to see ALL fields in [Supplier Master] and just those records in
[Supplier Country] where they match

make the query an Update Query
from the menu --> Query, Update

on the grid -->

field --> [Supplier Code]
table --> [Supplier Country]
criteria --> Is Null

field --> [Country Field]
table --> [Supplier Master]
updateTo --> “No country codeâ€

~~~~~~~~~~~~~~

To update for matches, change the link line to option 1 -- show records
from both tables where they match

then, on the grid:

field --> [Country Field]
table --> [Supplier Master]
updateTo --> [Supplier Country].[Country Field]


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field in
Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl

 

[Jamie Collins]

make a new query

add tables --> [Supplier Master] and [Supplier Country]

link on [Supplier Code]

right-click on the link-line
choose to see ALL fields in [Supplier Master] and just those records in
[Supplier Country] where they match

make the query an Update Query

The equivalent standard SQL UPDATE syntax (for which Access/Jet is
non-compliant) is something like:

UPDATE <table>
SET <column> = (<scalar subquery>)
....

'Scalar' is database jargon for 'single value'.

Consider these tables:

TableA:
(1, NULL)

TableB:
(1, 'USA')
(1, 'UK')

To relate the above to the OP's spec, consider that the supplier trades
in more than one country; a reasonable assumption given that 1:1
relationships are usually modelled by one table rather than two.

To update the second column in TableA, as per your description you
would join to TableB using the first column. Which value should the
UPDATE use, 'USA' or 'UK'?

The result should be undetermined, hence the requirement for a scalar
subquery in the standards, yet Access/Jet is happy to pick one. Do you
know which one Access/Jet will pick? Cheery <g> in advance but I think
that if you do know you should explain this in your reply to the OP; if
you don't know, perhaps you should not be suggesting such syntax at
all.

Jamie.

--

 

[strive4peace]

you're welcome, Anath

"But can it not be done in a single query."

If you are using code to run the queries, it shouldn't really make a
difference -- but to answer your question: yes, it probably can, but you
would need to use a slower and more complicated process

I suggested 2 seperate queries because the logic is easier to understand.

If [Supplier Code] does not exist in your [Supplier Country] table, then
there is no field for the link. "Links" can be done using criteria, but
it does complicate things when the linking field is not there and you
need to use more complex expressions to get the same results.

I have assumed that each supplier has just one country; I should have
stated that -- if this is not right, this changes things...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Thanks very much. It worked. But can it not be done in a single query.

Hi Anath,

here is how to update for the unmatched records:

make a new query

add tables --> [Supplier Master] and [Supplier Country]

link on [Supplier Code]

right-click on the link-line
choose to see ALL fields in [Supplier Master] and just those records in
[Supplier Country] where they match

make the query an Update Query
from the menu --> Query, Update

on the grid -->

field --> [Supplier Code]
table --> [Supplier Country]
criteria --> Is Null

field --> [Country Field]
table --> [Supplier Master]
updateTo --> “No country codeâ€

~~~~~~~~~~~~~~

To update for matches, change the link line to option 1 -- show records
from both tables where they match

then, on the grid:

field --> [Country Field]
table --> [Supplier Master]
updateTo --> [Supplier Country].[Country Field]


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field in
Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl

 

[strive4peace]

Hi Ananth,

Instead of updating country in one table when you already have it stored
in another, why not just link to that information whenever you need it?
When there are null values (Suppliers that don't have a record in
[Supplier Country], you could do something like this in your query:

field --> SuppCountry: IIF(
Isnull([Supplier Country Primary Key field]),
“No country codeâ€, [country field])

If you have multiple countries for each supplier -- now that I look at
your structure, I suppose that is why they are seperated -- and you
want to have a comma list, here is a function you can use to do that
(this code goes into a general module):

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06

'NEEDS REFERENCE
'Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~

pTablename As String --> "[Supplier Country]"
pIDFieldname As String --> "[Supplier Code Fieldname]"
pTextFieldname As String --> "[country_Fieldname]"
pValueID As Long --> [Supplier Code]
Optional pWhere As String --> if you want to further specify records
within a matching Supplier Code, if not --> just put comma to skip
argument if you are specifying optional arguments after this
Optional pDeli As String -- optional if you want something in
addition to space around each value -- a comma is used is nothing is
specified
Optional pNoValue as string --> what to return if there are no records


'~~~~~~~~~~~~~~~~

so, in a query, here is what you would do:

SuppCountry: LoopAndCombine(
"[Supplier Country]",
"[Supplier Code Fieldname]",
"[country_Fieldname]",
[Supplier Code Fieldname],
,
,
"No Country Code")

If your Supplier Code Field is not a long integer, you will need to
adjust the data types of the function parameter pValueID accordingly --
as well as add the proper delimiter to the string that is built.

For instance, if the Supplier Code Field is a string...

change
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
to
mAllValues = mAllValues _
& " '" & r(pTextFieldname) & "'" & mValueDeli




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Jamie,

thanks for pointing out the multiple value possibility ... I just
answered the question asked without paying much attention to what the
data was and why it was being done ...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

make a new query

add tables --> [Supplier Master] and [Supplier Country]

link on [Supplier Code]

right-click on the link-line
choose to see ALL fields in [Supplier Master] and just those records in
[Supplier Country] where they match

make the query an Update Query

The equivalent standard SQL UPDATE syntax (for which Access/Jet is
non-compliant) is something like:

UPDATE <table>
SET <column> = (<scalar subquery>)
...

'Scalar' is database jargon for 'single value'.

Consider these tables:

TableA:
(1, NULL)

TableB:
(1, 'USA')
(1, 'UK')

To relate the above to the OP's spec, consider that the supplier trades
in more than one country; a reasonable assumption given that 1:1
relationships are usually modelled by one table rather than two.

To update the second column in TableA, as per your description you
would join to TableB using the first column. Which value should the
UPDATE use, 'USA' or 'UK'?

The result should be undetermined, hence the requirement for a scalar
subquery in the standards, yet Access/Jet is happy to pick one. Do you
know which one Access/Jet will pick? Cheery <g> in advance but I think
that if you do know you should explain this in your reply to the OP; if
you don't know, perhaps you should not be suggesting such syntax at
all.

Jamie.

 

[strive4peace]

Hi Ananth

correction:

this

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

should say this:

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

... air-code


'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hi Ananth,

Instead of updating country in one table when you already have it stored
in another, why not just link to that information whenever you need it?
When there are null values (Suppliers that don't have a record in
[Supplier Country], you could do something like this in your query:

field --> SuppCountry: IIF(
Isnull([Supplier Country Primary Key field]),
“No country codeâ€, [country field])

If you have multiple countries for each supplier -- now that I look at
your structure, I suppose that is why they are seperated -- and you
want to have a comma list, here is a function you can use to do that
(this code goes into a general module):

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06

'NEEDS REFERENCE
'Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~

pTablename As String --> "[Supplier Country]"
pIDFieldname As String --> "[Supplier Code Fieldname]"
pTextFieldname As String --> "[country_Fieldname]"
pValueID As Long --> [Supplier Code]
Optional pWhere As String --> if you want to further specify records
within a matching Supplier Code, if not --> just put comma to skip
argument if you are specifying optional arguments after this
Optional pDeli As String -- optional if you want something in
addition to space around each value -- a comma is used is nothing is
specified
Optional pNoValue as string --> what to return if there are no records


'~~~~~~~~~~~~~~~~

so, in a query, here is what you would do:

SuppCountry: LoopAndCombine(
"[Supplier Country]",
"[Supplier Code Fieldname]",
"[country_Fieldname]",
[Supplier Code Fieldname],
,
,
"No Country Code")

If your Supplier Code Field is not a long integer, you will need to
adjust the data types of the function parameter pValueID accordingly --
as well as add the proper delimiter to the string that is built.

For instance, if the Supplier Code Field is a string...

change
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
to
mAllValues = mAllValues _
& " '" & r(pTextFieldname) & "'" & mValueDeli




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field
in Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl

 

[Jamie Collins]

"But can it not be done in a single query."

I have assumed that each supplier has just one country; I should have
stated that -- if this is not right, this changes things...

I think this needs three UPDATEs:

UPDATE
SupplierMaster
LEFT OUTER JOIN
SupplierCountry AS C1
ON SupplierMaster.SupplierCode = C1.SupplierCode
SET SupplierMaster.CountryField = C1.CountryCode
WHERE 1 = (
SELECT COUNT(*)
FROM SupplierCountry AS C2
WHERE SupplierMaster.SupplierCode = C2.SupplierCode
);

UPDATE
SupplierMaster
LEFT OUTER JOIN
SupplierCountry AS C1
ON SupplierMaster.SupplierCode = C1.SupplierCode
SET SupplierMaster.CountryField = '{{multiple country codes}}'
WHERE 1 < (
SELECT COUNT(*)
FROM SupplierCountry AS C2
WHERE SupplierMaster.SupplierCode = C2.SupplierCode
);

UPDATE
SupplierMaster
LEFT OUTER JOIN
SupplierCountry AS C1
ON SupplierMaster.SupplierCode = C1.SupplierCode
SET SupplierMaster.CountryField = '{{no country code}}'
WHERE 0 = (
SELECT COUNT(*)
FROM SupplierCountry AS C2
WHERE SupplierMaster.SupplierCode = C2.SupplierCode
);

I can't seem to come up with something that does it in one go: I keep
getting the dreaded 'Operation must use an updateable query'. Here's my
best attempt:

UPDATE
SupplierMaster
INNER JOIN
(
SELECT DISTINCT DT1.SupplierCode,
SWITCH(
country_tally = 0, '{{no country code}}',
country_tally = 1, C2.CountryCode,
country_tally > 1, '{{mutiple countries}}'
) AS CountryField
FROM
(
SELECT M1.SupplierCode, COUNT(C1.CountryCode) AS country_tally
FROM SupplierMaster AS M1
LEFT OUTER JOIN SupplierCountry AS C1
ON M1.SupplierCode = C1.SupplierCode
GROUP BY M1.SupplierCode
) AS DT1
LEFT OUTER JOIN
SupplierCountry AS C2
ON DT1.SupplierCode = C2.SupplierCode
) AS DT2
ON SupplierMaster.SupplierCode = DT2.SupplierCode
SET SupplierMaster.CountryField = DT2.CountryField;

Can anyone do this in one...?

Jamie.

--

 

[Jamie Collins]

thanks for pointing out the multiple value possibility ... I just
answered the question asked without paying much attention to what the
data was and why it was being done ...

I'll answer my own question, then, with a quick demo (in accordance
with Access parlance I'll refer to this as 'The Update bug' <g>):

Sub update_bug()
Const CONN_STRING = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"

Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create CONN_STRING
Set cat.ActiveConnection = Nothing

Dim conn
Set conn = CreateObject("ADODB.Connection")
With conn
.ConnectionString = CONN_STRING
.Open

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test1 (" & _
" col1 INTEGER NOT NULL," & _
" col2 VARCHAR(20) NOT NULL);"
.Execute _
"INSERT INTO Test1 (col1," & _
" col2) VALUES (1, '{{NONE}}');"

.Execute _
"CREATE TABLE Test2 (" & _
" col1 INTEGER NOT NULL," & _
" col2 VARCHAR(20) NOT NULL);"

Const INSERT_UK = _
"INSERT INTO Test2 (col1," & _
" col2) VALUES (1, 'UK');"

Const INSERT_USA = _
"INSERT INTO Test2 (col1," & _
" col2) VALUES (1, 'USA');"

.Execute INSERT_UK
.Execute INSERT_USA

Const SELECT_QUERY = _
"SELECT Test1.col1, Test1.col2," & _
" Test2.col1, Test2.col2" & _
" FROM Test1 INNER JOIN Test2" & _
" ON Test1.col1 = Test2.col1;"

Dim rs
Set rs = .Execute(SELECT_QUERY)
MsgBox _
"On UPDATE, will (NONE) be replaced" & _
" by 'UK' or 'USA'...?" & vbCr & vbCr & _
rs.GetString

Const UPDATE_QUERY = _
"UPDATE Test1 INNER JOIN Test2" & _
" ON Test1.col1 = Test2.col1" & _
" SET Test1.col2 = Test2.col2;"

.Execute UPDATE_QUERY

rs.Requery
MsgBox _
"UPDATE used 'UK':" & vbCr & vbCr & _
rs.GetString & vbCr & _
"because 'UK' was INSERTed first." & _
vbCr & vbCr & _
"To test, reverse the INSERT order..."

.Execute _
"DELETE FROM Test2;"

.Execute INSERT_USA
.Execute INSERT_UK

.Execute UPDATE_QUERY

rs.Requery
MsgBox _
"UPDATE used 'USA' this time:" & _
vbCr & vbCr & _
rs.GetString & vbCr & _
"because 'USA' was INSERTed first." & _
vbCr & vbCr & _
"But what happens to the *same* data" & _
" when a PRIMARY KEY is added...?"

.Execute _
"UPDATE Test1" & _
" SET col2 = '{{NONE}}';"

rs.Close

.Execute _
"ALTER TABLE Test2" & _
" ADD CONSTRAINT PK__Test2" & _
" PRIMARY KEY (col2, col1);"

.Close
End With

' Compact the file
Dim jeng
Set jeng = CreateObject("JRO.JetEngine")

Const CONN_COMPACT = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe2.mdb"

jeng.CompactDatabase _
CONN_STRING, CONN_COMPACT

Set conn = CreateObject("ADODB.Connection")
With conn
.ConnectionString = CONN_COMPACT
.Open

.Execute UPDATE_QUERY

Set rs = .Execute(SELECT_QUERY)
MsgBox _
"Although the INSERT order did not change," & _
" the UPDATE used 'UK' this time:" & _
vbCr & vbCr & _
rs.GetString & vbCr & _
"because of the PRIMARY KEY."

.Close
End With
End Sub

Jamie.

--

 

[Guest]

Thanks ver much for all the help.

Hi Ananth

correction:

this

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

should say this:

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

... air-code


'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hi Ananth,

Instead of updating country in one table when you already have it stored
in another, why not just link to that information whenever you need it?
When there are null values (Suppliers that don't have a record in
[Supplier Country], you could do something like this in your query:

field --> SuppCountry: IIF(
Isnull([Supplier Country Primary Key field]),
“No country codeâ€, [country field])

If you have multiple countries for each supplier -- now that I look at
your structure, I suppose that is why they are seperated -- and you
want to have a comma list, here is a function you can use to do that
(this code goes into a general module):

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06

'NEEDS REFERENCE
'Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~

pTablename As String --> "[Supplier Country]"
pIDFieldname As String --> "[Supplier Code Fieldname]"
pTextFieldname As String --> "[country_Fieldname]"
pValueID As Long --> [Supplier Code]
Optional pWhere As String --> if you want to further specify records
within a matching Supplier Code, if not --> just put comma to skip
argument if you are specifying optional arguments after this
Optional pDeli As String -- optional if you want something in
addition to space around each value -- a comma is used is nothing is
specified
Optional pNoValue as string --> what to return if there are no records


'~~~~~~~~~~~~~~~~

so, in a query, here is what you would do:

SuppCountry: LoopAndCombine(
"[Supplier Country]",
"[Supplier Code Fieldname]",
"[country_Fieldname]",
[Supplier Code Fieldname],
,
,
"No Country Code")

If your Supplier Code Field is not a long integer, you will need to
adjust the data types of the function parameter pValueID accordingly --
as well as add the proper delimiter to the string that is built.

For instance, if the Supplier Code Field is a string...

change
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
to
mAllValues = mAllValues _
& " '" & r(pTextFieldname) & "'" & mValueDeli




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field
in Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl

 

[strive4peace]

you're welcome, Ananth happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks ver much for all the help.

Hi Ananth

correction:

this

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

should say this:

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

... air-code


'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hi Ananth,

Instead of updating country in one table when you already have it stored
in another, why not just link to that information whenever you need it?
When there are null values (Suppliers that don't have a record in
[Supplier Country], you could do something like this in your query:

field --> SuppCountry: IIF(
Isnull([Supplier Country Primary Key field]),
“No country codeâ€, [country field])

If you have multiple countries for each supplier -- now that I look at
your structure, I suppose that is why they are seperated -- and you
want to have a comma list, here is a function you can use to do that
(this code goes into a general module):

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06

'NEEDS REFERENCE
'Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(pValueDeli) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~

pTablename As String --> "[Supplier Country]"
pIDFieldname As String --> "[Supplier Code Fieldname]"
pTextFieldname As String --> "[country_Fieldname]"
pValueID As Long --> [Supplier Code]
Optional pWhere As String --> if you want to further specify records
within a matching Supplier Code, if not --> just put comma to skip
argument if you are specifying optional arguments after this
Optional pDeli As String -- optional if you want something in
addition to space around each value -- a comma is used is nothing is
specified
Optional pNoValue as string --> what to return if there are no records


'~~~~~~~~~~~~~~~~

so, in a query, here is what you would do:

SuppCountry: LoopAndCombine(
"[Supplier Country]",
"[Supplier Code Fieldname]",
"[country_Fieldname]",
[Supplier Code Fieldname],
,
,
"No Country Code")

If your Supplier Code Field is not a long integer, you will need to
adjust the data types of the function parameter pValueID accordingly --
as well as add the proper delimiter to the string that is built.

For instance, if the Supplier Code Field is a string...

change
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
to
mAllValues = mAllValues _
& " '" & r(pTextFieldname) & "'" & mValueDeli




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Ananth wrote:
I am comparing 2 Tables.

Table-A=Supplier Master
Table-B=Supplier Country

Both the Tables are linked to Supplier Code.

I am updating the Country Field in Table-A

When there is no matching Supplier code in Table B, the country Field
in Table-A is to be replaced with “No country codeâ€.

I tried using IIF, is null etc, but no success

I would consider any help a great one, at this moment pl