Query Help - "This recordset is not updateable" issue

[milodm]

Hello smart people who know a lot more about this stuff than I do!! I need some help on a project I am trying to accomplish.

I am building an Access 2007 database that will allow multiple users the ability to track and update Open Orders that need to be billed or have not been billed for some reason.

All Open Orders are housed in one "MASTER" table. Based on that "MASTER" table I have 3 different queries built
- TOP20 (Top 20 orders by value and age of order)
- TOP100 (Next 100 orders excluding the TOP20 by value and age)
- AGE < 0 (All orders that less than 0 age)

All these queries (each have a form created for them) need to have the ability to allow user to enter information into them providing status or other info about the Order and why it hasn't been billed etc.

I have the ability to edit in my TOP20 and AGE < 0 queries because those queries I can create based exclusively on the "MASTER" table but where I am running into an issue is my TOP100 query.

The TOP100 query is an unmatch query, it pulls the next 100 Orders by value and age and excludes Orders in the TOP20, this query uses the MASTER table and the TOP20 query based on the PK COMBO. My SQL is below

SELECT TOP 100 MASTER.OODATE, MASTER.COMBO, MASTER.[Customer Name], MASTER.City, MASTER.State, MASTER.[Customer Number], MASTER.[Order Number], MASTER.[Quote#], MASTER.[Customer PO], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS, MASTER.[LAST UPDATED DATE], MASTER.DEAD
FROM MASTER LEFT JOIN TOP20 ON MASTER.COMBO=TOP20.COMBO
WHERE (((MASTER.AGE)>=0) AND ((TOP20.COMBO) Is Null))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

The problem with this query is because I am using both the MASTER table and the TOP20 query to get my desired results, my users are not allowed to enter information, I get the message "This recordset is not updateable".

I have tried multiple different ways around this and researched this issue but still don't have a solution to my problem. Does anyone know how I can get this query to work or can you provide some suggestions on a work-around?

Thank you in advance for your help

 

[Mihail]

Take a look here.
I think that the 6th paragraph is for you.

 

[jdraw]

This link may help you
http://allenbrowne.com/ser-61.html

Ooops I think Mihail has directed you to the same info.

 

[milodm]

So if I am reading that link correctly, I should look at doing this via a subquery verses a join like I have this query setup now?

The problem with that is my SQL knowledge is at best marginal, since reading the post I have been trying to get it to work and I still have no results and I am getting frustrated! :banghead: Any other suggestions anyone can offer for me to try, I appear to be stuck.

My need is to have this query pull the TOP100 orders excluding the TOP20 orders identified by the TOP20 query on the Primary Key of COMBO which is in both the MASTER table and TOP20 query and the main part is this query needs to have the ability to allow the recordset to be updateable.

 

[Pat Hartman]

A subquery won't necessarily help. Is the Top20 query updateable? You need to drill down until you find the query that is making the top100 query not updateable.

 

[madefemere]

Hello,

If I well understand, your users update records directly in the query?
Why don't you use forms?
A form based on your TOP100 query named Frm_TOP100 for e.g.. Another based on table MASTER (Frm_MASTER).
So when they find the record that must be updated in the Frm_TOP100, they open the Frm_MASTER at the searched record. I think they can do their update here.

 

[milodm]

Thank you very much for responding and helping me out with this issue! You will have to excuse me for my stupidity, I am a self taught Access user and I have had one very general SQL class so I am limited in expertise!

To answer the first question, yes the TOP20 query is updateable and the only reason why I believe it is updated is because it is based on the MASTER table only. My TOP100 query is currently using the MASTER table plus the TOP20 query and the only reason I am bringing in the TOP20 query is so I can exclude all those records from being brought into my TOP100 results...I hope that makes sense. In my MASTER table all queries the primary key is the COMBO field. And from the reading and web searching I have done on this topic, from my understanding when you are using multiple tables/queries to create a query, that fact is why the query gets the dreaded "This recordset is not updateable" issue. I am not 100% sure that is correct so that is why I am seeking out help from more experienced developers on possible solutions.

Regarding the forms question. As I stated previous, I have one MASTER table and then 3 queries and each query has a form built off the query that I want my user to use when updating the Open Order db.

- The TOP20 Form (pulling the data from the TOP20 query based on the MASTER table) is updateable
- The TOP100 Form (pulling the data from the TOP100 query based on the MASTER table and the TOP20 query) is not updateable because of the issues stated previously
- The AGE < 0 Form (pulling the data from the AGE<0 query based on the MASTER table) is updateable

Having the user look at the TOP100 and find the record that needs updated and then make those updates in a form created for the MASTER table is an option but the end users have little to know Access/db knowledge so I am trying to make this as simple as possible.

Just so all the relevant info is given, my TOP20 query SQL is below and again, this is being done via Access 2007

SELECT TOP 20 MASTER.OODATE, MASTER.COMBO, MASTER.[Customer Name], MASTER.City, MASTER.State, MASTER.[Customer Number], MASTER.[Order Number], MASTER.[Quote#], MASTER.[Customer PO], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS, MASTER.[LAST UPDATED DATE], MASTER.DEAD
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

If it would help, I can take screen shots of my TOP100 query in design view in Access so you can look at that?

There has to be a way to do this...and knowing my luck it is probably simple but I'll be damned if I can figure it out!

Any help/suggestions are appreciated!!

 

[Pat Hartman]

Does your table have a primary key defined? Is Master.Combo that PK?

 

[milodm]

Yes Pat, PK is MASTER.COMBO

 

[Pat Hartman]

Well, I guess you're going to have to post your database so someone can look at it. Remove any sensitive data, compact it, and zip it before uploading.

 

[milodm]

Okay, it may take me a half day or so to remove the sensitive data and then back fill with data to make it relevant.

Thanks in advance for the help