im not conversant with ms access, but i gave a try to it
i assume that your booking table must have an primary key column and it's autoincremented field and the field name of key column is ID.
*** NOTE THESE ***
There are 2 queries which will facilitate to create the date sequence tables.
Code:
save this as query by name qdxNumber10 ( and this code is not myne )
SELECT DISTINCT Abs([id] Mod 10) AS N
FROM MsysObjects;
create another query which will give you the date serials, you can manipulate this to suite your needs
Code:
save this query by name DateBank
SELECT DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) AS [Date]
FROM qdxNumber10 AS qdxNumber10_0, qdxNumber10 AS qdxNumber10_1, qdxNumber10 AS qdxNumber10_2
WHERE DateSerial(Year(Date()),1,1+qdxNumber10_0.N+qdxNumber10_1.N*10+qdxNumber10_2.N*100)<=DateSerial(Year(Date()),12,31);
Code:
now create a new query which will store your daywise , last booking invoce , booking count and booking id
save it by name DayWiseMaxBooking
SELECT Booking.date_service, Max(Booking.ID) AS MaxOfID, COUNT(Booking.ID) AS BookCount
FROM Booking
GROUP BY Booking.date_service;
Code:
now you will get datewise including with no bookings done date
save this query as MonthlyBookings
SELECT DateBank.Date, Booking.no_booking AS LastBookingId, DayWiseMaxBooking.BookCount
FROM (DateBank LEFT JOIN DayWiseMaxBooking ON DateBank.Date=DayWiseMaxBooking.date_service) LEFT JOIN Booking ON DayWiseMaxBooking.MaxOfID=Booking.ID
WHERE DateBank.Date Between #9/1/2017# And #9/30/2017 23:59:0#
GROUP BY DateBank.Date, Booking.no_booking, bookCount;