Solved Query Limiting the Records Displayed Doesn't Allow Me to Make Edits to the Records (1 Viewer)

LGDGlen

Member
Local time
Today, 18:09
Joined
Jun 29, 2021
Messages
153
Hi All

need some assistance in understanding and help with directions on how best to achieve the outcome i require

here is a diagram to try to describe the situation i have to cope with:

1634630525015.png


What we have is a consignment of products that get split into deliveries to clients and each delivery can have parts or all of the products that make up the consignment. So in the above:
  • 1 Consignment made up of 2 products from a supplier
  • 3 Deliveries to customers
    • Delivery 1 uses some of product 1
    • Delivery 2 uses some of product 1
    • Delivery 3 uses the last of product 1 and all of product 2
  • Each delivered product will have an invoice from us
  • Each delivered product will potentially have a credit note from us based on customer experience
Previously I had a question about trying to find something thats NOT there https://www.access-programmers.co.uk/forums/threads/query-to-find-something-thats-not-there.319881/

This question was to enable the users of my database find Delivered Products that have not had invoices raised against them. That works great and I have created a process with displays the list of Delivered Products that have no invoices and the users can select some and generate invoice records for them

This has now moved me onto the next step in the process which is when there are quality issues with Delivered Products that have been invoiced that we need to raise Credit Notes against.

I have followed the same steps from the linked post to create an "unmatched query" to do:
  1. Find all Delivered Products that have Invoices against them
  2. Find all Delivered Products that have Credit Note records against them
  3. Using the query in (2) find all Delivered Products that are not in the list of those that have Credit Notes
  4. Finally using the query in (1) find all Delivered Products that are found in (3) that are also in query (1)

Query 1
Code:
SELECT [FACT-DeliveryInvoicesAndCNs].delivery_product_id
FROM [FACT-DeliveryInvoicesAndCNs]
WHERE ((([FACT-DeliveryInvoicesAndCNs].reference) Like "INV*"));

Query 2
Code:
SELECT [FACT-DeliveryInvoicesAndCNs].delivery_product_id
FROM [FACT-DeliveryInvoicesAndCNs]
WHERE ((([FACT-DeliveryInvoicesAndCNs].reference) Like "CN*"));

Query 3
Code:
SELECT [FACT-DeliveryProducts].del_prod_id
FROM [FACT-DeliveryProducts] LEFT JOIN qryAllDeliveredProductsWithCNCreditNotes ON [FACT-DeliveryProducts].del_prod_id = qryAllDeliveredProductsWithCNCreditNotes.delivery_product_id
WHERE (((qryAllDeliveredProductsWithCNCreditNotes.delivery_product_id) Is Null));

Query 4
Code:
SELECT [FACT-DeliveryProducts].del_prod_id
FROM (qryAllDeliveredProductsWithoutCNCreditNotes INNER JOIN qryAllDeliveredProductsWithINVInvoices ON qryAllDeliveredProductsWithoutCNCreditNotes.del_prod_id = qryAllDeliveredProductsWithINVInvoices.delivery_product_id) INNER JOIN [FACT-DeliveryProducts] ON qryAllDeliveredProductsWithoutCNCreditNotes.del_prod_id = [FACT-DeliveryProducts].del_prod_id;

So with all these queries setup Query 4 provides me with the list of Delivered Products that I need to work with which is great and has gotten me to the point where i'm struggling.

I created a continuous form to display the list of Delivered Products and in the Record Source grabbed the FACT-DeliveryProducts table and linked it to Query 4 and displayed details on the form including the Claim Amount - del_claim in the picture included above - which is required to be updated by the user as part of the creation of the Credit Notes.

The process needed to be replicated is:

  1. Display list of all Delivered Products that have Invoices but no Credit Notes
  2. Enter Claim amounts against multiple records as required
  3. Click a button to run through those updated records and using the newly entered Claim amounts generate a Credit Note record
The problem I have is that the fields that I added to the continuous form are unable to be updated with the message "This Recordset is not updateable"

So i'm a bit stuck and not sure how to achieve the outcome i require.

Here's a very rough thrown together view of the continuous form i'm working with. I have simplified the explanation as there are 3 types of claim amounts a customer can raise to us, a claim against the quality, quantity or price of the delivered products but the overall process would be the same:
1634632436291.png


Here is the Record Source for that form:

Code:
SELECT [FACT-DeliveryProducts].*
FROM [FACT-DeliveryProducts] INNER JOIN qryAllDeliveredProductsWithINVWithoutCN ON [FACT-DeliveryProducts].del_prod_id = qryAllDeliveredProductsWithINVWithoutCN.del_prod_id;

I hope i've made this understandable as i know its always not easy to explain working practices to people who are not involved in the process so i'm sure i've not explained something properly but the TL;DR is that a query limiting the records displayed doesn't allow me to make edits to the records displayed as i require it to

