Multipe Joins!!!

[Kaspro via AccessMonster.com]

Hey everyone, i've sent my problem earlier but i got no answer, now what i've
been trying to do is to create a query which joins 3 tables which included a
left join and inner join, but i got an error msg and i got this help from
access help to create my query successfuly:

Query1
SELECT * FROM Orders, [Order Details],
Orders INNER JOIN [Order Details]
ON Orders. OrderID = [Order Details].OrderID;

Query2
SELECT * FROM Customers, Query1,
Customers LEFT JOIN Query1
ON Customers.CustomerID = Orders.CustomerID;

Now i've done exactly the same, and when i run my query it gives a syntax
error in join operation error!
So any body got any ideas please!!!!

 

[Dale Fye]

Try:

Query1
SELECT *
FROM Orders INNER JOIN [Order Details]
ON Orders. OrderID = [Order Details].OrderID;

Query2
SELECT *
FROM Customers LEFT JOIN Query1
ON Customers.CustomerID = Orders.CustomerID;

HTH
Dale

 

[Kaspro via AccessMonster.com]

Yes man, that's what i did in first place and still the same problem!!!

 

[Dale Fye]

Looks like there is a space between the . and OrderID in

From Orders Inner Join [Order Details]
ON Orders.OrderID = [Order Details].OrderID

If that isn't what is causing the problem, maybe you could be more specific
WRT the error you are getting

Dale

 

[John W. Vinson]

Hey everyone, i've sent my problem earlier but i got no answer, now what i've
been trying to do is to create a query which joins 3 tables which included a
left join and inner join, but i got an error msg and i got this help from
access help to create my query successfuly:

Query1
SELECT * FROM Orders, [Order Details],
Orders INNER JOIN [Order Details]
ON Orders. OrderID = [Order Details].OrderID;

Query2
SELECT * FROM Customers, Query1,
Customers LEFT JOIN Query1
ON Customers.CustomerID = Orders.CustomerID;

Now i've done exactly the same, and when i run my query it gives a syntax
error in join operation error!
So any body got any ideas please!!!!

As written you have a space between Orders. and OrderID, which would certainly
be a syntax error. Also your second query should reference Query1.CustomerID,
not Orders.customerID.

I'd really suggest removing blanks (and any other special characters) from
your table and fieldnames.

 

[Kaspro via AccessMonster.com]

Ok Guys thanks for your help, ive been able to fix this problem, but my
problem now is that i need to use this 2 queries in a form and report,
especialy i need to use Where clause in the select customers statement to
specify a specific customer chosed in the form by combo or listbox in my form,
so how can i imort these two queries in my form and a report?!!!!

 

[KenSheridan via AccessMonster.com]

I can't see why you need the two queries. As a row in Orders with no
matching row in Order Details would not make a lot of sense I'd have thought
you could use two outer joins. Here's a simple example, using the Northwind
tables, which references a combo box on a form as a parameter:

SELECT CompanyName, OrderDate, [Order Details].ProductID
FROM (Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
LEFT JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.CustomerID = Forms!MyForm!cboCustomerID;

The query can then be used as the RecordSource of a form and/or report.

If you do stick with your two queries just include the WHERE clause in Query2
and make it the RecordSource of a form and/or report.

If the combo box is in the form to which the query is bound then requery the
form in the combo box's AfterUpdate event procedure with:

Me.Requery

Ken Sheridan
Stafford, England

 

[Kaspro via AccessMonster.com]

Thanx man, i'm gonna try your way...the thing is that i got three tables
(Expenses, Payments, Owners) the Payments table is the middle table between
expenses and owners, and i want to get all expenses and payments for each
owner, and what i wanted is to get all payed and unpayed expenses, so for now
i think everything is going well, and if there's anything i'm gonna post back
here, Thank you People!

I can't see why you need the two queries. As a row in Orders with no
matching row in Order Details would not make a lot of sense I'd have thought
you could use two outer joins. Here's a simple example, using the Northwind
tables, which references a combo box on a form as a parameter:

SELECT CompanyName, OrderDate, [Order Details].ProductID
FROM (Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
LEFT JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.CustomerID = Forms!MyForm!cboCustomerID;

The query can then be used as the RecordSource of a form and/or report.

If you do stick with your two queries just include the WHERE clause in Query2
and make it the RecordSource of a form and/or report.

If the combo box is in the form to which the query is bound then requery the
form in the combo box's AfterUpdate event procedure with:

Me.Requery

Ken Sheridan
Stafford, England

Ok Guys thanks for your help, ive been able to fix this problem, but my
problem now is that i need to use this 2 queries in a form and report,
especialy i need to use Where clause in the select customers statement to
specify a specific customer chosed in the form by combo or listbox in my form,
so how can i imort these two queries in my form and a report?!!!!