I'm having a problem with relationships disappearing from queries.

Table1 has the field RecID.

Table2 has the fields RecID, Generation, and other data. The generation number is incremented each time updated data is appended for a given RecID.

The tables are linked by a 1-many relationship (in the query) between Table1 and Table2.

The goal is: for each RecID in table1, show the matching record in Table2 with the most recent data (i.e. the one with the maximum Generation value).

I tried to accomplish this by creating a query (call it qryMaxDataGen) that groups the data on RecID and the max of generation (MaxGen). The results of this query appear to work fine.

So I have the following relationships:
table1!RecID -> table2!RecID
table1!RecID -> qryMaxDataGen!RecID
qryMaxDataGen!MaxGen -> table2!Generation

This works great, except that when I reopen the query in design view the last relationship line is GONE.

I have many queries using this technique, some pulling the most current record from 3 other tables, and all have the query to table relationship. Sometimes they all disappear, sometimes just some of them, and sometimes all of them. I can't get the problem to repeat absolutely reliably (I opend and fixed a quey 3 time; the fourth time the I opend it the relationship was still there).

Any ideas?

A couple of questions:

What version of Access are you using?

Are these relationships permanent or are they setup in the query window only?

Have you tried establishing permanent relationships to these queries in the relationship layout?

Keagan Quilty
Kilbride, Newfoundland

Access 2000.
Query window only.

I'll try building the relationship permanently.

Permanent relationships appear to do the trick.

I also developed an alternate method (using 2 tables and some code).

Thanks!

I still have (at least) one query where the relationships won't "stick".

I think your database may be corrupted. You can try three things (make a backup copy before trying the third).
1. Run the repair utility.
2. Create a new database and import all of the objects from the old database.
3. Use the decompile switch when you open the database - http://www.access-programmers.co.uk/ubb/Forum1/HTML/000668.html

Mr. Hartman:

Option 1 did not work.

Option 2 (so far) appears to be working. I will check the offending queries for the next few days and post again if there are problems.


Thanks for all the help.


fmm