Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Access Queries - Can't Update Some Information

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
cozmo_troll
Asked:
cozmo_troll
  • 3
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
PatHartmanCommented:
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
 
cozmo_trollAuthor Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
PatHartmanCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@ cozmo_troll,

did you look at the link I posted above ?

When can I update data from a query?
0
 
cozmo_trollAuthor Commented:
I did look at the Recordset and it was already set to Dynaset
0
 
cozmo_trollAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<I did look at the Recordset and it was already set to Dynaset >

did you try
Dynaset (Inconsistent Updates)
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now