Multi Table Queries

[Pete]

At my job, I use enter data through generated queries. However it appears
that I can only ENTER data in a query when it is based on two tables. If it
is three or more, it doesn't let me enter any info.

Is this normal? Am I doing something wrong?

-Pete

 

[Marshall Barton]

At my job, I use enter data through generated queries. However it appears
that I can only ENTER data in a query when it is based on two tables. If it
is three or more, it doesn't let me enter any info.

Is this normal? Am I doing something wrong?

Yes, that's common. You should only edit data in a single
table at a time. Forms and subforms are the mechanisms
intended for entering/editing data in (multiple) tables.

 

[Duane Hookom]

You can create queries with many tables and keep them updateable. The "key"
is the "key". If you join primary to foreign keys 1 to many through mutliple
"generations", the results can be updateable. I have one application where I
think I use 6 tables and the results can be updated.

 

[Marshall Barton]

You can create queries with many tables and keep them updateable. The "key"
is the "key". If you join primary to foreign keys 1 to many through mutliple
"generations", the results can be updateable. I have one application where I
think I use 6 tables and the results can be updated.

Oh my!

Are you also allowing edits in that kind of query's
datasheet or in some sophisticated form?

 

[Duane Hookom]

It's a datasheet view on a subform. I have most of the fields locked and
allow entry into only about 3 fields. This is an inspection database
application and this form allows entry of inspection results.

Here is the formatted SQL view:

SELECT tblFacility.facFacID, tblFacility.facFacility, tblAreas.areAreID,
tblAreas.areDescription, tblSites.sitSitID, tblSites.sitDescription,
tblInspections.insComplDate, tblInspections.insEnterDate,
tblInspections.insEnterBy, tblInspections.insResID,
tblInspectionTypes.ityInspectionType, tblInspectionPoints.poiDescription,
tblInspections.insInspectDate, tblInspections.insRespPSEmpID,
tblInspections.insInsID, tblInspections.insAuditBy
FROM tblInspectionTypes
INNER JOIN (tblInspectionPoints
INNER JOIN (tblFacility
INNER JOIN (tblAreas
INNER JOIN (tblSites
INNER JOIN tblInspections ON tblSites.sitSitID = tblInspections.insSitID)
ON tblAreas.areAreID = tblInspections.insAreID)
ON tblFacility.facFacID = tblInspections.insFacID)
ON tblInspectionPoints.poiPOIID = tblInspections.insPOIID)
ON tblInspectionTypes.ityITyID = tblInspections.insITyID
ORDER BY tblFacility.facFacility, tblAreas.areDescription,
tblSites.sitDescription, tblInspectionTypes.ityInspectionType,
tblInspectionPoints.poiDescription;

 

[Marshall Barton]

It's a datasheet view on a subform. I have most of the fields locked and
allow entry into only about 3 fields. This is an inspection database
application and this form allows entry of inspection results.

Here is the formatted SQL view:

SELECT tblFacility.facFacID, tblFacility.facFacility, tblAreas.areAreID,
tblAreas.areDescription, tblSites.sitSitID, tblSites.sitDescription,
tblInspections.insComplDate, tblInspections.insEnterDate,
tblInspections.insEnterBy, tblInspections.insResID,
tblInspectionTypes.ityInspectionType, tblInspectionPoints.poiDescription,
tblInspections.insInspectDate, tblInspections.insRespPSEmpID,
tblInspections.insInsID, tblInspections.insAuditBy
FROM tblInspectionTypes
INNER JOIN (tblInspectionPoints
INNER JOIN (tblFacility
INNER JOIN (tblAreas
INNER JOIN (tblSites
INNER JOIN tblInspections ON tblSites.sitSitID = tblInspections.insSitID)
ON tblAreas.areAreID = tblInspections.insAreID)
ON tblFacility.facFacID = tblInspections.insFacID)
ON tblInspectionPoints.poiPOIID = tblInspections.insPOIID)
ON tblInspectionTypes.ityITyID = tblInspections.insITyID
ORDER BY tblFacility.facFacility, tblAreas.areDescription,
tblSites.sitDescription, tblInspectionTypes.ityInspectionType,
tblInspectionPoints.poiDescription;

Interesting. I don't think I've ever run into a situation
where I needed more than three tables and even then I ran
into trouble occasionally.

With that many fields, you probably reduce clutter by
setting ColumnWidths to 0 for at least the ID columns.

 

[Duane Hookom]

There are a couple of the ID fields that aren't displayed in the datasheet
form. The others match values displayed in a paper report used to conduct
inspections on the factory floor. Since the joins are all primary/foreign
keys, the query remains updateable.