Thanks in advance

Glen
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 18:09
Joined
Feb 19, 2013
Messages
13,549
the usual rule is one form one table - relationships/joins are represented by using a subform and the subform linkchild/master properties.

You could try a different sql

SELECT *
FROM [FACT-DeliveryProducts]
WHERE EXISTS(SELECT del_prod_id FROM qryAllDeliveredProductsWithINVWithoutCN WHERE del_prod_id= [FACT-DeliveryProducts].del_prod_id)
 

LGDGlen

Member
Local time
Today, 18:09
Joined
Jun 29, 2021
Messages
153
it doesn't seem to like the [FACT-DeliveryProducts].del_prod_id of the WHERE of the EXISTS clause saying
1634638414014.png


i'm guessing because the query uses the table as well there might be some confusion when its trying to resolve the EXISTS clause
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:09
Joined
May 7, 2009
Messages
14,564
change your Query to Left Join.
Inner Join will render your query non-updateable when a 1-1 join cannot be made
due to some keys missing on either table.
 

LGDGlen

Member
Local time
Today, 18:09
Joined
Jun 29, 2021
Messages
153
ok so when i change

Old SQL
Code:
SELECT [FACT-DeliveryProducts].*
FROM [FACT-DeliveryProducts] INNER JOIN qryAllDeliveredProductsWithINVWithoutCN ON [FACT-DeliveryProducts].del_prod_id = qryAllDeliveredProductsWithINVWithoutCN.del_prod_id;

to

New SQL
Code:
SELECT [FACT-DeliveryProducts].*
FROM [FACT-DeliveryProducts] LEFT JOIN qryAllDeliveredProductsWithINVWithoutCN ON [FACT-DeliveryProducts].del_prod_id = qryAllDeliveredProductsWithINVWithoutCN.del_prod_id;

I get records i don't want to see and still get the message about the recordset not being updateable

i'm sure that what i want to do is possible but i'm just not looking at things in the right way or have done something wrong with the many many many many queries i've created so if i can help more please let me know
 

LGDGlen

Member
Local time
Today, 18:09
Joined
Jun 29, 2021
Messages
153
On the left is the results of the left join, the right is the inner join.
1634641788193.png


When i've reviewed things the list of IDs on the right are exactly the right list, as you can see on the left there are claim amounts documented which means they have Credit Notes linked to them as well
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:09
Joined
Feb 19, 2013
Messages
13,549
it doesn't seem to like the [FACT-DeliveryProducts].del_prod_id of the WHERE of the EXISTS clause saying
probably because in your qryAllDeliveredProductsWithINVWithoutCN you are bringing del_prod_id mor than once - that case use aliases - with your long table/query names would be a good practice to follow modify to

SELECT *
FROM [FACT-DeliveryProducts] A
WHERE EXISTS(SELECT * FROM qryAllDeliveredProductsWithINVWithoutCN B WHERE B.del_prod_id= A.del_prod_id)

note I have also modified the select part of the exists query
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:09
Joined
May 7, 2009
Messages
14,564
WHERE EXIST will output all the fields though.
you may also try:

SELECT *
FROM [FACT-DeliveryProducts]
WHERE prod_id IN (SELECT prod_id FROM qryAllDeliveredProductsWithINVWithoutCN)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:09
Joined
Feb 19, 2013
Messages
13,549
WHERE EXIST will output all the fields though.
Actually it doesn't, it just returns a boolean value, but you can call a single field if you want to, wont make any difference. Your method on the other hand returns all the records in the query.

Only test for efficiency is to try it and see.

@LGDGlen assuming your BE is access (jet/ace) you don't need the AS in the sql statement. I'm a lazy typer so try to keep the amount of typing to a minimum, and reduces the risk of typo's :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:09
Joined
May 7, 2009
Messages
14,564
Actually it doesn't, it just returns a boolean value,
no, it will still scan all records if it can be satisfied (if it exists).
"for each record in fact-deliveryProducts, check if there exist a record in the other table that will satisfy the criteria".
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:09
Joined
Feb 19, 2013
Messages
13,549
not going to get into an argument over this, but this is what the documentation says

EXISTS ( subquery)
Code language: SQL (Structured Query Language) (sql)
In this syntax, the subquery is a SELECT statement only. As soon as the subquery returns rows, the EXISTS operator returns TRUE and stop processing immediately.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:09
Joined
May 7, 2009
Messages
14,564
SQL for Microsoft Access:
Screenshot_1.png

Screenshot_2.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:09
Joined
Feb 19, 2013
Messages
13,549
doesn't change what I'm saying. end of discussion on my part.
 

Users who are viewing this thread

Top Bottom