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

 

[Pat Hartman]

I suspect that you did not partition the tables correctly. It sounds like you ended up with 4 tables with 1-to-1 relationships. If that is the case, only ONE of the tables should have an autonumber pk, the other three tables should have their pk defined as long integer and your application should place the autonumber value into the pk of the other three tables. I am guessing that something caused the autonumbers to go out of sync and that's why you can't add new records.

You should probably re-examine your tables and remove the flattened 1-to-many relationships so they are stored properly.

 

[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;

 

[Pat Hartman]

Since this is the query for a subform or subreport, I would change it as follows:

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

Unless there are missing required fields, this query should be updatable.

The problem was that the foreign key field was missing from the query and therefore, the form couldn't create a new record. I substituted the caseID from the Exam table for the field from the Demographics table. You don't need to update the Demographics table from the subform.

 

[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.

 

[Pat Hartman]

But you are not selecting any of the demographics columns. That's why I changed the join type. Also, since the demographics data should show on the main form, there is NO reason to include it in the subform's query.