Denormalized query is read only (1 Viewer)

[pbaldy]

As discussed last night at this meeting:

Updateable Bound Forms with Denormalized Data with Pat Hartman

Can you display and edit properly normalized data in a form which resembles a spreadsheet? Although it sounds impossible, it can be done with the right technique. Pat Hartman shares her method with us. She will show us the sample database containing two examples (using the same technique) of...

www.access-programmers.co.uk

I'm trying to implement Pat's method. Attached is the test db I discussed. The live data is in SQL Server but the problem shows up with local tables as well.

The issue is that I can get the header query and 1 detail query (either one) to be editable. As soon as I add a second detail query, it becomes read only. There will eventually be 7-8 detail queries, but I figure once I can get 2 working the others will fall into place.

Pinging @Pat Hartman and @GPGeorge who were present and involved. Thanks for any help and a good meeting!

Edit: For context, the "master" table is the header info for accounts receivable transactions. There will be one or more detail records for each master record, depending on what charges a customer incurred.

 

[Pat Hartman]

I've been looking at this for an hour and I haven't yet found the difference. Some things I've tried:

Added RI
Changed the left joins to inner joins
Removed the left query and replaced it with direct access to the table and embedded criteria.

I went into my app and noticed that since the Expense types were strings, none of the queries joined to the lookup table so I added the lookup table using an inner join and then as a left join. Neither version broke the query.

Got another project I need to work on so I'll let it simmer for a while and get back to it later.

 

[GPGeorge]

As discussed last night at this meeting:

Updateable Bound Forms with Denormalized Data with Pat Hartman

Can you display and edit properly normalized data in a form which resembles a spreadsheet? Although it sounds impossible, it can be done with the right technique. Pat Hartman shares her method with us. She will show us the sample database containing two examples (using the same technique) of...

www.access-programmers.co.uk

I'm trying to implement Pat's method. Attached is the test db I discussed. The live data is in SQL Server but the problem shows up with local tables as well.

The issue is that I can get the header query and 1 detail query (either one) to be editable. As soon as I add a second detail query, it becomes read only. There will eventually be 7-8 detail queries, but I figure once I can get 2 working the others will fall into place.

Pinging @Pat Hartman and @GPGeorge who were present and involved. Thanks for any help and a good meeting!

Edit: For context, the "master" table is the header info for accounts receivable transactions. There will be one or more detail records for each master record, depending on what charges a customer incurred.

Hi Paul. I'll try to look at this in the next day or two. Priority one is editing the video from the presentation.

THANKS to everyone who participated.

I noted a discussion in the Non-NDA group regarding composite Primary Keys. Is that possibly involved here?

 

[pbaldy]

I've been looking at this for an hour and I haven't yet found the difference. Some things I've tried:

Added RI
Changed the left joins to inner joins
Removed the left query and replaced it with direct access to the table and embedded criteria.

I went into my app and noticed that since the Expense types were strings, none of the queries joined to the lookup table so I added the lookup table using an inner join and then as a left join. Neither version broke the query.

Got another project I need to work on so I'll let it simmer for a while and get back to it later.

Thanks Pat, I appreciate your help and continued attention. I guess at least I can take comfort knowing I haven't made a bonehead blunder. Or at least the odds are lower.

 

[pbaldy]

Hi Paul. I'll try to look at this in the next day or two. Priority one is editing the video from the presentation.

THANKS to everyone who participated.

I noted a discussion in the Non-NDA group regarding composite Primary Keys. Is that possibly involved here?

That was me George. The production table has those extra fields defined as keys. I couldn't get an editable query at all until I used an archive table of the same data and switched to the single field that should have been the sole PK all along. Then I used the "convert to local table" option. I don't think there could be anything behind the scenes affecting this now but that isn't my area of expertise. In fact, I'm still trying to figure out what my area of expertise is.

 

[ebs17]

I only briefly looked at the example database.
Any database, like this one, that doesn't show relationships between tables is a sign that it's not meant to be easily understood.
This is simply a weak introduction, even for beginners, and even weaker for advanced users or aspiring professionals.

 

[pbaldy]

Thank you for your thoughts. As noted this is a sample db drawn from a very large db meant to explore a single issue.

 

[ebs17]

In Jet-SQL, DISTINCTROW is a frequently effective means of accessing uniqueness in tables and thereby achieving updateability in queries.

As a rule, I limit the update to exactly one table and thus avoid any problems. The only thing that needs to be taken into account is referential integrity.
So I am discussing this topic just for entertainment purposes.

 

[pbaldy]

As a rule, I limit the update to exactly one table and thus avoid any problems. The only thing that needs to be taken into account is referential integrity.

As a rule I do too, this is a somewhat unique situation. I deal with "heads-down" data entry users a lot. Accounts Receivable is one component in entering a driver's daily activity. For initial entry, I give them a continuous form bound to a local denormalized table.

When they save the driver's other activity, I insert the AR data into the 2 normalized tables you see in the sample. I was looking to give them a similar way to edit the data. They find the standard form/subform cumbersome to deal with for this data.