Results 1 to 2 of 2
  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2017

    How to find un-available date on database ?

    I use access db, i use this query :
    "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 ?

  2. #2
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008

    Re: How to find un-available date on database ?

    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.
    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
    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);
    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;
    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;
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.