Suggestions for making a query updatable

[AC5FF]

I'm working on pulling a bunch of data together for a form but need to be able to edit the data.

I realize my query is using some joins that make the query itself read only but I've been going back and forth trying to come up with another idea on how to write everything so that it would be something I can edit.

The joins in this query are unavoidable - i think.

SELECT CustomerServiceLog.[Initially Found], CustomerServiceLog.[Identify By], CustomerServiceLog.[Account #], CustomerServiceLog.Issue, [Excess RunTime List].RunTime, [Excess RunTime List].Avg, ExcessCountsPastYear.CountOfReportDate AS PreviousIDd, CustomerServiceLog.SendReport, CustomerServiceLog.SendNotes, CustomerServiceLog.RepairNotes, CustomerServiceLog.Resolution, CustomerServiceLog.[Closure Date], [BASE: APARTMENT INFORMATION].[dcc code], "0" & Right([ccc-aaa],6) AS PropertyAcct
FROM [BASE: APARTMENT INFORMATION] RIGHT JOIN (ExcessCountsPastYear RIGHT JOIN (CustomerServiceLog LEFT JOIN [Excess RunTime List] ON (CustomerServiceLog.[Account #] = [Excess RunTime List].[ACCT NUMBER]) AND (CustomerServiceLog.[Initially Found] = [Excess RunTime List].ReportDate)) ON ExcessCountsPastYear.[ACCT NUMBER] = [Excess RunTime List].[ACCT NUMBER]) ON [BASE: APARTMENT INFORMATION].[CGas Acct #] = CustomerServiceLog.[Account #]
WHERE (((CustomerServiceLog.[Closure Date]) Is Null))
ORDER BY CustomerServiceLog.[Initially Found] DESC;

I need to be able to edit the SENDREPORT, SENDNOTES, REPAIRNOTES, RESOLUTION, and CLOSUREDATE fields when the report is shown. But all the other data is needed to display on the form.

Any ideas on how I can go about this?

TIA

 

[Pat Hartman]

Joins are not inherently bad. I have many queries that include a dozen tables and they are updateable.

Without seeing your schema, I'm going to guess that you have more than one 1-many relationship and they are not hierarchical. I'll give you an example we can all relate to. You have students, classes, and pets. Both classes and pets are related to students but that doesn't mean that it makes sense to include both in the same query. You end up with:

Sally, Math, dog
Sally, Math, fish
Sally, English, dog
Sally, English, fish
John, Math, cat
John, Math, ferret
John, Math, dog
John, geometry, cat
John, geometry, ferret
John, geometry, dog

Notice the strange duplication. You end of with a recordset that doesn't make any sense. I'm pretty sure that is your problem. If you are not seeing the duplication, it may be because John and Sally have only a single pet at this time. But, that doesn't mean that tomorrow they won't have more than one.

Do you really need all the tables?

If you are using a form, you may be able to get past the problem by setting the Recordset Type on the Data tab of the Property Sheet to:
Dynaset (Inconsistent Updates)

If that doesn't work, please post the database and we'll see if we can figure it out.

 

[AC5FF]

Pat

Thanks for the reply. I've never had any luck posting a copy of a DB; file sizes have always been too large. Also; because several of my tables are linked it doesn't operate well anywhere else.

I am going to have to sit down and re-think how I'm storing my data and also try to understand your example above a little more. I can initially come up with a few different options for my data - but because of some of the external data I use I don't see a way around the joins. I'll keep digging though

 

[Mark_]

It looks like you need to run the form off of CustomerServiceLog. The others should be fields you can look up based off of values in the CustomerServiceLog.

How are you initially getting to this form? Could you recreate this as a query on CustomerServiceLog but have the other fields in sub-forms?

As your "PropertyAcct" is calculated I would not expect this query to be updatable. Would it be possible to have [ccc-aaa] in your query but format it in a control on the screen rather than formatting it in the query?

 

[isladogs]

Have a look at this article by Allen Browne
Why is my query read only?

 

[AC5FF]

ridders; I have had that on my screen all day to reference. It's actually bookmarked for me

Mark;
Just an FYI - no forms created yet. I was building the query needed for the form before actually designing the form. Some of the formatting I am doing [ccc-aaa] was to facilitate other queries that will be needed for reports. As for using subforms - I may give that a look; but to get the correct data to display I'm going to need to do the same type of query that'll make it uneditable (I think).

I've since come to the conclusion I may have to start over and while I've tried to keep things 'normalized' I don't think i've been doing a very good job. Stepping back and pulling data together differently may be the best way to solve the issues I'm having. Where this becomes difficult is where I have to tie into other legacy databases that were built with ZERO consideration for normalization. That's making it difficult - hence the formatting of the [ccc-aaa] field.

A co-worker recently tried to rewrite one of our larger databases and learned a lot on normalizing the data. We've been bouncing ideas back and forth today and that is one reason I am leaning towards re-writing this all. I just don't see how it can be done with the way the database is currently structured...

 

[Mark_]

A co-worker recently tried to rewrite one of our larger databases and learned a lot on normalizing the data. We've been bouncing ideas back and forth today and that is one reason I am leaning towards re-writing this all. I just don't see how it can be done with the way the database is currently structured...

Having to deal with a legacy system used to track medical requirements, I've found that you can use queries to turn non-normalized data into query results that act like normalized data.

You can then make more normal queries off of the now normalized information. This cuts down a LOT on the strange issues you would otherwise run into (Sally, Math, fish) and allows you to concentrate on what outputs you need.

Do not be afraid to have different queries for different purposes. Often if you try to force too many uses for one query you run into issues where it doesn't do well for any.