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
|
|||
|
|||
Query shows no results when using 2 tables with multiple relations
I am attempting to generate a query using two tables that have multiple
relationships. When I run the query, I know I should get some results, but don't get any. I will try to explain this as well as I can. Table 1: Supplier Information Supplier_ID Active_Supplier (Boolean) Other Fields Table 2: Item Information Received_From (one to many relationship with Supplier_ID) Date_Received Other Fields Query: The purpose of the query is to look at the Item Info table and group all items by the Received_From number, determine the date I last received any item from that supplier, and to limit the list to only those suppliers that are marked as active in the Supplier Information Table When I add Table 1 or Table 2 and add either the Supplier_ID or Received_From Field and run the query, I get the expected listing of all the ID numbers. As soon as I add the other table to the query (but even before I add an actual field) when I run the query, I get no results. One note, there are other fields in the tables that are related as well. I am not using those fields in the query, but could that be screwing me up? Thank you for any advice you can give. |
#2
|
|||
|
|||
Query shows no results when using 2 tables with multiple relations
Create the query that you want to use in the query grid, then change to SQL
View (View icon on toolbar). Copy all the text that is in that window, and paste it into your reply ... let us see the query that you're trying to run. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Aaron" wrote in message news I am attempting to generate a query using two tables that have multiple relationships. When I run the query, I know I should get some results, but don't get any. I will try to explain this as well as I can. Table 1: Supplier Information Supplier_ID Active_Supplier (Boolean) Other Fields Table 2: Item Information Received_From (one to many relationship with Supplier_ID) Date_Received Other Fields Query: The purpose of the query is to look at the Item Info table and group all items by the Received_From number, determine the date I last received any item from that supplier, and to limit the list to only those suppliers that are marked as active in the Supplier Information Table When I add Table 1 or Table 2 and add either the Supplier_ID or Received_From Field and run the query, I get the expected listing of all the ID numbers. As soon as I add the other table to the query (but even before I add an actual field) when I run the query, I get no results. One note, there are other fields in the tables that are related as well. I am not using those fields in the query, but could that be screwing me up? Thank you for any advice you can give. |
#3
|
|||
|
|||
Query shows no results when using 2 tables with multiple relat
Hi Ken,
I just figured it out. Never knew about the SQL view before. The problem was in the other fields that where also in relationships. Once I removed reference to them in the SQL view, everything worked fine. I can post the final SQL code if it is good etiquette to do so if necessary. Thank you for your reply though. "Ken Snell MVP" wrote: Create the query that you want to use in the query grid, then change to SQL View (View icon on toolbar). Copy all the text that is in that window, and paste it into your reply ... let us see the query that you're trying to run. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Aaron" wrote in message news I am attempting to generate a query using two tables that have multiple relationships. When I run the query, I know I should get some results, but don't get any. I will try to explain this as well as I can. Table 1: Supplier Information Supplier_ID Active_Supplier (Boolean) Other Fields Table 2: Item Information Received_From (one to many relationship with Supplier_ID) Date_Received Other Fields Query: The purpose of the query is to look at the Item Info table and group all items by the Received_From number, determine the date I last received any item from that supplier, and to limit the list to only those suppliers that are marked as active in the Supplier Information Table When I add Table 1 or Table 2 and add either the Supplier_ID or Received_From Field and run the query, I get the expected listing of all the ID numbers. As soon as I add the other table to the query (but even before I add an actual field) when I run the query, I get no results. One note, there are other fields in the tables that are related as well. I am not using those fields in the query, but could that be screwing me up? Thank you for any advice you can give. |
#4
|
|||
|
|||
Query shows no results when using 2 tables with multiple relat
No need for you to post the SQL statement now that you've fixed the problem.
Good luck. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Aaron" wrote in message ... Hi Ken, I just figured it out. Never knew about the SQL view before. The problem was in the other fields that where also in relationships. Once I removed reference to them in the SQL view, everything worked fine. I can post the final SQL code if it is good etiquette to do so if necessary. Thank you for your reply though. |
Thread Tools | |
Display Modes | |
|
|