Updating Records in a Union Query

[throwdini]

I am creating a database that will have the following tables.

tbl1: name, id, tbl_id
tbl2: name, id, tbl_id
tbl3: id, tbl_id, y/n value

tbl1 & tbl2 do not contain duplicates and are linked to tbl3 on both id and tbl_id.

1 and 2 have to be kept separate.

I want to open a continous form that allows users to click a check box and update y/n value in tbl3, that also shows all the values from tbl1 and tbl2.

Any ideas or suggestions are greatly appreciated.
Thanks,
Jay

 

[mikey69]

Just construct an ordinary sdelect query from the related tables. You might have to set the record type to Dynaset (inconsistent updates) to allow updating of the fields you state

Access makes all things possible. It even makes them intelligible

 

[throwdini]

I'm still having problems with it.
What should the query look like?
What I have in mind looks something like this.

Select tbl1.name, tbl3.value from tbl1 inner join tbl3 on ... from tbl.1
UNION
Select tbl2.name, tbl3.value from tbl2 inner join tbl3 on ... from tbl.2

But this is not working.It returns all the records I'm interested in, but it doesn't allow any modifications to those records.
Any help?
Thanks,
Jay

 

[mikey69]

You are trying to be too complicated, I think. Union queries are not updatable and all you need is a simple select query. But I don't understand why you have multiple fields in the relationships between the tables. In fact, as I type this reply, I can't think why you might need to have multiple tables. Can you not put allth info in a single table? It shouldn't be a problem if there are no duplicates between tbl1 & tbl2 and it would make everything simpler.

Access makes all things possible. It even makes them intelligible

 

[throwdini]

I know it would make things simpler, but unfortunately tbl1 and tbl2 have to be separate. You were correct though, all I needed was a modified select query that created a name field using an IIF statement, and where the joins were not inner joins but were left joins. Also I had to set the query to dynaset (inconsistan updates).

Thanks for your help.