Do I need a subquery?

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

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
 
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];
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top