i have a number of ombrella free?

[sal21]

i need to check if in FILA=1, have a free number of ombrella beach by GIORNO=01/08/2021, in my case have a number 20 ombrella...

FILA is the number of row.
NUMERO is the number of ombrella per row (FILA)

for each FILA I CAN HAVE A FIXED NUMBER OF OMMBRELLA=50

I i need a SQL, to select all number of ombrella free, by FILA...

in my case FILA 1 have only a free NUMERO=20

[HansV]

See the attached database. I created two auxiliary tables and a query.
The query returning the free umbrellas is LIBERO.

OMBRELLONI.zip

[sal21]

See the attached database. I created two auxiliary tables and a query.
The query returning the free umbrellas is LIBERO.


OMBRELLONI.zip

[sal21]

See the attached database. I created two auxiliary tables and a query.
The query returning the free umbrellas is LIBERO.


OMBRELLONI.zip

OPS...
i have just modified my post when you write a solution ...

i need to add also a GIORNO in the where clausole, sorry..

for example ... WHERE OMBRELLONI.GIORNO=15/08/2021 ...

TKS.

[HansV]

See below.

OMBRELLONI.zip

[sal21]

See below.


OMBRELLONI.zip

TKS, work.

But in this case, with the new sql, i can use, also a between statement from two days, or not?

[HansV]

Yes, replace

WHERE OMBRELLONI.GIORNO=#8/01/2021#

with for example

WHERE OMBRELLONI.GIORNO BETWEEN #8/01/2021# AND #8/12/2021#

Remember that you must specify dates in US format m/d/yyyy in SQL.

[sal21]

Yes, replace

WHERE OMBRELLONI.GIORNO=#8/01/2021#

with for example

WHERE OMBRELLONI.GIORNO BETWEEN #8/01/2021# AND #8/12/2021#

Remember that you must specify dates in US format m/d/yyyy in SQL.

TESTED and it work!

sorry...
if i can extract only a distinct value of FILA, based a free NUMBER?

[HansV]

What do you mean? I'm afraid I don't understand.

[sal21]

What do you mean? I'm afraid I don't understand.

TKS.
RESOLVED MY SELF.

[sal21]

Yes, replace

WHERE OMBRELLONI.GIORNO=#8/01/2021#

with for example

WHERE OMBRELLONI.GIORNO BETWEEN #8/01/2021# AND #8/12/2021#

Remember that you must specify dates in US format m/d/yyyy in SQL.

SELECT DISTINCT COMBI.NUMERO
FROM COMBI LEFT JOIN (SELECT * FROM OMBRELLONI WHERE OMBRELLONI.FILA=2 AND OMBRELLONI.GIORNO=#08/01/2021#) AS Q ON (COMBI.FILA = Q.FILA) AND (COMBI.NUMERO = Q.NUMERO)
WHERE Q.FILA IS NULL
ORDER BY COMBI.NUMERO

why have a complete list 50!

see image with filter on FILA and GIORNO

[HansV]

What is the problem? I see only 5 records in the screenshot.

[sal21]

What is the problem? I see only 5 records in the screenshot.

yes...
but 50-5(not freeable)=45... the query return a complete list of 50 free

[HansV]

Sorry, I don't understand. Could you attach the database with the query?

[sal21]

Sorry, I don't understand. Could you attach the database with the query?

SELECT DISTINCT COMBI.NUMERO
FROM COMBI LEFT JOIN (SELECT * FROM OMBRELLONI WHERE OMBRELLONI.FILA= 2 AND OMBRELLONI.GIORNO=#8/01/2021#) AS Q ON (COMBI.FILA = Q.FILA) AND (COMBI.NUMERO = Q.NUMERO)
WHERE Q.FILA Is Null
ORDER BY COMBI.NUMERO

I need to return in a distinct set of a free NUMERO of OMBRELLONI based FILA=2 and GIORNO=01/08/2021.

in this case are not free only 5 NUMBER (you see in screenshot)

[HansV]

SELECT DISTINCT COMBI.NUMERO
FROM COMBI LEFT JOIN (SELECT * FROM OMBRELLONI WHERE OMBRELLONI.FILA= 2 AND OMBRELLONI.GIORNO=#8/01/2021#) AS Q ON (COMBI.NUMERO = Q.NUMERO) AND (COMBI.FILA = Q.FILA)
WHERE (((COMBI.FILA)=2) AND ((Q.FILA) Is Null))
ORDER BY COMBI.NUMERO;

[sal21]

SELECT DISTINCT COMBI.NUMERO
FROM COMBI LEFT JOIN (SELECT * FROM OMBRELLONI WHERE OMBRELLONI.FILA= 2 AND OMBRELLONI.GIORNO=#8/01/2021#) AS Q ON (COMBI.NUMERO = Q.NUMERO) AND (COMBI.FILA = Q.FILA)
WHERE (((COMBI.FILA)=2) AND ((Q.FILA) Is Null))
ORDER BY COMBI.NUMERO;

But this query return a list of 50 NUMERO free!(immagine.jpg)

i need a list of 45 NUMERO free!

see the immagine1.jpg are the NUMERO just not free

i need
1
2
3
4
5
6
7
8
9
10
11
13
14
15
16
18
20
....
38
40
41
43
...
50

in my report(immagine2.jpg) the blank cell are the free NUMERO free

[HansV]

S0735.png

[HansV]

Here is the database with the query.

OMBRELLONI.zip

[sal21]

Here is the database with the query.


OMBRELLONI.zip

I'm sorry but I did it all without attention

i used your query in a very very old database...

Your sql work like a charm!

Sorry