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:
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:
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:
Query 1
Query 2
Query 3
Query 4
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:
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:
Here is the Record Source for that form:
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
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:
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
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:
- Find all Delivered Products that have Invoices against them
- Find all Delivered Products that have Credit Note records against them
- Using the query in (2) find all Delivered Products that are not in the list of those that have Credit Notes
- 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:
- Display list of all Delivered Products that have Invoices but no Credit Notes
- Enter Claim amounts against multiple records as required
- Click a button to run through those updated records and using the newly entered Claim amounts generate a Credit Note record
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:
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: