Autopopulate date in blank fields

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Hi!

I have a table with two fields (Data and Check). The field "Data" has 10 records.

John
Mike
Julie
Julie
Tom
Harry
Harry
Harry
****
Vicky

The field "Check" has all blank records.

Can we write a Query/SQL Statement/VBA Code to check the names in the "Data" field and populate "YES" in the check field?

For instance:

Record1 of Field1 "Data" (John) is NOT EQUAL TO Record2 of Field1 "Data" (Mike) therefore, Record1 of Field2 "Check" should still be blank.

but

Record3 of Field1 "Data" (Julie) is EQUAL TO Record4 of Field1 "Data" (Julie) therefore, Record3 of Field2 "Check" should contain the value "YES".

Can this be done?

Maxi
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Hi Maxi,
How are the records sorted to enable the comparison? The example you gave was not in alphabetical order, and what result would you want for names that appear more than once but are not in sequence? Also, where you have a name appearing twice, do you want the 2nd field "checked" for both matching records? If so, I think this could be done with some queries - one query to group & count the names & a second query to update the check box to "yes" for all records from the first query that have a count greater than 1. It might take a bit of setting up but it shold be doable (is that a word?)
HTH, Andrew. :)
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Let me change my question a bit.

I have two tables "Data" and "Check"

Here is the content of the tables:



"Data" "Check"

Field1 Field1 Field2
Jack Jill Yes
Jill Maxi Yes
Dino John Yes
Tomy Rani No
Maxi Tony Yes
Jane Romy No
John Jane Yes
Gems
Rick
Pony
****
Tony



I want the Yes/No to be populated automatically. What SQL statement/VBA code can I write to check if the name in field1 of the "Check" table appears in field1 of "Data" table and accordingly put Yes/No in field2 of the "Check"table.

The records are not sorted and not in alphabetical order. Even if a name appear more than once and not in sequence, just put YES

Maxi
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Hi Maxi

Thanks for the info - now I understand. This can be done with an update query.

In your "check" table, set "field2" to be the yes/no data type and have the default value as No.

Create a new query, add the tables "data" and "check", link "field1" from "data" with "field1" from "check", change the link type to include all records from "check" and only those records from "data" where the joined fields are equal. On the menu bar Click Query -> Update Query. Add the following fields to the query : "field2" from "check" and "field1" from "data". Under the "field2" field, in the "Update To" box, enter the value "Yes" without the quotes. Under the "field1" field, in the "Criteria" box, enter "Is Not Null" without the quotes.

Save and run the query.

The query SQL looks like this (please note that given the variable names were the same I prefixed the variable names with the table name to avoid confusion, be sure to use your actual variable names) :

UPDATE data RIGHT JOIN [check] ON data.[data-field1] = check.[check-field1] SET [check].[check-field2] = Yes
WHERE (((data.[data-field1]) Is Not Null));

HTH, Andrew. :)
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Hi! Andrew,

Can I still run some kind of an update query to do the similar task if my Check table is in another database?

Maxi
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Hi Maxi
This shouldn't be a problem provided you can access the other database and link the table(s). I tested it with both tables linked in another database and the update query still worked.
Andrew
 

Forum statistics

Threads
1,147,621
Messages
5,742,180
Members
423,709
Latest member
AMacL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top