Update Query Problem

  • Thread starter Thread starter BBAL20
  • Start date Start date
B

BBAL20

I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
 
It all starts with the data ... and I don't have a very clear picture of
your data.

It would be an unusual data design that made the value in a single record
dependant on the values in multiple other records, which is what it sounds
like you are describing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Call the update query from the same sub in your code like this:
------------------------
Dim strSQL as String
Dim db as DAO.Database

Set db = dbengine(0)(0)

If DCount ("[CHD_ACCOUNT_NUMBER]", "[Daily-Scrubbed]", "[CHD_ACCOUNT_NUMBER]
= " & Me.Prev_Ref_NO & ") = 0 Then
strSQL = "SavedQueryName"
db.Execute strSQL, dbFailOnError
End If
Set db = Nothing
--------------------------
If Prev_Ref_NO is a text field, change
" & Me.Prev_Ref_NO & "
to
""" & Me.Prev_Ref_NO & """

Change your saved query like this:
In the UpdateTo: row for CHD_ACCOUNT_NUMBER change it to
Forms!TheFormName![Prev_Ref_NO]
assuming that Prev_Ref_NO is the name of the control on your form with the
value for Prev_Ref_NO

Note: replace SavedQueryName with your query, and TheFormName with your
form's name.

Explanation: This 'If DCount' part of the code does a check to see if
Prev_Ref_NO is found already in the table. If the number is not found, it
will run the update query.

Jeanette Cunningham
 
What is it suppose to do if not all records of [Daily_Scrubbed]![Prev_Ref_NO]
are the same?
 
If it doesn't find the value in that column then it is supposed to
mark/update the Active column with a "Yes". If it does find a match then it
should mark/update the record with a "No". I'm basically trying to take one
record at a time in the CHD_ACCOUNT_NUMBER column and match it against every
record in the entire Prev_Ref_NO column, but Access is taking the starting
record and only looking for matches in the same record, not the entire column.

KARL DEWEY said:
What is it suppose to do if not all records of [Daily_Scrubbed]![Prev_Ref_NO]
are the same?
--
KARL DEWEY
Build a little - Test a little


BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
 
I know. It's unusual to me as well, but that is the way this file needs to be
scrubbed. It needs to look at each value row by row in the CHD_ACCOUNT_NUMBER
field and LookUp the entire Prev_Ref_NO column for a match. If it doesn't
find a match in that column for the row or record then it needs to update the
Active field with a "Yes" I hope that helps.

Jeff Boyce said:
It all starts with the data ... and I don't have a very clear picture of
your data.

It would be an unusual data design that made the value in a single record
dependant on the values in multiple other records, which is what it sounds
like you are describing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help
and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
 
My question was what if all records of [Daily_Scrubbed]![Prev_Ref_NO] are NOT
the same?

--
KARL DEWEY
Build a little - Test a little


BBAL20 said:
If it doesn't find the value in that column then it is supposed to
mark/update the Active column with a "Yes". If it does find a match then it
should mark/update the record with a "No". I'm basically trying to take one
record at a time in the CHD_ACCOUNT_NUMBER column and match it against every
record in the entire Prev_Ref_NO column, but Access is taking the starting
record and only looking for matches in the same record, not the entire column.

KARL DEWEY said:
What is it suppose to do if not all records of [Daily_Scrubbed]![Prev_Ref_NO]
are the same?
--
KARL DEWEY
Build a little - Test a little


BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
Click to expand...
 
This is helpful. Thank you. My query is not attached to a form at all. Can
you show me how to write the query by itself. Should it be a select query or
a update query. I understand the VB just not the query portion to be inserted
in the VB code because it's not attached to a form.

Jeanette Cunningham said:
Call the update query from the same sub in your code like this:
------------------------
Dim strSQL as String
Dim db as DAO.Database

Set db = dbengine(0)(0)

If DCount ("[CHD_ACCOUNT_NUMBER]", "[Daily-Scrubbed]", "[CHD_ACCOUNT_NUMBER]
= " & Me.Prev_Ref_NO & ") = 0 Then
strSQL = "SavedQueryName"
db.Execute strSQL, dbFailOnError
End If
Set db = Nothing
--------------------------
If Prev_Ref_NO is a text field, change
" & Me.Prev_Ref_NO & "
to
""" & Me.Prev_Ref_NO & """

Change your saved query like this:
In the UpdateTo: row for CHD_ACCOUNT_NUMBER change it to
Forms!TheFormName![Prev_Ref_NO]
assuming that Prev_Ref_NO is the name of the control on your form with the
value for Prev_Ref_NO

Note: replace SavedQueryName with your query, and TheFormName with your
form's name.

Explanation: This 'If DCount' part of the code does a check to see if
Prev_Ref_NO is found already in the table. If the number is not found, it
will run the update query.

Jeanette Cunningham

BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help
and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
 
If you are not doing this from a form, why does your query use form
references like [Daily_Scrubbed]![CHD_ACCOUNT_NUMBER] ?
If you are just using a query and Daily_Scrubbed is a table, first find all
duplicate records for Prev_Ref_NO
You create a find Duplicates query. On the menu, Query >>Find Duplicates
Query Wizard
After you have found all the duplicates, you can select only the records
that are not duplicates and set their Active value to Yes by creating an
update query.

Jeanette Cunningham
 
