Update Union Query

[jweisman]

I am using Access 2000. The following is my table structure.

tblRegulation:
RegulationID (PK)
Issue
SubIssue1
SubIssue2
SubIssue3
SubIssue4
Comments
Other Fields

tblRegReviewed:
RegulationID (PK)
User (PK)
Reviewed
DateReviewed

tblUserPreferences
User
Issue

tblLocalUser
User

This is a multi-user database. tblLocalUser stores the User ID of the current user. tblUserPreferences stores the Issues from tblRegulation that each user wants to view. tblRegReviewed stores info on which records each user has reviewed and when they were reviewed. tblRegulation stores the records.

I have a form that I want to use to show the records that the LocalUser has not reviewed. The criteria for this query should be records where tblRegReviewed.Reviewed = NO and tblRegulation.Issue = (an Issue from tblUserPreferences) Or tblRegulation.SubIssue1 = (an issue from tblUserPreferences).

Currently, I have a four queries (one for each issue and subissue) that select the records, then I join these queries with a union query. This gives me the correct records; however I am not able to add comments or mark the records as reviewed (These fields are not updatable in a union query).

Is there another way to view the relevant records and also update comments and reviewed fields?

Another help would be greatly appreciated?

Thanks,

Jeff Weisman

 

[scriverb]

Since an UNION query is not updateable you could always take this UNION query into an Update query and update the fields that are necessary. The UNION query would just be the record selecting process and the UPDATE query would link back to the records in the other tables allowing you to update the tables as needed. I take it that these will be stand across the board updates to these fields so this should work for you.

Bob Scriver

 

[jweisman]

Before we added the option to search the subissues, this was just a basic select query. The user would just mark a check box for reviewed and type comments into a text box. These would write directly into the tables. I would like to keep a similar structure if possible. The users of this database have minimal computer skills and I would like to keep them from clicking too many buttons (i.e. type comments and run update queries)

 

[scriverb]

If someone else doesn't help you today I will get back with you this evening. I will be out of the office all day until then. Bob Scriver