By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
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

mseo
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

✓ answered by topher23

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.  

Share this Question
Share on Google+
17 Replies


topher23
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
File Type: zip Order And Production.zip (63.3 KB, 97 views)
Apr 5 '10 #2

mseo
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

mseo
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

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

topher23
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
  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.  
Apr 12 '10 #6

topher23
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
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.     If Me.DateRequired < DateAdd("d", -7, Now()) Then Me.Detail.Visible = False Else Me.Detail.Visible = True
  3. End Sub
  4.  
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

mseo
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

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

mseo
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

mseo
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

topher23
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
File Type: zip mseo_db2.zip (40.6 KB, 90 views)
Apr 19 '10 #12

mseo
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

topher23
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

mseo
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
  1. 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
  1. 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

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

mseo
100+
P: 181
hi, topher23
I explained everything about the problem in the post #15 and the attached file # 13
please tell me if i can do this, because I can't stop thinking about this report/query
I have tried to do it but I think that there is something more advanced about this report/query and I don't know about
please let me know how I can do this report/query
thank you very much
Apr 30 '10 #17

mseo
100+
P: 181
hi, topher23
thank you very much,and for saving your time, I did as you told me in your last post and by breaking the problem down into its simplest contents I found that I must create a report for orders only then create subreport that contains the of the receipts quantity for each product for specific customer
and it works fine
I will attach the file in here to share the knowledge as soon as I get this report done
thank you again and over again for your previous help
I really appreciate your help and time
May 1 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.