Join 2 or more tables and update any 1 of the joined tables

[khughes46]

Is there a way to join 2 or more tables and update any 1 of the joined tables?
I have been trying a way to discover how to join tables and be able to update either one of the joined tables.
As a for instance. I have a main file with codes for locations, race, language and attendance.
I have created a relationship between the main file and the four tables, each has a different coded field in the main file.
When I display the main file without the related tables I have no problem making changes.
As soon as I add the related tables all I get is a beep (or whatever that sound is).


Is there a way to do what I am asking?

[June7]

Normally, a form can do data entry/edit for only one table.

What is relationship between these tables? Are Locations, Race, Language tables just lookups for values? Is Attendance to record individual daily attendance info?

Possibly need a form/subform arrangement with Main and Attendance.

[khughes46]

I am using a form/subform for things like this now. The data in the tables is gleaned from other sources. The race and language is downloaded from a state supported web site, the main file has a code that corresponds to the downloaded code and description; the description for the code is used. The locations are all manually entered with a location ID, loacation name, address and phone numbers and used as needed. The attendance is downloaded from 3rd party vendor software. None of the data in the tables need to be updated in my system, just the main file data.

[ranman256]

Yes a simple join of 2 tables/queries will allow an update...however..
you cannot update if 1 query is a summation qry.

Also some 1-many relations my prevent cascading deletes.

[khughes46]

I have joined just 2 tables and still could not update the main one. The two table are a data entry table and a names table. I joined the names table in the qery with the data table and I could not update the datat table.

[June7]

Post query for analysis. Or provide db.

[khughes46]

SELECT Glance.[Campus ID], Glance.[PEIMS ID], Glance.[Last Name], Glance.[First Name], Glance.[Middle Name], [LPAC Data].LastName, [LPAC Data].FirstName, [LPAC Data].MiddleName, [LPAC Data].PEIMSId
FROM [LPAC Data] RIGHT JOIN Glance ON [LPAC Data].PEIMSId = Glance.[PEIMS ID];

Here I am comparing the names from 2 files to make corrections if there is a fifference. I keep getting the "DING".

[rpeare]

Is the PEIMS_ID the primary key of both tables? At the very least it needs to be the primary key of the GLANCE table since that's the table that's driving the query.

[khughes46]

In neither table is PEIMS ID the primary key. I see what you mean. Let me make that change and see what I get. PEIMS ID will probably be the primary key in both tables.

Worked like a charm. It's been a while since I have done anything like that. Thanks again for your help.