update yes / no field in table based on another table

I am wanting to update a yes/no field in one table based on another table.

If the name is in both tables to put a "yes" value in the field
If the name is in one table but not the other to put a "no" value in the field

For example:

Table 1:  Name
Table 2: Name
Check box = yes


Table 1: Null (Name not in table)
Table 2:  Name
check box = no
0
Utf
3/3/2008 10:55:01 PM
access 16762 articles. 3 followers. Follow

3 Replies
967 Views

Similar Articles

[PageSpeed] 38

On Mon, 3 Mar 2008 14:55:01 -0800, Bryan <Bryan@discussions.microsoft.com>
wrote:

>I am wanting to update a yes/no field in one table based on another table.
>
>If the name is in both tables to put a "yes" value in the field
>If the name is in one table but not the other to put a "no" value in the field
>
>For example:
>
>Table 1:  Name
>Table 2: Name
>Check box = yes
>
>
>Table 1: Null (Name not in table)
>Table 2:  Name
>check box = no

I would be very strongly inclined to say that this field *should not exist*.
Its value depends on the values of two other values, either of which could be
edited or deleted at any time.

You can instead determine it at any time in a Query joining the two tables, or
using DLookUp.
-- 
             John W. Vinson [MVP]
0
John
3/4/2008 3:16:04 AM
The problem is that I need to update table 2 based on the names in table 1. 
The yes/no value is linked to a delete query so that the names in each table 
will alway match = if table 2 has a no value then that row is deleted from 
the table.

"Bryan" wrote:

> I am wanting to update a yes/no field in one table based on another table.
> 
> If the name is in both tables to put a "yes" value in the field
> If the name is in one table but not the other to put a "no" value in the field
> 
> For example:
> 
> Table 1:  Name
> Table 2: Name
> Check box = yes
> 
> 
> Table 1: Null (Name not in table)
> Table 2:  Name
> check box = no
0
Utf
3/4/2008 2:25:04 PM
On Tue, 4 Mar 2008 06:25:04 -0800, Bryan <Bryan@discussions.microsoft.com>
wrote:

>The problem is that I need to update table 2 based on the names in table 1. 
>The yes/no value is linked to a delete query so that the names in each table 
>will alway match = if table 2 has a no value then that row is deleted from 
>the table.

No. You do NOT NEED the yes/no field in order to run the delete query.

You can base the Delete query on a Join, or use a Subquery to identifiy which
names exist in the other table.

To delete records in Table1 where the name value does not exist in Table2, 

DELETE * FROM Table1
WHERE NOT EXISTS
(SELECT Table2.namefield FROM table2 WHERE table2.namefield =
table1.namefield);


I do hope these aren't people's names though - names are NOT unique, and you
might have two or more people named (say) Jim Smith.
-- 
             John W. Vinson [MVP]
0
John
3/4/2008 4:34:51 PM
Reply:

Similar Artilces:

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

Update Stock Price
I own Money 2002 and I've periodically done manual and online updates, but is there a way to to an online update for a stocks price that will update the historical price not only for the current day, but for any days I missed in between. Thanks in Advance for any help that can be offered. No. "Nick" <nmoshou@yahoo.com> wrote in message news:039601c398ed$e8473e00$a501280a@phx.gbl... > I own Money 2002 and I've periodically done manual and > online updates, but is there a way to to an online update > for a stocks price that will update the historical pric...

link custom field to resource names
hello, i have created a custom field "Primary contact" is there a way to link it to the list of Resource names? maybe turn it into a drop down list? thanks "greg" <greg@nospam.com> wrote in message news:%23hSQ6HJsEHA.3396@tk2msftngp13.phx.gbl... > hello, > i have created a custom field "Primary contact" > is there a way to link it to the list of Resource names? > maybe turn it into a drop down list? > > thanks > > ...

Bizarre Sales Tax Table Issue
I have four taxes set up in my system, and confirmed in the TAX table in the database: ID Description Rate 1 Sales Tax 0% 2 VAT 15% 3 VAT-EX 0% 4 VAT-ZR 0% If I create a new item, and select VAT, then check the ITEM table, the TaxID = 3 for that item. You would think that the system would apply the VAT-EX taxation on the item. However, when i do a transaction, it puts the 15% rate on the item and everything seems work out okay, including the reports, which show that VAT was collected. The TransactionEntry table has the proper sales ...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

