Grouping Problem.

SteveG

I have two tables that have a master-detail relationship. For the purposes of this posting, I will call them the Customers table and the Orders table.

The Customers table has a primary key called CustomerID, and the Orders table has a primary key called OrderID, and a foreign key called CustomerID. Naturally, the one-to-many relationship is via CustomerID.

I have a query that pulls in the data from the Customers table, and from the Orders table using an inner join. Records are ordered by CustomerID.

In the report's Group Header, data is grouped by CustomerID. The customer data fields are in the Group Header, and the order data fields are in a Master Data band under the Group Header.

It all works fine. If one or more customer records are selected, the appropriate order data is also displayed for each respective customer.

But here's the problem...

If a customer record has no orders, *nothing* is displayed, ie. the report is totally blank. And if a combination of records are selected, ie. customers with and without orders, only the ones with orders are displayed. The others just don't appear in the report.

I have been trying to resolve this problem for a week now without any success. What could I be doing wrong?

Thanks.

Regards,

Steve

gordk

check your query sql statement.
it should look something like

select * from customer a, orders b, items c, parts d
where a.custno = b.custno
and b.orderno = c.orderno
and c.partno = d.partno
order by a.company, b.orderno

the key is the second line you only select from customer where orders exist.
in other words your join is incorrect.

technisoft

I don't know whether gordk's code will return the result you want (I doubt it) but it is also old SQL standard style code (SQL1994).
New style coding (SQL2003) which is easier to read, uses joins but you must use a LEFT OUTER JOIN in your case

select * from customer a
LEFT OUTER JOIN orders b
where a.custno = <condition> [and b.orderno = <condition>]
order by a.company, b.orderno

If a proper SQL database is used then as far as this query is concerned primary and foreign keys are totally irrelevant. IOW, even is there are no keys or indexes whatsoever, the query result would be correct albeit slower.

PS The years mentioned above may not be quite correct

gordk

Pete it does still work and is the sql of a query in the datamodule of the main demo.
the point was the last line of my response
his join was incorrect.

SteveG

Thanks technisoft and gordk,

I'm such an idiot. The answer was so obvious - I couldn't see the forest for the trees! I replaced the INNER JOIN with a LEFT OUTER JOIN and it now works perfectly. I don't know how I missed that. [img]style_emoticons/<#EMO_DIR#>/huh.gif" style="vertical-align:middle" emoid=":huh:" border="0" alt="huh.gif" /> Thanks for your help. Regards, Steve'[/img]