Access SQL query

[oracle128]

Ok, in an Access database, I have the following tables:
tbl_staff
staff_id
first_name
surname

tbl_booking
booking_id
booking_date
cost
staff_id (FK)

tbl_weekdates
weekdate_id
weeknum
weekending
year (follows financial year)

Now what I need to do is create a report that will group each staff member, where each member has a list all the weeks of a particular year from the weekdates datable, and their total sales for that week.

Before anyone asks, yes, tbl_weekdates is needed - not only does it follow the financial year, but it also needs to excludes weeks that weren't worked (eg Christmas) from any calculations, and it's unpredictable.

So far, I'm trying to construct the base query for it, but access doesn't seem to like any of the joins or grouping I'm doing, especially Right Joins. The number of records returned by the query should be the product of staff and weekdates, with each of these weeks also summing the cost. Grouping by staff member will be done in the report.

So, if there are 2 staff, and 50 weekdates in the 2006 financial year, and let's say each staff member has made two sales each, in the first and second weeks, a query where tbl_weekdates.year=2006 should return 100 records like follows:

staff_id, weeknum, total_sales
1, 1, $100
1, 2, $300
1, 3, 0
...
1, 50, 0
2, 1, $157
2, 2, $250
2, 3, 0
...
2, 50, 0

Just how in the hell could I construct that query? Everything I've tried using RIGHT JOINs has either produced Access errors ("Join expression not supported"), or an odd result. The closest I can get is:SELECT s.staff_id,w.weeknum FROM tbl_staff s,tbl_weekdates w
WHERE w.year=2006Which returns the right number of records, but as soon as I try to introduce the cost it screws up one way or another (I'm guessing because the two tables are selected seperately, it can't form a relationship between them via tbl_bookings).
I know the join for staff->booking is tbl_staff s INNER JOIN tbl_booking b ON s.staff_id=b.staff_idAnd I know the join for booking->weekdate is tbl_bookings b RIGHT JOIN tbl_weekdates w ON b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w.weekending)(with RIGHT JOIN questionable, it may need an INNER when everything's put together). Those are the easy parts, and individually they work fine, but putting them all into one query, I can't figure out how it should go.

[smurfy]

That's what they invented Crystal for ;)

I'd have to have a sample data set to work from but I'm not sure it's even possible to do the kind of join on "between" criteria the way you need to.

What I'd seriously look at is a 4th table of:

tbl_datelookup
date
weeknum (FK)

[oracle128]

The "between" Join (for connecting a booking to a week) works fine by itself, so I don't see why it'd be causing problems when joined with the staff ID. Likewise, the 3 of them join together just fine, as long as I'm using inner joins:SELECT s.staff_id,sum(b.cost),w.weeknum
FROM tbl_staff s INNER JOIN (tbl_bookings b INNER JOIN tbl_weekdates w ON b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w* .weekending)) ON s.staff_id=b.staff_id
GROUP BY s.staff_id,w.weeknum,w.year
HAVING w.year=2006gives a partially desired result. However, as soon as I introduce any combination of RIGHT/LEFT/FULL joins to get it to select ALL staff, and for ALL dates, that's when I start getting problems.

[smurfy]

I don't use Access much (can't you tell) but do use SQL queries 100 times a day.

Couple of thoughts (now that I'm thinking a little clearer):
1) Why are you including the staff table? The report is using staff_id, not name? Either include the name instead of the id in the query/report or ditch the table from the query.
2) Personally, I usually define my joins within the WHERE or HAVING clauses (depending on whether there is any aggregation). The M$ course I did years ago (SQL2000) suggested specific "JOIN" statements were slightly more efficient but I've never seen it in practice, although again, I'm not normally using Access.

i.e.
SELECT
s.staff_id, s.surname & "," & s.first_name,w.weeknum,sum(b.cost)
FROM tbl_staff s, tbl_bookings b, tbl_weekdates w
GROUP BY s.staff_id, s.surname & "," & s.first_name,w.weeknum
HAVING w.year=2006 AND s.staff_id=b.staff_id AND (b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w.weekending))

[oracle128]

1) Because, eventually, I'll be including the staff name. Which specific field it uses right now is irrelevant. It also needs to use the staff table, because the report will have to show one set of weeks for the year, for each staff member, regardless of whether or not they have made any bookings. Without a link (or, more appropriately, an outer join) to this table, there's no way to ensure every staff member gets their listing of weeks.
2) Yes, it could be done that way, and I've heard tell that Access isn't any more efficient with JOINs than other alternatives (nested SELECTs or WHERE clauses), but I can't see how rearranging the statement will help the problem, which I believe lies in the correct use of OUTER joins, which I don't think a WHERE clause can replicate.

SELECT s.staff_id, s.surname & "," & s.first_name,w.weeknum,sum(b.cost)
FROM tbl_staff s, tbl_bookings b, tbl_weekdates w
GROUP BY s.staff_id, s.surname & "," & s.first_name,w.weeknum,w.year,b.booking_date,w.wee kending,b.staff_id
HAVING w.year=2006 AND s.staff_id=b.staff_id AND (b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w.weekending))

[oracle128]

Nevermind, I got something working. Ugly, but it should do.
SELECT s.staff_id,sum(b.cost),w.weeknum
FROM tbl_staff s INNER JOIN (tbl_bookings b INNER JOIN tbl_weekdates w ON b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w.weekending)) ON s.staff_id=b.staff_id
GROUP BY s.staff_id,w.weeknum,w.year
HAVING w.year=2006

UNION SELECT s.staff_id, 0, ww.weeknum FROM tbl_weekdates ww, tbl_staff s WHERE
NOT EXISTS (
SELECT s.staff_id,w.weeknum
FROM tbl_staff s INNER JOIN (tbl_bookings b INNER JOIN tbl_weekdates w ON b.booking_date<=w.weekending AND b.booking_date>DateAdd("d",-7,w.weekending)) ON s.staff_id=b.staff_id
GROUP BY s.staff_id,w.weeknum,w.year
HAVING w.year=2006 AND s.staff_id = s.staff_id AND w.weeknum = ww.weeknum
)
AND ww.year=2006
ORDER BY staff_id, weeknum;