Another way to to this:
Create a query with the Primary Key and Prev_Ref_NO fields.
Make the query a totals query by right clicking on the query grid and
choosing totals.
For the primary key the totals row will be Group By
For Prev_Ref_NO the totals row will be Count
In the criteria row for Prev_Ref_NO, put 1
Switch the query to datasheet view, you will see only the rows where
Prev_Ref_NO is unique.

Back in design view, clear the checkbox for show for Prev_Ref_NO field
Switch to datsheet view, and you should see a list of the primary key for
all records that have a unique value for Prev_Ref_NO.

Save this query as qryUniqueRefNo

Now create a new query.
Use the Daily_Scrubbed table and qryUniqueRefNo
Create a join on the primary key field in the query
Drag the field Active to the query grid.
Make the query an update query
In the Update to row put "Yes"
Switch to datasheet view and check that only the records with unique values
for Prev_Ref_NO are showing.
Run the update query.

If you need to do this often, save the update query.

Jeanette Cunningham
 
I'm not trying to find duplicates at all. It's hard to explain, but I'm
trying to determine and update the most active account. Unfortunately, the
data has been delivered to me in very bad shape and I'm just trying to clean
it up. As I explained earlier, I need Access to lookup up the value in the
first column/row ([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]) and then cross
reference that vlaue in the entire [Daily_Scrubbed]![Prev_Ref_NO] column for
a match. Then it would go to the next record/row in the
[Daily_Scrubbed]![CHD_ACCOUNT_NUMBER] column and then cross reference that
value in the entire [Daily_Scrubbed]![Prev_Ref_NO] column. I need access to
do the same process over and over again until the entire table has been
completed. Does that make sense?

Jeanette Cunningham said:
If you are not doing this from a form, why does your query use form
references like [Daily_Scrubbed]![CHD_ACCOUNT_NUMBER] ?
If you are just using a query and Daily_Scrubbed is a table, first find all
duplicate records for Prev_Ref_NO
You create a find Duplicates query. On the menu, Query >>Find Duplicates
Query Wizard
After you have found all the duplicates, you can select only the records
that are not duplicates and set their Active value to Yes by creating an
update query.

Jeanette Cunningham


BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking if
both columns are equal and then marking "Yes". I need Access to check the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match. I
hope that's not confusing. I've included my Update query below. Any help
and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
 
No you are just checking for duplicate values for Prev_Ref_NO.
If you use the method in my most recent post, you should be able to achieve
this.
Here it is again:

Create a query with the Primary Key and Prev_Ref_NO fields.
Make the query a totals query by right clicking on the query grid and
choosing totals.
For the primary key the totals row will be Group By
For Prev_Ref_NO the totals row will be Count
In the criteria row for Prev_Ref_NO, put 1
Switch the query to datasheet view, you will see only the rows where
Prev_Ref_NO is unique.

Back in design view, clear the checkbox for show for Prev_Ref_NO field
Switch to datsheet view, and you should see a list of the primary key for
all records that have a unique value for Prev_Ref_NO.

Save this query as qryUniqueRefNo

Now create a new query.
Use the Daily_Scrubbed table and qryUniqueRefNo
Create a join on the primary key field in the query
Drag the field Active to the query grid.
Make the query an update query
In the Update to row put "Yes"
Switch to datasheet view and check that only the records with unique values
for Prev_Ref_NO are showing.
Run the update query.

If you need to do this often, save the update query.

Jeanette Cunningham

BBAL20 said:
I'm not trying to find duplicates at all. It's hard to explain, but I'm
trying to determine and update the most active account. Unfortunately, the
data has been delivered to me in very bad shape and I'm just trying to
clean
it up. As I explained earlier, I need Access to lookup up the value in the
first column/row ([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]) and then cross
reference that vlaue in the entire [Daily_Scrubbed]![Prev_Ref_NO] column
for
a match. Then it would go to the next record/row in the
[Daily_Scrubbed]![CHD_ACCOUNT_NUMBER] column and then cross reference that
value in the entire [Daily_Scrubbed]![Prev_Ref_NO] column. I need access
to
do the same process over and over again until the entire table has been
completed. Does that make sense?

Jeanette Cunningham said:
If you are not doing this from a form, why does your query use form
references like [Daily_Scrubbed]![CHD_ACCOUNT_NUMBER] ?
If you are just using a query and Daily_Scrubbed is a table, first find
all
duplicate records for Prev_Ref_NO
You create a find Duplicates query. On the menu, Query >>Find Duplicates
Query Wizard
After you have found all the duplicates, you can select only the records
that are not duplicates and set their Active value to Yes by creating an
update query.

Jeanette Cunningham


BBAL20 said:
I'm trying to update a Yes/No column based on the CHD_ACCOUNT_NUMBER
field
not being equal (<>) to the Prev_Ref_NO field. I think I know what the
problem is. Access is going row by row or record by record and checking
if
both columns are equal and then marking "Yes". I need Access to check
the
CHD_ACCOUNT_NUMBER field for the first value and reference the entire
Prev_Ref_NO column rather than just that row or record to find a match.
I
hope that's not confusing. I've included my Update query below. Any
help
and
support is greatly appreciated.

UPDATE Daily_Scrubbed SET Daily_Scrubbed.Active = Yes
WHERE
(([Daily_Scrubbed]![CHD_ACCOUNT_NUMBER]<>[Daily_Scrubbed]![Prev_Ref_NO]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top