difficult to crteate a query very complicated 1

[2009luca]

this table contain a price list of room.
DAL and AL is the period (from>to) PREZZO is the price
Based myvarfrom="05/06/2024 and myvarto="15/06/2024" and myvaroom="SINGOLA" how to calculate the price of the living room

note:
Naturally myvarfrom and myvarto and myvaroom is dinamic

 

[Andrzejek]

Any example(s) of your attempt(s) to create this query?
Show your work.

 

[strongm]

Some questions first

1) What is your backend dbase server. I am assuming Access, but just want to check
2) Can a booking stretch across more than 1 price period. I am assuming yes, but again just want to check (since your example date do not)
3) Are the prices for a single room really the same with a mountain view as for a sea view? Or is that just your test data?
4) Are the booking periods all in the same year?

 

[2009luca]

for strongm

You have understand my question!

Yes my database is Access

For now use only a STRUTTURA=MARE

Yes,

possible date from for example:

myvarfrom=07/06/2024 myvarto=14/11/2024

...yea, now i see your face

 

[mmerlinn]

how to calculate the price of the living room

You calculate the price of any room by multiplying the length by the width by the cost per square foot.

 

[strongm]

So,whilst it is possible to do this in a relatively straightforward single query in SQL Server (due to additional features in Transact-SQL that Jet SQL does not have), it is somewhat more complex in Access .. and in fact it is easier take a slightly different approach, which is simply to iterate through each day of the booking and get that day's price and total it up.

e,.g

' Using Microsoft DAO library rather ADO, as it is fractionally faster for this than ADO/ADODB against an MDB
Option Explicit

Public Sub Doit()
    Debug.Print BookingTotal("25/08/2024", "07/09/2024")
End Sub

Public Function BookingTotal(startdate As Date, enddate As Date) As Long
    Dim db As Database
    Dim rs As Recordset
    Dim lp As Date
    Dim SQL As String

    Set db = DBEngine.OpenDatabase("d:\downloads\deleteme\test.mdb") ' replace with path to your database
    For lp = startdate To enddate
        SQL = "SELECT PREZZI.PREZZO, PREZZI.TIPO FROM PREZZI WHERE (cdate('" & lp & "') between CDate([DAL] & '/" & Year(startdate) & "') and CDate([AL] & '/" & Year(enddate) & "')) and PREZZI.TIPO='SINGOLA' AND PREZZI.STRUTTURA='MARE'"
        Set rs = db.OpenRecordset(SQL, dbOpenForwardOnly, dbReadOnly)
        BookingTotal = BookingTotal + rs("PREZZO").Value
    Next
    
End Function

 

[2009luca]

strongm, work perfect...
But the user can see the price by date to date, and the related period example:

admit have "25/08/2024" to "07/09/2024"

price from 25/08/2024 to 31/08/2024 a price for SINGOLA
price from 01/09/2024 to 07/09/2024 a price for SINGOLA

possible?

note:
in this case the room is SINGOLA

 

[strongm]

So as ever you didn't provide all your requirements!

Possible? Absolutely

But I've not got the time to look at this to provide an example just at the moment.

 

[sal21]

Sorry strongm.
No prob for time.
Tks.

 

[Andrzejek]

sal21 / 2009luca,

Based on the code strongm provided, you should be able to modify his code to get what you need.