Update YN Query Another Table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update YN Query Another Table

Scott Crippen

3 years ago

I am almost embarrassed to admit being a beta tester for Microsoft Access 7.0 (Access 95) because I >SHOULD< be able to figure this out.  I have watched several tutorials and read as much as I think I can.  Still no luck as it seems most tutorials are focused on renaming multiple fields AND multiple lines.  I'm just trying to update one.  SO.... help please!

I have a table/form (Testing) where I queried data from one table (Master)  I query the Master table for all items which are marked as "not passed" or no checkbox.

From the testing Form/Table functionality tests (Outside links check) of some of the 40 or so fields occurs and each line item is marked pass/fail in a "passed" field if everything on that line works properly.  This really a checkbox but you get the idea.  If testing fails, a couple other forms are brought up later to make corrections.  This means ALL I need to do with the testing form is update the Master Table!

Basically, everything is in the master table already but unless it has passed, it is not visible to the user through any user queries.  This update query process I'm trying to do is preferred, rather than hunting down each line item to mark a single checkbox in a large table.

From the Testing table, I want to update the Master Table with Yes in the field named "passed".  If it has passed testing, and running the update query, then the item in the Master is accessible to the user.

I am TRYING to simply query for the items which have recently been marked as passing within the TESTING form/table and update to indicate "yes" or "true" in the "passed" field of the Master Table.  Items which have not yet passed remain in the testing table on a different re-query.

Here is the best (closest) query I have come up with.

Relationships between "passed" and "ID" fields linked in both tables.  The only links present.

Update Query shows:

Field: Passed
Table: Master
Update to: Master!Passed
Criteria False

Field: ID
Table: Testing
Update to: Master!Passed
Criteria

This returns all items which have not passed.  Changing the criteria to True returns nothing though looking at the table, there are several which have been marked as passed. I thought maybe I had reversed the source and update to areas so I tried that, without luck.

	Richard Rost @Reply 3 years ago
So let me see if I understand correctly... you have a table 1 with a bunch of y/n fields in it, and you want to run an update query such that if ANY of the items in a record in table 1 are marked no (failed) then set a single value in a related table 2 also to no (failed). Is that right?

Scott Crippen OP @Reply

3 years ago

I have a master table with several fiienames on each line item.  Each filename is for an mp3 file in a local folder.  Each line item has data for 4 to 6 filenames (for hyperlinks) AND a yes/no field named "passed".

I query the master table with criteria set for all items which are NO in the passed field. The results also return each of the filename fields and the (ID) Primary Key autonumber.  Results go into the testing table where the hyperlinks for each separate line item are verified as working correctly.  If they all work, I mark YES in the passed field of the testing table.  If they do not pass, I leave the "passed" field alone, or NO.  There is ONLY ONE "passed" field Y/N box used, not a Y/N box for each filename.  "Passed" YES means ALL filenames in the line item work.  Otherwise "passed" is NO.

Everything works fine to this point.

The issue is to simply add the YES in the passed field from the testing table, back to the original line items of the master table. As there may be several tested, there would be several which would update to the master table. This is the ONLY field which needs to be updated in the master table, but potentially could be several line items.

A subsequent re-query of the master table for not passed items, back to the testing table, would return reduced results, since there are less line items which are "not passed" in the master.  For "not passed" items in the testing table, I can fix the filenames elsewhere by making corrections in the folder or in another form and re-query to test again.

This is basically a long way to go to check for typos

	Scott Crippen OP @Reply 3 years ago
The error has been found. I discovered a loop with relationships. It now works as intended. This thread could be closed.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View