Autopopulate date in blank fields

[mac_see]

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

 

[Andrew Fergus]

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]

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]

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]

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]

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