Query design

[kirkm]

How could a query be build from 2 tables, showing all fields in table1 and only same fields (having different values) in table 2?

The '(having different values' isn't essential as a Form could be based on this and all the value shown. Yes? Any issues having it editable?

Thanks

 

[sneuberg]

If you mean by same field, same field names, then the query can distinguish the fields by the table name as show in this example query.

SELECT Table1.Field1, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FID;

You will find this query in the attached database and find that it is not editable so any form based on this wouldn't be editable. The normal way to set this up is not with a single query but with two forms. A form and a subform is which case the fields would be editable as keys are handle in the link fields for you. See Form1 in the attached database.

This may not be of any help to you as I'm guessing at what you are trying to do. What are you trying to accomplish in terms of managing data?

 

[kirkm]

Hi Steve, can you add your attachment as an .mdb file ?

Had a play with the query but I don't think I'll succeed. Does it need matching ID fields? (Whats FID?) And is it working on one field only?

Thanks

 

[sneuberg]

I've attached an mdb for Access 2002-2003.

If you look in Relationships you will see that FID is the foreign key in table2. table1 is related to table2 in a one to many relationship.

I really think you need to tell us what you are trying to accomplish if you want any help that is of any value.

 

[kirkm]

I'm trying to see/compare table1 and table 2, and edit either.
There's no relationship except the same field names (although one table has more fields than the other).

I've been doing it in VBA, using two recordsets, and debug window but seeking a better way. I tried opening two forms but its a bit messy.

 

[sneuberg]

If you just want to view and edit the fields in both tables I suggest making a query for each table with the fields you want, create a continuous form for based on each query, i.e., two continuous forms. Then put these in another form. I call this other form MainForm in the attached database which demonstrates what I mean.