SolvedPrivate

Access Query: Update "one" side of a one-to-many when many is a totals query

Posted on 2014-11-07
25
24 Views
Last Modified: 2016-02-11
I would like to update the "one" side of a one-to-many when the many side is a totals query.  Is this possible?
Jeff
0
Comment
Question by:wellesleydpw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 3
  • +2
25 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428138
Yes you can. However, you need to be more specific, so the best advice can be given.
0
 

Author Comment

by:wellesleydpw
ID: 40428292
Thanks Phillip,

On the one side, I have two fields:
1. [Seg] - a text field
2. [Locked] - a yes/no check box).  

One the many side, is a total query  with two fields:
1. [Seg3] - the join field and the field that the query is also grouped by
2. [MaxOfEditDate] - the total field which is a Max of the field [EditDate]

I would like to be able to edit the value of the field [Locked].

Does that help?
Jeff
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428295
Yes - HOW do you want to edit it?
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:wellesleydpw
ID: 40428349
I would like to change it back and forth from Yes to No and No to Yes.
Jeff
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428353
It's a boolean - yep.

Based on what criteria?
0
 

Author Comment

by:wellesleydpw
ID: 40428354
Clicking the check box.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428359
Right - we are getting closer. So here is what I imagine (and what you have not said).

You have a form, which is based on some unknown query (which would be nice to know), but which shows fields from 2 tables:

1. One table, which has [Seg] and [Locked]. It is presumed that Seg is the primary key.
2. A second table, which has [Seg3] and [MaxOfEditDate]

It is grouped by Seg3, which is the foreign key of Seg.

You want the end user to be able to click on the [Locked] check box, and update all Locked values based on the value of Seg3.

Is this correct?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40428662
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
0
 

Author Comment

by:wellesleydpw
ID: 40480734
Thanks Phillip,
Sorry I was out sick for a week and then catching up.

You're close.  
1. One table, which has [Seg] and [Locked]. It is presumed that Seg is the primary key. - YES
2. A second table, which has [Seg3] and [MaxOfEditDate] - not a table but a total query that is grouped by [Seg3] and returns a max value of the field [EditDate] so the resulting field name in the query is [MaxOfEditDate]

I want the end user to be able to click on the [Locked] check box, which would only change the value on the [Locked] check box in the first table and have no effect on the second query.

Is that clearer?
Jeff
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480758
And is the problem that, when the user clicks in the check box, he gets the message "This Recordset is not updateable"?
0
 

Author Comment

by:wellesleydpw
ID: 40482585
Correct.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482608
Finally. It's been a long, hard process, but after a month of work, I've finally got the question out of you, namely:

I'm getting the message "This Recordset is not updateable" when the user clicks in the check box on a form.

My tables are this ...
Here's the answer. Put this in the Locked_GotFocus VBA:

Private Sub Locked_GotFocus()
On Error Resume Next
Dim myRecord As Long
myRecord = Me.CurrentRecord
DoCmd.SetWarnings False
DoCmd.RunSQL "Update TableA Set Locked = " & IIf([Locked], "False", "True") & " Where Seg = " & Chr(34) & [Seg] & Chr(34)
DoCmd.SetWarnings True
DoCmd.RefreshRecord
DoCmd.Requery
DoCmd.GoToRecord acActiveDataObject, , acGoTo, myRecord
End Sub

Open in new window

0
 

Author Comment

by:wellesleydpw
ID: 40507356
Thanks Phillip,
I'm using access 2007 and from what I gather RefreshRecord was introduced in the 2010 version.  I get an error when I try to compile.  Is there an alternative command for 2007?
Jeff
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40507369
Try just omitting it and seeing if it works.
0
 

Author Comment

by:wellesleydpw
ID: 40508354
I tried but it didn't work.
Jeff
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40508436
Can you recreate the issue using a sample database and upload?
 
Explain which field to update and under what criteria.
0
 

Author Comment

by:wellesleydpw
ID: 40533490
I've uploaded the file.  Thanks for your help.

Jeff
Database1.accdb
0
 
LVL 58
ID: 40551083
Short answer to this is you can't do this directly.   In order for a query to be updateable, Access must be able to identify rows uniquely, but in a totals query (has a group by), it can't do that.  So you need to do it indirectly as Philip showed you, or split the query up a bit.

