Costings by day query

[junction]

I'm currently putting together a database which shows out of hours costings per day.

The date is input into a table with a start time and end time. An hourly costing is then calculated (ie 3hours at £20ph = £60)

The problem I have is that the costing is different at a weekend (£20 per weekday, £30 at weekend)



I have an expressions which recognises the date and gives me the day of the week (DayOfTheWeek: Format([MyDate],"dddd")) and a table with all the days of the week and the rate per day (ie Monday - Friday £20, Saturday & Sunday £30)

What I want the query to do is use DayOfTheWeek: Format([MyDate],"dddd"), give me the day (which works) and the rate (which doesn't work)

Any ideas?

Copy of the database is attached

[pbaldy]

The nature of the join will force this to be edited in SQL view, but does this do what you want?

SELECT Format([MyDate],"dddd") AS DayOfTheWeek, EmpDates.MyDate, Day.Day, Day.Amount
FROM [Day] INNER JOIN EmpDates ON Day.Day = Format(EmpDates.MyDate, "DDDD")

[junction]

Thanks Paul. That's perfect.

Is there anyway of getting this to show in Design View?

[pbaldy]

I think it may show, but design view can't represent the join, so it will warn you. You may be able to work with it in design view. I typically set up the basic query in design view, then switch to SQL view and change the join. From then on, I work in SQL view if I need to tweak something.