return also day without condition

[sal21]

In attached file are mdb and query6.

the query6 extract number of day with a count of reservation.

the query naturally extract and count only a day with a resevation.

but i need also a day where there are no reservations...

actually have:

GIORNO NR
01/08/2021 166
03/08/2021 21
05/08/2021 18
06/08/2021 12
07/08/2021 74
12/08/2021 15

but i need:

GIORNO NR
01/08/2021 166
02/08/2021 0
03/08/2021 21
04/08/2021 0
05/08/2021 18
06/08/2021 12
07/08/2021 74
08/08/2021 0
09/08/2021 0
10/08/2021 0
11/08/2021 0
12/08/2021 15
...
31/0/2021 0

note:
the year and month and DS are dinamic

[HansV]

See the attached version.
I added a table DATI with all dates in 2021 and 2022 (you can expand it later), and a query DS_S with all records from OMBRELLONI with DS = 'S'.

OMBRELLONI.zip

[sal21]

See the attached version.
I added a table DATI with all dates in 2021 and 2022 (you can expand it later), and a query DS_S with all records from OMBRELLONI with DS = 'S'.


OMBRELLONI.zip

GREAT!

[sal21]

See the attached version.
I added a table DATI with all dates in 2021 and 2022 (you can expand it later), and a query DS_S with all records from OMBRELLONI with DS = 'S'.


OMBRELLONI.zip

hi bro, during my work i have filled a new parameter in NOME in OMBRELLONI table, is the id of each beach company

can you adjust?
tks

for example NOME is a simple number, similar 21, 34, 22...ecc

[HansV]

Can you attach a new database?

[sal21]

Can you attach a new database?

[HansV]

Thanks. What do you want to do with NOME in the query?

[sal21]

Thanks. What do you want to do with NOME in the query?

I need to use for a where clausole.
For example ...where nome='23' ecc...

[HansV]

You could add the condition to DS_S:

S1142.png

SELECT OMBRELLONI.GIORNO
FROM OMBRELLONI
WHERE OMBRELLONI.DS='S' AND OMBRELLONI.NOME="23"

[sal21]

You could add the condition to DS_S:


S1142.png


SELECT OMBRELLONI.GIORNO
FROM OMBRELLONI
WHERE OMBRELLONI.DS='S' AND OMBRELLONI.NOME="23"

NO.
i need to use also sub query6, refered to DATI.

i need a number of day.... based a new condition in NOME

see image

[HansV]

Have you tried opening Query6 after adding the condition to DS_S?

[sal21]

Have you tried opening Query6 after adding the condition to DS_S?

not completly undestand...

[HansV]

See attached version. Open Query6.

[sal21]

See attached version. Open Query6.

confused...

Attached new db.
the database now contain the original table OMBRELLONI.
instead to use NOME i have used IDAZ.

(NOME is refered to new field in table OMBRELLONI)

Use for test 23 or 21 from IDAZ and anno 2022 and month 8

[HansV]

You entered 2021 instead of 2022 in the Criteria row of Year([Dato])

[sal21]

You entered 2021 instead of 2022 in the Criteria row of Year([Dato])

DISTRACT!
Very busy with this project....
Sorry me and tks.

[sal21]

You entered 2021 instead of 2022 in the Criteria row of Year([Dato])

Tested on ide Access.
now how to use the two query in SQL, for vb6 code?

i thing i need to pass in D_S the pArameter IDAZ... FROM A CODE

IDAz Is a dinamic value.

or jouin the two query in one and have a unique query to run in SQL


or not?

[HansV]

Code: Select all

    Dim MyIDAZ As Long
    Dim SQL As String
    MyIDAZ = 23
    SQL = "SELECT DATI.Dato, Count(DS_S.GIORNO) AS Nr FROM DATI LEFT JOIN " & _
        "(SELECT GIORNO FROM OMBRELLONI WHERE DS='S' AND IDAZ=" & MyIDAZ & ")" & _
        " AS DS_S ON DATI.Dato = DS_S.GIORNO WHERE Year([Dato])=2022 AND " & _
        "Month([Dato])=8 GROUP BY DATI.Dato"
    ...

[sal21]

Code: Select all

    Dim MyIDAZ As Long
    Dim SQL As String
    MyIDAZ = 23
    SQL = "SELECT DATI.Dato, Count(DS_S.GIORNO) AS Nr FROM DATI LEFT JOIN " & _
        "(SELECT GIORNO FROM OMBRELLONI WHERE DS='S' AND IDAZ=" & MyIDAZ & ")" & _
        " AS DS_S ON DATI.Dato = DS_S.GIORNO WHERE Year([Dato])=2022 AND " & _
        "Month([Dato])=8 GROUP BY DATI.Dato"
    ...

NO COMMENT!

Only you...

Tks work like a charm