Why is my simple query read only?

[Whizbang]

I am having a dumb-moment here. I cannot figure out why my simple query is read only. It has something to do with tblPacketResults, but I cannot figure out why. Can someone take a look at my database and give me some hints as to why qryResultEntry is read only?

[JoeM]

I don't have the ability to download files from my present location, but Allen Browne put together a nice list of reasons why queries may be read-only.
See here: http://allenbrowne.com/ser-61.html

[Whizbang]

YEah, I had seen that before, and I think it has to do with this one:

The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

tblSectionFields is two fields (SectionID and FieldID {Yes, I know using the name "FieldID" is a little odd, but it is the only name I can think of for my use}) Each SectionID can have many FieldIDs. Each FieldID can be used in many SectionIDs (but only once per SectionID). So, tblSectionFields ties the FieldIDs to the SectionIDs. The Primary Key is both the SectionID and the FieldID fields. Then I have tblPacketResults. Each PacketResultID can have many FieldIDs under it. I need a way to organize the FieldIDs by SectionID. So, I wanted to tie tblPacketResults to tblSectionFields. But the FieldID is indexed as "Duplicates OK" for both tables. I've done Many to Many relationship queries before and I don't remember it causing this issue (though maybe I've only done that for reports or something, and so never noticed it being Read Only).

[JoeM]

Obviously, it is difficult for me to say exactly what is going without seeing the structure and data. Could you possibly post the SQL code of your query?

Don't know if this helps, but I found another link that tells you how to solve each issue: http://rogersaccessblog.blogspot.com...eable-why.html

[Whizbang]

I have attached the documentation generated by Access.

SELECT tblPacketResults.AccountPacketID, tblPacketResults.PacketSectionID, tblPacketResults.Error,
tblPacketResults.Description
FROM qrySectionFields LEFT JOIN tblPacketResults ON qrySectionFields.FieldID = tblPacketResults.FieldID;

Here is an excerpt of the documentation for the tables and queries in question.

Table: tblPacketResults
Properties
AlternateBackShade: 95 AlternateBackThemeColorInd 1
AlternateBackTint: 100 BackShade: 100
BackTint: 100 DatasheetForeThemeColorIn 0
DatasheetGridlinesThemeCol 3 DateCreated: 10/22/2012 8:50:26 AM
DefaultView: 2 DisplayViewsOnSharePointSit 1
FilterOnLoad: False GUID: {guid {2049F804-688A-45CE-
B3BE-C9C03DB85074}}
HideNewField: False LastUpdated: 10/23/2012 12:35:12 PM
NameMap: Long binary data OrderByOn: False
OrderByOnLoad: True Orientation: Left-to-Right
PublishToWeb: 1 ReadOnlyWhenDisconnected: False
RecordCount: 2 SubdatasheetName: [None]
ThemeFontIndex: 1 TotalsRow: False
Updatable: True
Columns
Name Type Size
AccountPacketID Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {38AE1BAD-8CA7-4299-B7C7-78CC179771D8}}
OrdinalPosition: 0
Required: True
ResultType: 0
SourceField: AccountPacketID
SourceTable: tblPacketResults
TextAlign: General
FieldID Long Integer 4
AggregateType: -1
AllowMultipleValues: False
AllowValueListEdits: False
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
BoundColumn: 1
CollatingOrder: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 0;2340
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Combo Box
GUID: {guid {C36E1705-FC6F-47A7-AC8A-18ACCE2202AC}}
LimitToList: True
ListRows: 16
ListWidth: 2340twip
OrdinalPosition: 1
Required: True
ResultType: 0
RowSource: SELECT [tblFieldSettings].[FieldID], [tblFieldSettings].[FieldName]
FROM tblFieldSettings ORDER BY [FieldName];
RowSourceType: Table/Query
ShowOnlyRowSourceValues: False
SourceField: FieldID
SourceTable: tblPacketResults
TextAlign: General
PacketSectionID Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1920
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {081DB1E8-C146-4933-8705-5532810D99AD}}
OrdinalPosition: 2
Required: True
ResultType: 0
SourceField: PacketSectionID
SourceTable: tblPacketResults
TextAlign: General
Error Long Integer 4
AggregateType: -1
AllowMultipleValues: False
AllowValueListEdits: False
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
BoundColumn: 1
CollatingOrder: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 0;1440
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Combo Box
GUID: {guid {E435E174-F199-4631-BDC7-7DA9DDA2A913}}
LimitToList: True
ListRows: 16
ListWidth: 2880twip
OrdinalPosition: 3
Required: False
ResultType: 0
RowSource: 1;"Error";2;"No Error";3;"N/A"
RowSourceType: Value List
ShowOnlyRowSourceValues: False
SourceField: Error
SourceTable: tblPacketResults
TextAlign: General
Description Text 255
AggregateType: -1
AllowZeroLength: True
AppendOnly: False
Attributes: Variable Length
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
CurrencyLCID: 0
DataUpdatable: False
DisplayControl: Text Box
GUID: {guid {B53D8C82-C989-4CE2-9575-22275CEDDC74}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 4
Required: False
ResultType: 0
SourceField: Description
SourceTable: tblPacketResults
TextAlign: General
UnicodeCompression: True
Relationships
tblFieldSettingstblPacketResults
tblFieldSettings tblPacketResults
FieldID FieldID
Attributes: Not Enforced
RelationshipType: One-To-Many
Table Indexes
Name Number of Fields
AccountPacketID 1
Clustered: False
DistinctCount: 1
Foreign: False
IgnoreNulls: False
Name: AccountPacketID
Primary: False
Required: False
Unique: False
Fields:
AccountPacketID Ascending
FieldID 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: FieldID
Primary: False
Required: False
Unique: False
Fields:
FieldID Ascending
PacketSectionID 1
Clustered: False
DistinctCount: 1
Foreign: False
IgnoreNulls: False
Name: PacketSectionID
Primary: False
Required: False
Unique: False
Fields:
PacketSectionID Ascending
PrimaryKey 3
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields:
AccountPacketID Ascending
FieldID Ascending
PacketSectionID Ascending

Table: tblSectionFields
Properties
AlternateBackShade: 95 AlternateBackThemeColorInd 1
AlternateBackTint: 100 BackShade: 100
BackTint: 100 DatasheetForeThemeColorIn 0
DatasheetGridlinesThemeCol 3 DateCreated: 10/23/2012 9:03:16 AM
DefaultView: 2 DisplayViewsOnSharePointSit 1
FilterOnLoad: False GUID: {guid {C935569F-9728-4533-
9EB1-B2DA5888E918}}
HideNewField: False LastUpdated: 10/23/2012 9:15:52 AM
NameMap: Long binary data OrderByOn: False
OrderByOnLoad: True Orientation: Left-to-Right
PublishToWeb: 1 ReadOnlyWhenDisconnected: False
RecordCount: 2 SubdatasheetName: [None]
ThemeFontIndex: 1 TotalsRow: False
Updatable: True
Columns
Name Type Size
SectionID Long Integer 4
AggregateType: -1
AllowMultipleValues: False
AllowValueListEdits: False
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
BoundColumn: 1
CollatingOrder: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 0;2940
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Combo Box
GUID: {guid {2AA41543-C976-4BBC-825B-3B4F5EB105CE}}
LimitToList: True
ListRows: 16
ListWidth: 2940twip
OrdinalPosition: 0
Required: True
ResultType: 0
RowSource: SELECT [tblSectionSettings].[SectionID],
[tblSectionSettings].[SectionName] FROM tblSectionSettings ORDER
BY [SectionName];
RowSourceType: Table/Query
ShowOnlyRowSourceValues: False
SourceField: SectionID
SourceTable: tblSectionFields
TextAlign: General
FieldID Long Integer 4
AggregateType: -1
AllowMultipleValues: False
AllowValueListEdits: True
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
BoundColumn: 1
CollatingOrder: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 0;2865
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Combo Box
GUID: {guid {BF945A82-D3DF-40A0-A117-B18DDB823539}}
LimitToList: True
ListRows: 16
ListWidth: 2865twip
OrdinalPosition: 1
Required: True
ResultType: 0
RowSource: SELECT [tblFieldSettings].[FieldID], [tblFieldSettings].[FieldName]
FROM tblFieldSettings ORDER BY [FieldName];
RowSourceType: Table/Query
ShowOnlyRowSourceValues: False
SourceField: FieldID
SourceTable: tblSectionFields
TextAlign: General
Relationships
tblFieldSettingstblSectionFields
tblFieldSettings tblSectionFields
FieldID FieldID
Attributes: Not Enforced
RelationshipType: One-To-Many
tblSectionSettingstblSectionFields
tblSectionSettings tblSectionFields
SectionID SectionID
Attributes: Not Enforced
RelationshipType: One-To-Many
Table Indexes
Name Number of Fields
FieldID 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: FieldID
Primary: False
Required: False
Unique: False
Fields:
FieldID Ascending
PrimaryKey 2
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields:
SectionID Ascending
FieldID Ascending
SectionID 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: SectionID
Primary: False
Required: False
Unique: False
Fields:
SectionID Ascending

Query: qryResultEntry
Properties
DateCreated: 10/22/2012 8:57:16 AM DefaultView: 2
DOL: Long binary data FilterOnLoad: False
GUID: {guid {98AE1633-093C-4CC5- LastUpdated: 10/23/2012 1:27:26 PM
8D28-CBFF94E67D49}}
MaxRecords: 0 ODBCTimeout: 60
OrderByOn: False OrderByOnLoad: True
Orientation: Left-to-Right PublishToWeb: 1
RecordLocks: No Locks RecordsAffected: 0
RecordsetType: Dynaset ReturnsRecords: True
TotalsRow: False Type: 0
Updatable: True
SQL
SELECT tblPacketResults.AccountPacketID, tblPacketResults.PacketSectionID, tblPacketResults.Error,
tblPacketResults.Description
FROM qrySectionFields LEFT JOIN tblPacketResults ON qrySectionFields.FieldID = tblPacketResults.FieldID;
Columns
Name Type Size
AccountPacketID Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1980
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {38AE1BAD-8CA7-4299-B7C7-78CC179771D8}}
OrdinalPosition: 0
Required: True
ResultType: 0
SourceField: AccountPacketID
SourceTable: tblPacketResults
TextAlign: General
PacketSectionID Long Integer 4
AggregateType: -1
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1920
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: {guid {081DB1E8-C146-4933-8705-5532810D99AD}}
OrdinalPosition: 1
Required: True
ResultType: 0
SourceField: PacketSectionID
SourceTable: tblPacketResults
TextAlign: General
Error Long Integer 4
AggregateType: -1
AllowMultipleValues: False
AllowValueListEdits: False
AllowZeroLength: False
AppendOnly: False
Attributes: Fixed Size
BoundColumn: 1
CollatingOrder: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 0;1440
CurrencyLCID: 0
DataUpdatable: False
DecimalPlaces: Auto
DisplayControl: Combo Box
GUID: {guid {E435E174-F199-4631-BDC7-7DA9DDA2A913}}
LimitToList: True
ListRows: 16
ListWidth: 2880twip
OrdinalPosition: 2
Required: False
ResultType: 0
RowSource: 1;"Error";2;"No Error";3;"N/A"
RowSourceType: Value List
ShowOnlyRowSourceValues: False
SourceField: Error
SourceTable: tblPacketResults
TextAlign: General
Description Text 255
AggregateType: -1
AllowZeroLength: True
AppendOnly: False
Attributes: Variable Length
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1485
CurrencyLCID: 0
DataUpdatable: False
DisplayControl: Text Box
GUID: {guid {B53D8C82-C989-4CE2-9575-22275CEDDC74}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 3
Required: False
ResultType: 0
SourceField: Description
SourceTable: tblPacketResults
TextAlign: General
UnicodeCompression: True
Table Indexes
Name Number of Fields
AccountPacketID 1
Clustered: False
DistinctCount: 1
Foreign: False
IgnoreNulls: False
Name: AccountPacketID
Primary: False
Required: False
Unique: False
Fields:
AccountPacketID Ascending
FieldID 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: FieldID
Primary: False
Required: False
Unique: False
Fields:
FieldID Ascending
PacketSectionID 1
Clustered: False
DistinctCount: 1
Foreign: False
IgnoreNulls: False
Name: PacketSectionID
Primary: False
Required: False
Unique: False
Fields:
PacketSectionID Ascending
PrimaryKey 3
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields:
AccountPacketID Ascending
FieldID Ascending
PacketSectionID Ascending

Relationships: All
Relationships
tblFieldSettings tblPacketResults
tblFieldSettings tblPacketResults
FieldID FieldID
Attributes: Not Enforced
RelationshipType: One-To-Many
tblFieldSettings tblSectionFields
tblFieldSettings tblSectionFields
FieldID FieldID
Attributes: Not Enforced
RelationshipType: One-To-Many
tblSectionSettings tblSectionFields
tblSectionSettings tblSectionFields
SectionID SectionID
Attributes: Not Enforced
RelationshipType: One-To-Many

[JoeM]

Looks like this query is calling another query. What is the sql code of "qrySectionFields"?

[Whizbang]

Oops. Sorry about that. I forgot I had changed that to see if I put some grouping or "Unique Records Only" value on tblSectionFields before I linked it to tblPacketResults, if it would fix my problem. It didn't.

Here is the original SQL

SELECT tblSectionFields.SectionID, tblSectionFields.FieldID, tblPacketResults.AccountPacketID, tblPacketResults.PacketSectionID, tblPacketResults.Error, tblPacketResults.Description
FROM tblSectionFields LEFT JOIN tblPacketResults ON tblSectionFields.FieldID = tblPacketResults.FieldID;

[JoeM]

Hmmm... I don't think there is much else I can do without seeing the data. I might be able to take a look at it tonight, when I am back home where I can download files.

[Whizbang]

I think I am just going to have to put SectionID in tblFieldSettings. If multiple Sections need the same field, then I'll just have to create multiple fields with the same name but different FieldIDs. Bummer, I was hoping to avoid consistency issues with Field names and start and end dates.