Join expression not supported

[Aerowolf]

Here's what I've got...

2 tables and a query:
qry has list of store #s with address (store# is primary key).
tblOrders has orders
tblOrderDetails has order details

Here's the Record Source setting in the report property:

SELECT [qry].[STORE#], [qry].[TYPE], [qry].[CATEGORY], [qry].[CITY], [qry].[ST], [qry].[POSSESSION], [tblOrders].[PO#], [tblOrders].[DUEDATE], [tblOrderDetails].[PARTID] FROM ([qry] INNER JOIN tblOrders ON [qry].[STORE#]=[tblOrders].[STORE#]) INNER JOIN tblOrderDetails ON [tblOrders].[ORDERID]=[tblOrderDetails].[ORDERID];

The result is only a list of stores that have orders. When I try to set the join property to #2 (left?) for the join between the qry and the Orders table and try to save, I get the error "Join expression not supported".

I want to be able to show all of the qry along with associated orders and associated order details.

What am I doing wrong? Is this not possible?

Edwin

 

[jiqjaq]

Yes, you can do this but you must if you make the first Join a Left Join, then the second Join must also be a Left Join.

The first Left Join would include stores without orders. If they have no orders, then they can't be Inner Joined to the Details, since the details are related to the order, which might not exist (Null).

You can do Inner and Outer (Left/Right) Joins in the same query but you can't do an Inner on the Outer. (Hope that makes sense.)

Change both the joins to Left Outer Join and it should be okay.

 

[Aerowolf]

This worked great! Thanks very much.

Another question.

This report now shows every store along with their order (if any) and the order details. Some orders have more than one entry in the detail section. As you would expect, each detail entry is shown on a separate line.

Is there any way to show multiple orderdetail entries for the same order on the same line?

ie.

Store# PO# Item1 Item2 Item3

Thanks again.

Edwin

 

[JeremyNYC]

Edwin,

Look into crosstab queries. That's the way to turn rows into columns.

But in general, using databases, what you'll end up doing is using a subform to display the details, and just not include the detail in what you pull over for the form. If you're doing a report you can either do the same thing or just use the grouping function to make the data land in the correct place, having the top level info only show once, with a detail that shows all of the detail information.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Aerowolf]

The crosstab query worked perfectly. I got just what I wanted.

Thanks very much!

Edwin

 

[Aerowolf]

Another question regarding the working crosstab query.

I've created a report using the crosstab query.
In the page footer I'm trying to sum() the Item columns but I get an #Error.

Any ideas?

Thanks

Edwin