If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combining tables in a query
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 |
#2
|
|||
|
|||
Combining tables in a query
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 "Matt T." wrote: 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 |
#3
|
|||
|
|||
Combining tables in a query
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 "Ofer" wrote: 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 "Matt T." wrote: 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 |
#4
|
|||
|
|||
Combining tables in a query
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 "Matt T." wrote: 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 "Ofer" wrote: 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 "Matt T." wrote: 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 |
#5
|
|||
|
|||
Combining tables in a query
Works great! Thanks.
"Ofer" wrote: 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 "Matt T." wrote: 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 "Ofer" wrote: 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 "Matt T." wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combining like tables in a query | Annette FA via AccessMonster.com | Running & Setting Up Queries | 2 | October 26th, 2005 01:19 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
query problem with linked SQL tables when importing to new mdb file | Keith G Hicks | Running & Setting Up Queries | 2 | March 22nd, 2005 09:44 PM |
Joining 3 tables in a query | Chrismja | Running & Setting Up Queries | 1 | March 17th, 2005 07:31 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |