|
| 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
February 19th, 2010, 04:03 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Do I need a subquery?
Hi:
I am having problems creating a query to show cars with WorkOrders. The tables involved are Customers (CustomerID as PK), CustomerCars (CardID as PK, CustomerID as FK) and WorkOrders (WorkOrderID as PK, CustomerID as FK, CarID as FK). I would like to have a query as the recordsource for a listbox that shows the WorkOrderID, CustomerName, CarYear, CarMake and CarModel. My problem is that I keep getting all of the customers' cars with the same WorkOrderID. ie If I am the customer and I have 5 cars listed in CustomerCars they all show in the query and I only want the one associated with the WorkOrderID. Any help is greatly appreciated. I hope that I have explained the probelm well enough. Please let me know if there are additional questions. Thanks, Barry 
|
|
#2
February 19th, 2010, 05:49 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Do I need a subquery?
Try this --
SELECT WorkOrderID, CustomerName, CarYear, CarMake and CarModel FROM (WorkOrders INNER JOIN Customers ON WorkOrders.CustomerID = Customers.CustomerID) INNER JOIN CustomerCars ON WorkOrders.CarID = CustomerCars.CarID WHERE WorkOrderID = [Forms]![YourFormName]![Listbox]; -- Build a little, test a little. "Barry" wrote: Hi: I am having problems creating a query to show cars with WorkOrders. The tables involved are Customers (CustomerID as PK), CustomerCars (CardID as PK, CustomerID as FK) and WorkOrders (WorkOrderID as PK, CustomerID as FK, CarID as FK). I would like to have a query as the recordsource for a listbox that shows the WorkOrderID, CustomerName, CarYear, CarMake and CarModel. My problem is that I keep getting all of the customers' cars with the same WorkOrderID. ie If I am the customer and I have 5 cars listed in CustomerCars they all show in the query and I only want the one associated with the WorkOrderID. Any help is greatly appreciated. I hope that I have explained the probelm well enough. Please let me know if there are additional questions. Thanks, Barry
|
|
#3
February 19th, 2010, 06:47 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Do I need a subquery?
Karl:
Thanks for the reply. It is working for my WorkOrders table however, it is not working on my Estimates table. I will need to check that table and see if I have some sort of corruption going on there. Again, thanks for your help. Sincerely, Barry "KARL DEWEY" wrote: Try this -- SELECT WorkOrderID, CustomerName, CarYear, CarMake and CarModel FROM (WorkOrders INNER JOIN Customers ON WorkOrders.CustomerID = Customers.CustomerID) INNER JOIN CustomerCars ON WorkOrders.CarID = CustomerCars.CarID WHERE WorkOrderID = [Forms]![YourFormName]![Listbox]; -- Build a little, test a little. "Barry" wrote: Hi: I am having problems creating a query to show cars with WorkOrders. The tables involved are Customers (CustomerID as PK), CustomerCars (CardID as PK, CustomerID as FK) and WorkOrders (WorkOrderID as PK, CustomerID as FK, CarID as FK). I would like to have a query as the recordsource for a listbox that shows the WorkOrderID, CustomerName, CarYear, CarMake and CarModel. My problem is that I keep getting all of the customers' cars with the same WorkOrderID. ie If I am the customer and I have 5 cars listed in CustomerCars they all show in the query and I only want the one associated with the WorkOrderID. Any help is greatly appreciated. I hope that I have explained the probelm well enough. Please let me know if there are additional questions. Thanks, Barry
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
