459,963 Members | 1,815 Online
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

# Query between two tables with no relationship

 100+ P: 181 Attached to this question is a MS-Access 2003 file that contains the design of the orders and production database. The database contains query named orders follow-up I want deduct the quantity produced from the quantity ordered and get the remainder depending on the date required for each ordered product. It is now 3 weeks looking for any solution and nothing Apr 5 '10 #1

Sorry it's been a while since I was on here; I've been busy (full-time worker and student).

I'm looking at the logic in your Remainder calculated field:
Expand|Select|Wrap|Line Numbers
1. =IIf(([Quantity]-[sumofquantity])+([RunningSum]-[quantity])<0,0,([Quantity]-[sumofquantity])+([RunningSum]-[quantity]))
2.
and it doesn't make any sense. [Quantity]-[sumofquantity])+([RunningSum]-[quantity] has two [Quantity] fields which cancel each other out, leaving [SumOfQuantity]-[RunningSum]. Other than that, the report is sound.

The name "Remainder," however, could be considered misleading. At my business, we use two measurements - Quantity On Hand, which is the one I set the report up with originally, and Build Quantity, which means the amount you have to make to fill the order. I'd suggest you replace Remainder with something more like "Build Quantity."

As for how to get "Remainder" to show only the Build Quantity required for the specific order, try this:
Expand|Select|Wrap|Line Numbers
1. =IIf([RunningSum]-[Quantity]>[SumOfQuantity],[Quantity],IIf([RunningSum]-[SumOfQuantity]<0,0,[RunningSum]-[SumOfQuantity]))
2.

17 Replies

Expert 100+
P: 234
I don't know of any way to do all of this in a query, but I made a change to your query to collect the sum total of each product produced, then built a report that uses a running sum field to subtract the quantities ordered from what was produced, giving you an amount on-hand after the order is fulfilled. I'll leave it to you to figure out how to filter order completion dates for old orders and the attendent production quantity issues, but this should get you started.
Attached Files
 Order And Production.zip (63.3 KB, 97 views)
Apr 5 '10 #2

 100+ P: 181 Thank you very much Topher23 for you quick and straighforword reply I will try to think about the filtering of the order completion dates but I may need your advice thank you very very much Apr 6 '10 #3

 100+ P: 181 I did a little change in the report to get the remainder in stead of getting the on-hand After Fulfillment and i want to make the line number 3 of the product milk to be 300 rather than 446 because it would duplicate the values of the above quantities and filter the report to contain the remainder only I tried to filter the report but the running sum of order quantities will start from the first record according to the query criteria thank you for any solution you provide me Apr 7 '10 #4

 100+ P: 181 please I need any solution for doing this report Apr 7 '10 #5

 Expert 100+ P: 234 Sorry it's been a while since I was on here; I've been busy (full-time worker and student). I'm looking at the logic in your Remainder calculated field: Expand|Select|Wrap|Line Numbers =IIf(([Quantity]-[sumofquantity])+([RunningSum]-[quantity])<0,0,([Quantity]-[sumofquantity])+([RunningSum]-[quantity]))   and it doesn't make any sense. [Quantity]-[sumofquantity])+([RunningSum]-[quantity] has two [Quantity] fields which cancel each other out, leaving [SumOfQuantity]-[RunningSum]. Other than that, the report is sound. The name "Remainder," however, could be considered misleading. At my business, we use two measurements - Quantity On Hand, which is the one I set the report up with originally, and Build Quantity, which means the amount you have to make to fill the order. I'd suggest you replace Remainder with something more like "Build Quantity." As for how to get "Remainder" to show only the Build Quantity required for the specific order, try this: Expand|Select|Wrap|Line Numbers =IIf([RunningSum]-[Quantity]>[SumOfQuantity],[Quantity],IIf([RunningSum]-[SumOfQuantity]<0,0,[RunningSum]-[SumOfQuantity]))   Apr 12 '10 #6

 Expert 100+ P: 234 Regarding filtering, actually filtering dates would mess with the running sum. Absent some amazingly complex and elegant answer, here is the simple fix to keep old dates off the report: Expand|Select|Wrap|Line Numbers Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)     If Me.DateRequired < DateAdd("d", -7, Now()) Then Me.Detail.Visible = False Else Me.Detail.Visible = True End Sub   This just makes it so that, when the report is printed, any date previous to one week earlier than the print date doesn't show up on the report, but their quantities are still calculated in the Running Sum. Apr 12 '10 #7

 100+ P: 181 Thank you very much Topher23 I have never seen any answer better than this , This report is the best I meant by filtering the report, showing the items that have a value and not to show any items that equal zero thank you very much again I really appreciate your help Apr 12 '10 #8

 Expert 100+ P: 234 Not a problem. For filtering zeroes, you could use the same method as above: Expand|Select|Wrap|Line Numbers     If Me.Remainder = 0 Then Me.Detail.Visible = False Else Me.Detail.Visible = True   and that should solve all of the issues you've noted. Apr 13 '10 #9

 100+ P: 181 thank you very much that's true all your best answers have solved all the problem for this report and I have learned a lot of things from you thank you again Apr 13 '10 #10

 100+ P: 181 Hi, topher23 one more issue, I need to make the same report that topher 23 posted in this thread to calculate the build quantity based on the customer and Delivery receipts where I can subtract the quantities in the delivery receipts from the orders that ordered from specific customer I tried to do this but I have found that two customers have the same product and both of them are ordered in the same time the point that I want to know how can I insert the receipt and its products for one customer and not to be subtracted from the other customer's order the attached file contains 3 products any one can find the point which I need to know in the product (Olive) this product its order quantity for Customer:Tri-Di = 500 and for 4Mg= 6000 and the and in receipts for Tri-Di =0 and for 4Mg=300 so build quantity for Tri-Di should be 500 and 5700 for 4Mg thank you for any help you may provide me Apr 19 '10 #11

