Problem w/multi-table query

[Susan5767]

I recently divided a large table into 4 smaller tables. Because there was no unique identifier within the tables I added an auto number to serve this function. After that I formed a simple query using the autonumber as the common element (sorry I'm really new at this). The first week everything worked great. I was able to easily update and add records to my query. This week Office XP was loaded replacing the original Office 2000 software - now my query can be updated but no new records can be added.

My questions are as follows - was using an autonumber as a unique identifier a good idea? If not, is there any other way to do this. I'm dealing with property records where the unique identifier is a street address and no unique number is easily and/or readily available.

Was the upgrade the source of my problems or is this just a coincidence?

Any ideas on what I can do to fix this problem? Is the problem having too many tables?

We have zero software support here at work and I have to rely on books or finding someone who uses access to help me.

Any suggestions would be greatly appreciated!

Susan

 

[AncientOne]

You have committed no sin in using autonumbers as your unique index. It is a recognized method when no other unique identifier can be readily found.

I would surmise that the problem is a by-product of the upgrade.

 

[AncientOne]

a quick trawl through the forum doesn't throw up any similar posts. some ideas: compact/repair the database. Check that your relationships are intact. Compile any code. Check the references (see Help for this). Try composing a new query and see if that works

 

[AnneB]

Add Record Problem using a Query

I have a similar problem and am using Access 2000. I have two tables with a one-to-many relationship; on the one side: patient information, on the many side: exam information. I have defined an exam data query with a calculated field, age at exam, using the birthdate from the one side and the exam date from the many side. The tables are combined by a left join. I am using forms to the enter the data, with the patient data table as the record source for the main form and the exam data query as the record source for the subform. I can edit the fields in the exam subform but can't add any records to the exam table. I can add records to the patient table.

I've tried turning on and off enforcement of referential integrity with and without cascade update/deletes. I've tried using and not using an auto number primary key in the exam table.

I can add records if I use the exam table as the record source instead of the exam query but then I lose my calculated age at exam field in the query. I know I can add this as a field to my subform, but it seems like a better idea to make it part of a query, then I can have "access" (wishful thinking) to the same field in other activities such as reporting and exporting data without having to redefine it.

Any thoughts, ideas, suggestions, questions appreciated!

 

[AncientOne]

It sounds as though your query is not updateable. Please post the SQL for your query.

 

[AnneB]

The query is...

SELECT tblDemographics.caseid, tblExamInfo.examnumber, tblDemographics.birthdate, tblExamInfo.examdate, DateDiff("m",[birthdate],[examdate]) AS examage
FROM tblDemographics LEFT JOIN tblExamInfo ON tblDemographics.caseid=tblExamInfo.caseid;

 

[AnneB]

That did the trick, thanks! I replaced CaseId from the Demographics table with CaseId from the Exam table, as suggested, and can now add records to the Exam table using the query in the subform.

I did not, however, change the LEFT JOIN to an INNER JOIN per your example. Since I plan to use this query for more than the subform, I thought the LEFT JOIN would be more useful, that is include all Patient demographics even if there are no exams listed?

Thanks again for your help.