return also day without condition
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
return also day without condition
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
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'.
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'.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
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
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
Thanks. What do you want to do with NOME in the query?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
You could add the condition to DS_S:
SELECT OMBRELLONI.GIORNO
FROM OMBRELLONI
WHERE OMBRELLONI.DS='S' AND OMBRELLONI.NOME="23"
SELECT OMBRELLONI.GIORNO
FROM OMBRELLONI
WHERE OMBRELLONI.DS='S' AND OMBRELLONI.NOME="23"
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
Have you tried opening Query6 after adding the condition to DS_S?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
See attached version. Open Query6.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
You entered 2021 instead of 2022 in the Criteria row of Year([Dato])
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
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?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: return also day without condition
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"
...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: return also day without condition
NO COMMENT!HansV wrote: ↑14 Feb 2022, 18:58Code: 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" ...
Only you...
Tks work like a charm