Expert 100+
P: 234
Because of filtering issues, a report seemed like it probably wasn't the best way to go about this. Instead, check out the form I put together in your database example.
Attached Files
 mseo_db2.zip (40.6 KB, 90 views)
Apr 19 '10 #12

 100+ P: 181 hi, topher23 thank you for the Form but using a Form or report are alike because the problem about the query and I need to continue using the report because if I use the form I will face the same problem with the report if I want to print the data within the form. about the filtering, now I can subtract the quantity delivered to a customer from the order of the same customer and this was the point that I mentioned in my last post, the problem now the Products in the order don't appear in the query or report only if I have receipt with the same product, I just need to view all the products in the order even if I don't have any delivering receipt I attached you a file, please help me doing this and for saving your time, the customers IDs: 79 for Tri-Di and 80 for 3Mg if you open the report using customer ID number 80 you will find product Olive and its details and if you removed the receipt for the same customer or changed the product to any other products the order won't appear the second example, the product milk within the order of Customer ID 79 but not within the report till I make receipt please help me doing this thank you very much Apr 21 '10 #13

 Expert 100+ P: 234 I have to admit that I'm completely lost as to what you are looking for. It appears I may have also misunderstood the post before this last one. I don't know what you are using the term "receipts" to apply to. Without knowing that, I can't evaluate the logic in the database that is causing the problem. This may just be a communication barrier. Try breaking it down into simple statements and stepping through the logic of what you are trying to do, explaining each table, what it's used for, why it needs to be put together into this query and what you intend to get when you put it all together... sort of like you are explaining to a small child. Apr 21 '10 #14

 100+ P: 181 hi, topher 23 I really appreciate your help thank you very much for your interest the problem in the query in this line Expand|Select|Wrap|Line Numbers HAVING (((tbl_Order.Customer_ID)=[enter cust]) AND (([tbl_Delivering Receipts].Customer_ID)=[enter cust]) this line view all the products in the Order if there is any quantity delivered from the same product if i removed this Expand|Select|Wrap|Line Numbers AND (([tbl_Delivering Receipts].Customer_ID)=[enter cust]) I will get all the receipts for all customer but the order of one customer first I receive the Order from the customer when the goods deliver to the customer he must get the receipt (delivery notice) other words receipt: is an document sends to the customer with the products he ordered and it represents an acknowledgment from the customer that he received the products in this receipt and I record this receipt (delivery note) in tbl_deliveringreceiptdetails in the report in my last post 1- I have customers, and each customer has his own Orders and each Order contains a lot of products and 2- I have customers and each customer has receipts (delivery notices) and each receipt (delivery notice) has products so the (receipts) or the quantity delivered to the customer should be subtracted from the orders (quantity Ordered) from the same customer to get the build quantity. that exactly what I need to do but i need to do one more step I need to view all the products in the order even if the customer don't have any receipt (delivery notice) because in the query or the report attached to my previous post doesn't view all the products in the order of the customer till I insert receipt ( quantity delivered) from this product to the same customer thank you very much Apr 22 '10 #15

 100+ P: 181 hi, topher23 please I need your help to do this report thank you Apr 26 '10 #16