How to combine two query which have the same column name to be another

[djungna]

Hi friend
I need your help.
I have made 2 query (named "QryReceivesum" & "QryWithdrawsum").
But query have the same common column called "ProductId".
I want to combine 2 query to be just one.

Currently I have mae one query named "QryStock".
but the data from this qry is not correct.

As you can see, it doesn't show the number of product from productID3.


can anyone help me??

 

[Pat Hartman]

In theory you shouldn't have any withdraw transactions if you don't have any receive transactions but since these queries could be for a particular date range, you will have to worry about having one side of the equation but not the other so you'll need to use a left join.

If joining receive to withdraw with a left join doesn't return all the data, you'll need to add the product table back into the mix. Create a new query and add the product table, the withdraw query and the receive query. Draw a join line from the product table to the withdraw query and change its type to Left. Draw a join line from the product table to the receive query and change its type to Left. That will give you a query that lists all the products whether they have any recieve or withdraw activity or not.

 

[djungna]

Thank you so much

Thank you so much Hartman
it works!!!!

 

[djungna]

How to use formular with the cell without value

Hi hartman

Now I can make the summary of "Receive" and "withdraw" for each product.
In some row, there are both value for "receive" & "withdraw", so i can deduct "receive" & "withdraw".

But in some row, there is only "Recieve" and no value for "withdraw" (the cell is blank but not zero), so i can not deduct it.

How i can i make the deduction for all row, even there is no data in "receive" or "withdraw".

please see "Qrystock".

Thank you so much

 

[Pat Hartman]

You need to use the Nz() function to replace any null values with zeros.

Nz(fldA, 0) - Nz(fldB, 0)

 

[djungna]

Ask some more about NZ function

Dear Hartman
Can you please guide me little more.
How can I use this Nz function.

I have try to put this function in Properties of "SumOfReceiveQty" text box.

and this what i put in (i search from help and adapt with your command)

Dim A as variant
A = Nz(A, 0)

But it doesn't work.

Thank you in advance for your help

 

[Pat Hartman]

If the field is truely a sum, you need to go back to where the sum is calculated and modify the expression there.

If you put the function in the ControlSource property, it will look like this:

=Nz(SumOfReceiveQty,0)

Warning - if this control was originally bound. It will no longer be bound and you will have to change the control's Name if it is = SumOfReceiveQty

 

[djungna]

Ask some more about Nz(SumOfReceiveQty,0)

To HJartman
I have try by Adding =Nz(SumOfReceiveQty,0) in ControlSource property of "Frmstock" in "Sumofreceiveqty" text box.

but is show error when open the form..

Could you please advise me little more

 

[Pat Hartman]

I anticipated this problem. Read my warning again - the Name property of an unbound control may NOT duplicate the name of any field in the RecordSource. You MUST change the Name property of the control.