While you can do this:

Private Sub Locked_GotFocus()
On Error Resume Next
Dim myRecord As Long
myRecord = Me.CurrentRecord
DoCmd.SetWarnings False
DoCmd.RunSQL "Update TableA Set Locked = " & IIf([Locked], "False", "True") & " Where Seg = " & Chr(34) & [Seg] & Chr(34)
DoCmd.SetWarnings True
DoCmd.RefreshRecord
DoCmd.Requery
DoCmd.GoToRecord acActiveDataObject, , acGoTo, myRecord
End Sub

Open in new window


 There are a couple of things that you need to be aware of:

1. You don't want to update the field directly via the form since you would be doing it in code.  So make sure it's not enabled and locked.

2. If you want the user to still be able to check and un-check it, you put a transparent command button over the top of it, then use the OnClick event of that to actually update the field with code.

3. I would not use DoCmd.RunSQL to execute any SQL, but rather:

   CurrentDB().Execute  <sql statement> , dbFailOnError

 as it allows you to error trap.  With warnings off, you'll never know if your statement fails.

4. You don't need the requery.  If the record is already displayed, all you need is the Refresh to pickup the new data (namely, the check box).

5. When you do the update, you want to update the actual table that has the yes/no field.  If your creating that on the fly as part of the totals query, then you can't do that.  It has to be a real field in a table somewhere.   If you don't want to modify the main table, consider creating a temp table (totals query get's turned into a make table, and your form is based on the temp table).

I also mentioned splitting up the query.   One way to make the query updateable would be to do a query that does the totaling, then create another query which includes the base table and the query you just created for the totals.

 In this way, you could update the main table, and do it directly through the form.

 Read through that, see if it makes sense, and based on what you want to do, which way you'd like to approach it.  

Jim.
0
 
LVL 40
ID: 40551531
You might be able to do it by creating a new query that links your "one" table with the result of your total query.

Create a new query.

Add to it your table and your query (you can add a query in a query - check the tabs in the window in which you select your tables).

Link both together.

Bring down the fields that you need for display, but for the seg field, make sure that you bring down the field from the table (it should be updatable) and not the one from the query (it is not).

This is seen as a one to one relation, so the table is updatable.
0
 

Author Comment

by:wellesleydpw
ID: 40605395
Jim and James,
I must not have been clear because I have my query already links my "one" table with the result of the total query.   Here's a screenshot of my queries.

Screenshots of queries

Jeff
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40605570
Jeff,

 Well I played around with this and I'm left scratching my head a bit; I don't understand why it's not updateable.  Seems it should be and I've done this in the past, but it doesn't work.

So you have a couple of choices:

1. Do it with a DMax()

screen capture
2. Make a temp table with the dates, then join to that table for the form (already mentioned).

3. Have a transparent button over the check box and when clicked, update the check box field yourself and refresh the record (again, already mentioned).

Jim.
0
 
LVL 40
ID: 40605789
From the documentation : When you set the UniqueValues property to Yes, the results of the query aren't updatable.

The reason is one of simple logic... for Access. For a given row that is displayed, there might be 2 underlying records. Because changes in changes in a query a made one record at a time, the system would not know which row you want to change. Even if there care no hidden rows, the query is automatically marked as non updatable because of the possible side effects it could cause.

UniqueValues is usually used for display purpose. When you need to update, the individual rows should be visible.
0
 

Author Closing Comment

by:wellesleydpw
ID: 40605807
Thanks Jim,

DMax it is!

Jeff
0
 
LVL 58
ID: 40605845
Just so you know, DMax() (or any domain function) inside a query is a big no-no from a performance standpoint.   The statements are un-optimizable by the query parser and will always yield poor performance as a result.

 Also, since all the domain functions represent a SQL statement, normally there is no reason to use one in a query since you can write the SQL directly.

 However is this case, as soon as I added in anything with a GROUP BY either as a sub query or a nested query, it's not updateable even though it should be from what I can see.  There's no obvious reason why the one side can't be updated as it is uniquely identified.

 But there must be something and I'm just not remembering this correctly because in looking back, I actually never did this.  I always used one of the workarounds.

And by the way, one other thing; you had a Department.* in one of the queries to select all the fields; bad habit to get into.

 Only use the fields you actually need in a query.  Don't select all by default.

Jim.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question