I have a main table, ordered by customer #. I have two sub tables, linked to the main table. The main table shows customer name, address. The first sub table is an order table, showing date, product #. The second sub table is a notes table regarding different complaints/suggestions from the customer, showing date, note. The two sub tables are linked in a one to many relationship with the main table, so one customer can have multiple orders and multiple notes.

When I try and do a complete report, it seems like I'm doing a cartesian join on the two subtables, as I'm getting something that looks like:
Name
Order 1 Note 1
Order 1 Note 2
Order 1 Note 3
Order 2 Note 1
Order 2 Note 2
Order 2 Note 3
Order 3 Note 1
Order 3 Note 2
Order 3 Note 3

What I want it to do is to sort all by date, doing an inner join on the two sub tables, like so:
Name
Order 1
Note 1
Note 2
Order 2
Order 3
Note 3
(Note that the first and second notes have dates that fall between first and second orders.)

Here's the SQL statement, AI is the main table, CI and NI are the two sub tables:
SELECT [AI].[Last Name], [AI].[First Name], [AI].[SS#], [CI].[Class Name], [CI].Date, [CI].Grade, [NI].Date, [NI].Note, [NI].[Eff Date]
FROM ([AI] INNER JOIN [CI] ON [AI].[Soc Sec #] = [CI].[Soc Sec]) INNER JOIN [NI] ON [AI].[Soc Sec #] = [NI].[Soc Sec]
ORDER BY [AI].[Last Name], [AI].[First Name];

Since this question is about modifying the above SQL statement (as I'm not sure how to fix it in Acess itself), I think it's best suited to this forum. This thread was originally posted in the Access forum at http://www.access-programmers.co.uk/forums/showthread.php?p=458659#post458659

Even just a general format for something like this would be incredibly helpful.

I do not think you will be able to format a query output like that, you will need to use a report to group your data like that.