Sales report: Assistant info, even if they have no sales

[StevoSimpson]

Hi, I'm having a slight problem in designing a query for a report.

I am creating a sales database, and the report I need to generate should loop through all the assistants, showing their sales grouped per day, and the item included in the sale (max of one per sale).

The tables look like this:

assistants(asstID,name)
sales(date,cost,asstID*,setID*)
set(setID, price, name)

Relationships:
assistants - sales is (1 - many)
sales - set is (many - 1)

I can get this query to work, almost.
In the result set is only the assistants that have made sales, but I need them all there, even if they have made none. Will I need to change the type of Joins on the tables? I don't think it should be too hard a problem, I just have no idea atm of what to do.

Any help is much appreciated.
Steven

 

[StevoSimpson]

Ok, never mind, I seem to have got it working... doing something I'm sure I tried a few hours ago - I've been staring at this screen for too long obviously.


For anyone in the same boat, I had to use a query within a query: First off, a query that returns all information about sales and what was in the sale. Then I used assistants LEFT JOIN previousQuery, to return the required results.


Seems easy now. Was sure I had tried this before and received an unsupported JOIN error, or something along those lines.

Oh well, at least its working!!

Steven