Subtracting two Queries

[binbin]

Hi everyone!

I need help regarding subtracting two values from two queries. I have search around and found some examples, but I could not make them work with mine because it involves SQL statements and I'm not that familiar with them.

Basically I have two queries that sums up the value from two tables. The following queries have the following fields in them.

qryQuantityA
-InvoiceID (Grouped)
-ProductID (Grouped)
-TotalQuantity (Sum)

qryQuantityB
-InvoiceID (Grouped)
-ProductID (Grouped)
-TotalQuantity (Sum)

I would like to subtract the fields "qryQuantityB.TotalQuantity" to "qryQuantityA.TotalQuantity" with InvoiceID criteria to be "1234."

I know I will indeed have to write it in SQL but I'm totally at a lost on what to do, I can add both tables, no problem, but could not make them subtract each other.

Thanks.

 

[Galaxiom]

No need for SQL. Just drag those queries into a new query in the designer just like they are tables.

Join on the InvoiceID and ProductID. Enter the criteria against InvoiceID.
Enter the subtraction expresssion in an empty column of the designer grid.

 

[binbin]

No need for SQL. Just drag those queries into a new query in the designer just like they are tables.

Join on the InvoiceID and ProductID. Enter the criteria against InvoiceID.
Enter the subtraction expresssion in an empty column of the designer grid.

Hi Galaxiom, I tried out your suggestion but did not quite work. If the join is #1, no records appeared, so I tried changing the joins to #2 and #3. Either "qryQuantityB.TotalQuantity" or "qryQuantityA.TotalQuantity" will have give a value, but not at the same time, which means I could not subtract because one of the two values would be missing.

It might be because I FAILED to mention that they are both queries of two sub-tables of a main table. Sorry.

But, it did give me some idea, I'm now trying another approach because of what I saw from the result of your suggestion. I included the main table and since the PK of the main table is existing in the two queries, I just joined the two queries to the main using InvoiceID (PK of main table).

Then I included the "qryQuantityB.TotalQuantity" to the columns and made another column that subtracts it from "qryQuantityA.TotalQuantity."

At a glance, it is working, but still too early for me to verify, since I have about 10 values to verify.

Thanks for helping, will update if the method I mentioned did work.

 

[binbin]

It did not work also, after verifying the values, it turns out that the same thing is happening with the method. Only 1 value is appearing. Took me a while to check since I have hundreds of items to check.

I'm out of ideas...

 

[mahenkj2]

can you just post main table with these two queries.

 

[binbin]

Ok, will just remove some data. It may take a while...

 

[binbin]

I tried to replicate the table, since it would be just to long to find the necessary objects in the original database. So I just made a simpler database that shows the problem.

In this database, "tblLoad" serves as the record for all products taken out of the warehouse. this table is linked to two tables. I need to subtract the two tables to get the balance quantity of the products that has been taken out. I put them in a query because tbleInvoice has a sub-table that contains the product quantity.

I was able to make a query, "qryAttemptToSubtract," which somehow works, unfortunately, when I add the tblProduct to get the product description, I can't make it work.

The database has been attached. Thanks!

 

[mahenkj2]

I think there is some improvement needed in your table relationship but since I am not an expert, I would suggest you to wait for some expert to intervene and comment.

It might be possible to get the your stated result somehow by some tricks but it is better to first improve the base.

regards.

 

[binbin]

All help are appreciated. Can you elaborate on how I can improve my table relationship? Because I'm all for improvement

 

[jdraw]

Can you describe your application/database in plain English? The fact that you have a specific design and you are using queries is HOW you have attempted to do something.
It is the WHAT of that something that we are trying to understand.

 

[mahenkj2]

As JDRAW and others might be advising you in detail, meanwhile I just produced a sample, look at tables and queries but in last it should be like what you want to achieve.

 

[binbin]

Hi everyone!

Do you guys mean the process? Here it is...

We have a warehouse of products. For a product to be taken out of the warehouse, the stockman generates a Load summary receipt which he gets from tblLoad. Now there are two ways for a "Load Ticket" to be generated, these are by:

1) Delivery.
2) Walk-in clients.

1) Delivery-In this process, the products are loaded first in a container to be delivered later to each client. The details of the Load will be entered in the "tblLoadProduct" so that there will be a record of what and how many products were loaded in the container. The invoices will be generated later when the product reaches the client. At the end of the delivery, if a number of products has been cancelled or failed to reach the client, they return to the warehouse. The stockman then encodes the invoices on the "tblLoadInvoice." This records which products were included in which invoice number. Now, in order for the stockman to check if any products were lost from transit/delivery. He has to know the total number of delivered and accepted products by the clients ("qryLoadInvoiceQty"), then subtract that total number from the Load summary ("tblLoadProduct") by matching the products and finding the difference. The result will tell the stockman if all the items were delivered, how many have returned or have been lost.

2)Walk-in clients-The only difference is that the stockman issues the Invoices first ("tblLoadInvoice"), since there is no need to put the products in a container and deliver it, the Load Ticket will only be generated at the end of the day, which will include all of the invoices genereated by walk-in clients, this way, almost always, the stockman only just need to append the products in the invoices to the "tblLoadProduct" since they will always be equal, since in theory, the stockman will be sure that the product was received by the client, since they (clients) are physically there, unlike in a "Delivery." But he still needs to subtract the products associated with "tblInvoiceDetail" to "tblLoadProduct" for record purposes.

Also to note, the "Load Ticket" means a list of products that has been withdrawn from the warehouse. It only records the quantity withdrawn or the quantity returned. To whom the products were delivered will be indicated on the invoices.

I hope this is what you guys meant, if you need to clarify anything, please do not hesitate.

@mahenkj2> Will take a peek at what you did. I'll update if it did the trick, Thanks!

 

[binbin]

Hi mahenkj2,

I looked at your sample, it would work if the process was structured like that, that is why I SHOULD have stated the whole process in detail... sorry for that mahenkj2

They need the "tblLoad" with two sub-tables "tblLoadProduct" and "tblLoadInvoice" with existing data. It is too damn scary for me to modify it, besides, I think they would not approve it. Its fine, i think what they want is do-able, I just can not quite make it work.