Combining tables in a query

[Guest]

I am having a bit of difficulty. I have built all the tables that I need (I
think) for my spare parts database. It includes information like part number,
description, initial qty, qty ordered, qty used, initial cost, new cost .....
I am trying to get a few of these tables to link together so that I can
calculate the total qty on hand, and the total value of all the parts on
hand.

Whenever I go to make a query to calculate the qty on hand, the only parts
that show in the query are parts that have an initial qty, an ordered qty and
a used qty. If the part has never been used, or hasn't been reordered since
the original stockpile, no values are filled in. I have "tried" to weed out
null values to see if that would help, but I don't believe I am doing it
right.

I believe the problem is that they are all linked by the part number, but
not all the tables contain that part number yet. Any suggestions on how to
get around having to put each part into all the tables with zero as the qty?
Thanks

 

[Guest]

It happen when you join the tables in Inner Join, which display all the
records that are common all of the tables, if you have a Main table that you
want to display all the records from it, and all of the matching data in a
second table, you need to change the link between the table to Left or Right
Join.

While in query design, double click on the line that link the two table, you
have a form come up, in it select the option that include all the records
from the main table.

 

[Guest]

Thank you! That got most of my problem resolved. Now I know that I had no
clue where to get rid of null values (my final calculation is blank because
two of the fields don't have data yet). Where in the query do I tell it to
not count null values?

Here is what I have:
Qty, SumOfQtyReceived, SumOfQtyUsed, QtyOnHand:
[Qty]+[SumOfQtyReceived]-[SumOfQtyUsed]

The SumOfQtyReceived and Used columns are blank for many rows which results
in the the null QtyOnHand.

Matt

 

[Guest]

Use the Nz function to replace Null with zero's

QtyOnHand: Nz([Qty],0)+Nz([SumOfQtyReceived],0)-Nz([SumOfQtyUsed],0)


--
\\// Live Long and Prosper \\//
BS"D

Thank you! That got most of my problem resolved. Now I know that I had no
clue where to get rid of null values (my final calculation is blank because
two of the fields don't have data yet). Where in the query do I tell it to
not count null values?

Here is what I have:
Qty, SumOfQtyReceived, SumOfQtyUsed, QtyOnHand:
[Qty]+[SumOfQtyReceived]-[SumOfQtyUsed]

The SumOfQtyReceived and Used columns are blank for many rows which results
in the the null QtyOnHand.

Matt

It happen when you join the tables in Inner Join, which display all the
records that are common all of the tables, if you have a Main table that you
want to display all the records from it, and all of the matching data in a
second table, you need to change the link between the table to Left or Right
Join.

While in query design, double click on the line that link the two table, you
have a form come up, in it select the option that include all the records
from the main table.

 

[Guest]

Works great! Thanks.

Use the Nz function to replace Null with zero's

QtyOnHand: Nz([Qty],0)+Nz([SumOfQtyReceived],0)-Nz([SumOfQtyUsed],0)


--
\\// Live Long and Prosper \\//
BS"D

Thank you! That got most of my problem resolved. Now I know that I had no
clue where to get rid of null values (my final calculation is blank because
two of the fields don't have data yet). Where in the query do I tell it to
not count null values?

Here is what I have:
Qty, SumOfQtyReceived, SumOfQtyUsed, QtyOnHand:
[Qty]+[SumOfQtyReceived]-[SumOfQtyUsed]

The SumOfQtyReceived and Used columns are blank for many rows which results
in the the null QtyOnHand.

Matt

It happen when you join the tables in Inner Join, which display all the
records that are common all of the tables, if you have a Main table that you
want to display all the records from it, and all of the matching data in a
second table, you need to change the link between the table to Left or Right
Join.

While in query design, double click on the line that link the two table, you
have a form come up, in it select the option that include all the records
from the main table.

--
\\// Live Long and Prosper \\//
BS"D


:

I am having a bit of difficulty. I have built all the tables that I need (I
think) for my spare parts database. It includes information like part number,
description, initial qty, qty ordered, qty used, initial cost, new cost .....
I am trying to get a few of these tables to link together so that I can
calculate the total qty on hand, and the total value of all the parts on
hand.

Whenever I go to make a query to calculate the qty on hand, the only parts
that show in the query are parts that have an initial qty, an ordered qty and
a used qty. If the part has never been used, or hasn't been reordered since
the original stockpile, no values are filled in. I have "tried" to weed out
null values to see if that would help, but I don't believe I am doing it
right.

I believe the problem is that they are all linked by the part number, but
not all the tables contain that part number yet. Any suggestions on how to
get around having to put each part into all the tables with zero as the qty?
Thanks