count a pair of numbers in row in a table
Hello, my question is: we have the following table: 34 29 13 15 7 15 8 40 11 24 13 6 8 21 38 9 17 23 1 4 22 38 42 37 16 1 18 11 37 41 5 42 18 33 45 9 1 21 41 15 41 1 27 23 42 23 29 7 38 18 42 12 26 34 36 and this one in another sheet 1 2 3 1 2 3 I want to fill the second table with the sum of how many times the numbers if each row and column appear in the same row in the first table. for example: how many time the numbers 2 and 3 appear together in the same row on the first table Nik, Assume t...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

Updated Office, now I'm no longer Registered
Help. I downloaded the Offixe X updates, and went to open Microsoft Word, and all of a sudden, its as if I was installing a brand new product, asking for my information, and product key (which of course, I can't find). Has anyone experienced this? Or, does anyone know if Macs store any kind of registry information? I appreciate any feedback! (other than "losing your registration key was reeeeeally smart!" :)) Thanks so much. Lisette On 12/23/06 11:58 AM, in article 1166903892.442462.51840@a3g2000cwd.googlegroups.com, "lisettea@mac.com" <lisettea@mac.com> wrot...

Database Field Sizes.
I have two small problems, firstly Can the Matrix Description field size be increased to 40/60 chars at present too small. or is there a bigger field i can use eg the extended description field size increased. We need a way that when a saleperson inputs a sale order at the POS and the item needs to be special order the following information is automatically passed to the purchased order for the supplier. 1, supplier Ref number for item. 2, Supplier Colour (four suppliers have 56 colours each) 3, Size of Dress etc 4, delivery date required 5, Order confirmation date and number from suppli...

Money 2007 fails additional update
I am trying to use Money 2007 Premium on my new computer (Windows XP SP2) and it installs fine, but when I start Money it notifies me that an additional update is required. It looks like it downloads the files properly but fails the add'l install. I have tried the following: uninstall and install again turn off all virus protection (Panda) firewalls, etc. download directly from the web None of these have helped. I saw one article on this problem that said to remove a Core File, but I can not find it on my computer. Please help. -- Josie In microsoft.public.money, Josie wrote...

Designing a report based on a crosstabe
Hello! Hope someone can help cause this is getting into my nerves... I have a crosstabe that I run with a parameter for "date of sales". Date of Sale is my column and the results are showed per month. So I run the query and set the dates from 01/jan/07 to 31/july/07 and the query returns the 7 months I want (mm/yy). How could I design a report that that would run with various parameters? From what I understood so far, the field in the report must necessarily have the name of the result achieved (ex. 01/07, 02/07, 03/07...). Hope I made myself understandable... Cheers everyon...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

Automatic Update application ignores permissions
I have a managed account on my computer - I've allowed the user to launch all Office applications except the Updater, Remove Office, and the Handheld Sync Installer. However, if the user clicks "Check for updates" in the Help menu in Word, the Updater application launches, and the AU daemon is put in the list of applications to startup on login. I guess this is the place to report such a bug - does anyone know how to prevent the user from launching the Updater application in this way? Does the user account have the "Allow Supporting Programs" box in the allow/disall...

"files based on content, not file extension" option issue
Hi, I met a strange question. When I disable "files based on content, not file extension" option in IE browser (IE -->Tools Options-->Security-->Internet zone-->Custom level --> "files based on content, not file extension" disable). When I used IE to access ftp://ftp.microsoft.com via. proxy, such as: ccproxy..etc. IE browser shows HTML source code instead of normal html webpage. Could you take a look? Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2; Vista SP1; Vista 64-bit SP2; Win7; Win7 64-bit) as well...

mySQL datetime field reports as Long Integer with ODBC
I'm using Access 2003 as a reporting tool against a mySQL database, connecting with the mySQL ODBC driver 3.51.17. The ODBC driver reports every datetime field as a Long Integer in Access. This makes date operations problematic. I notice an interesting post in this forum from 2005 that would work fine ("Convert UNIX time to windows general date"), but using a function with every row seems like an unnecessary performance hit. Also, it seems counter-intuitive that the driver doesn't recognize the correct field type. The problem looks suspiciously like the mySQL bug rep...

send as another user
i gave one user right to access another user mailbox and enable "send on behalf" of him/her in exchange. back in outlook, when the user highlight the other user inbox and click on send...it still say his send from his name instead of the other user name? am i doing someting wrong? if so, can someone tell me how to make this thing work? thx in advance. "send on behalf" is NOT "send as" theyre 2 totally different things. send as is given via ADUC "vincentnyc" <vincentnyc@discussions.microsoft.com> wrote in message news:F2A6FD12-5080-47...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

Readonly a linked table
I have a table that gets generated every month, and we use as a source of data for other databases. We link this, but I was wondering if there is a way to make that linked table read only. Any ideas? Thanks so much, Chris M. hi Chris, mcescher wrote: > I have a table that gets generated every month, and we use as a source > of data for other databases. We link this, but I was wondering if > there is a way to make that linked table read only. Either make the back-end read-only or use a pass-through query instead of a linked table. mfG --> stefan <-- ...

Text fields in report writer
I have a modified report dictionary, when I go into a report layout and enter a text field, when I tab off the field what I wrote is converted to something totally different. Has anyone seen this before? Reports that I have been using for months suddenly the text fields print something totally different. ????? Any suggestions would be helpful. Thank you! Hi J. We use a shared dictionary located on our server, and I find text fields do not "travel" well - ie. I often lose info if I export a report out of the dictionary to work on it. What I do is make a copy of the enti...

Using tables created in 2003 IN 2007
My office has recently upgraded to 2007. I enjoy new features such as the ability to highlight a few words within the table without the ENTIRE table's font changing; unfortunately, this only works in tables I have created since the upgrade. My old tables that were brought over from 2003 do not have this capability. Is there an add-on out there? I do not have to resort to re-typing and creating all new tables. PS. Copy and pasting into a new table does not work. Convertting the file using the office button does not work. Help? please? I think you talking about what is called...

SumIf
Hi =SUMIF(A6:A2000,"(left(a6:a2000,4))=(left($K$14,4))",F6:F2000) I want to create a sub total of all values in column F, at certain subtotal cells in column F where the the first 4 (or other to be set)characters of a code in cells a6 to a2000 match the first 4 characters in cell$K$14 (or other cell to be set). The above doesn't seem to work, is it possible? am I missing something? Any help would be greatly appreciated. Ritchi Try this array* formula: =SUM(IF(LEFT(A$6:A$2000,4)=LEFT($K$14,4),F$6:F$2000,0)) * As this is an array formula, then once you have typed it in (or s...

Counting number of rows based on mutiple criteria
Does anyone know how to count the number of rows based on mutiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked. Col 1 Col2 Col3 A Yes No B Yes Yes C No No D No Yes E Yes Yes Your help is greatly appreciated! Hi, =SUMPRODUCT((B1:B10=&qu...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Auto Filling fields in form
I am new at creating code and would like to have two fields populated when the record is reviewed. One is the user name and the other is the date reviewed. There is a form which contains the Member Name and ID number. I would like to add the two auto fill fields in the Parent form and have it fill in the subform fields for that particular member using a trigger of some sort. Any advice would be appreciated. Bev Access forms display data... Access tables store it. If you want to see data from a record in a table in your form, your controls in the form need to be poin...

Placing the results within a table
I have a form where I would add how many openings there are for each position. Once I get the total I can see it on my form but I am not able to transfer that total into my table. My code for this text box is =[VacancyQty1]+[VacancyQty2]+[VacancyQty3]+[VacancyQty4]+[VacancyQty5]+[VacancyQty6] it adds up the number from each of those fields. What I wanna do it to get that sum placed into my table. I have tried [TotalVacancy]=.... "TotalVacancy"=... ="TotalVacancy".... Please help me. I am at a lost. Please and Thank you, Hillary It is not correct to put the total i...