How to find un-available date on database ?

I use access db, i use this query :

Code:

---

"select top 1 no_booking from booking where date_service in (select top 1 date_service from booking group by date_service having count (date_service) < 4) order by no_booking desc"

---

, i got new problem !
when the condition data on table booking like this :
+------------+--------------+
| no_booking | date_service |
+------------+--------------+
| AN02091701 | 02/09/2017 |
| AN02091702 | 02/09/2017 |
| AN02091703 | 02/09/2017 |
| AN02091704 | 02/09/2017 |
| AN03091701 | 03/09/2017 |
| AN03091702 | 03/09/2017 |
| AN03091703 | 03/09/2017 |
| AN03091704 | 03/09/2017 | --> date_service 04/09/2017 (is doesn't have no_booking)
| AN05091701 | 05/09/2017 |
| AN05091702 | 05/09/2017 |
| AN05091703 | 05/09/2017 |
| AN07091701 | 07/09/2017 |
+------------+--------------+
i know my query just select to AN05091703 on date 05/09/2017,and the next command i make next no_booking AN05091704 .
but i want too select or find date 04/09/2017,and then make new no_booking AN04091701 .
assumtion we don't know un-available date..
how to make correct query ?

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;

---