Solved

Access Queries - Can't Update Some Information

Posted on 2014-03-26
8
266 Views
Last Modified: 2014-03-27
I am working on a query that has several different tables involved. Is there a way to make the query let me update information for specific data? It lets me update fine until a point where I add a specific table and then it locks everything up.
0
Comment
Question by:cozmo_troll
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39956913
you may have been using three or more tables which may have  many to one to many  relationship.

if you are using a form, try  setting the form's RecordsetType property  to Dynaset (Inconsistent Updates).

see this link for possible solution
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39957316
The number of tables is not important although I'm sure there is a limit.  I have updateable queries with more than 10 tables.  What is important is the relationships between the tables.  Generally in an updateable multi-table query, the tables are all in the same hierarchical path  although you can include lookup tables.  Where you are probably running into trouble is by including a sibling table.  For example,   You have three tables.  Students, Classes, and Vehicles.  A student takes many classes.  A student has one or more vehicles.  But, even though both Classes and Vehicles are related to Students by StudentID, they have nothing to do with each other and including both child tables in the same query would create a non-updateable query and would "duplicate" rows also since you would end up with a row for every combination of Class and vehicle.  So you have Math, Scooter; Math, Car; Math Bicycle; English, Scooter; English, Car; English, Bicycle; Science, Scooter; Science, Car;......
0
 

Author Comment

by:cozmo_troll
ID: 39957454
OK, I am working with a similar situation only for shipment tracking. Can you suggest a way to get around the result or if it just matter of fact that this will not work?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 37

Expert Comment

by:PatHartman
ID: 39957596
Get around what?  Are you trying to create a query that doesn't make sense like the one I described above with classes and vehicles?  Just because they are both related to students doesn't mean that it makes sense to include them in the same query.  We need to see your schema and may need a better description of what you need to do.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39957848
@ cozmo_troll,

did you look at the link I posted above ?

When can I update data from a query?
0
 

Author Comment

by:cozmo_troll
ID: 39959106
I did look at the Recordset and it was already set to Dynaset
0
 

Author Comment

by:cozmo_troll
ID: 39959128
Our situation is this:

We have Mother Orders, Children Orders, Mother Shipments, and Children Shipments. Children Orders from many Mother Orders can ship on one Child Shipment. I was looking for a way to pull a Query or a Form based on a Query that would let me update shipment information but at the same time view order information for that shipment.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39959175
<I did look at the Recordset and it was already set to Dynaset >

did you try
Dynaset (Inconsistent Updates)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

734 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