VERY STRONG query
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
VERY STRONG query
In the table ANAGRAFICA are patients
In the table PATOLOGIA are Pathologies
Mathcing IDPAT from ANAGRFICA and ID from PATOLOGIE possible to return the list related client with IDCLI 52?
In my case IDCLI 52 have:
14 IPERTENSIONE ARTERIOSA
7 DERMATITI
I hope I was clear
note:
SQL for ado connection
In the table PATOLOGIA are Pathologies
Mathcing IDPAT from ANAGRFICA and ID from PATOLOGIE possible to return the list related client with IDCLI 52?
In my case IDCLI 52 have:
14 IPERTENSIONE ARTERIOSA
7 DERMATITI
I hope I was clear
note:
SQL for ado connection
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 742
- Joined: 27 Jun 2021, 10:46
Re: VERY STRONG query
Step 1 - normalisation. You need to normalise ANAGRAFICA to at least First Normal Form
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
Speak
Not understand, your suggestioni.
Not understand, your suggestioni.
-
- 5StarLounger
- Posts: 742
- Joined: 27 Jun 2021, 10:46
Re: VERY STRONG query
I don't mean to be negative, but I think you need to be aware of some database basics before playing around with them.
Here's a starter:
Fix that, and your question becomes much easier to resolve
Here's a starter:
- A table is referred to as being in its First Normal Form if atomicity of the table is 1.
- Here, atomicity states that a single cell cannot hold multiple values. It must hold only a single-valued attribute
- The First normal form disallows the multi-valued attribute, composite attribute, and their combinations.
Fix that, and your question becomes much easier to resolve
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
@SpeakEasy: Don't you mean the IDPAT field in ANAGRAFICA?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
Code: Select all
Dim lngCLI As Long
Dim strSQL As String
lngCLI = 52
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & PATALOGIE.ID & '-*'"
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 742
- Joined: 27 Jun 2021, 10:46
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
HansV wrote: ↑25 Nov 2024, 19:54Code: Select all
Dim lngCLI As Long Dim strSQL As String lngCLI = 52 strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _ lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & PATALOGIE.ID & '-*'"
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
Try
Code: Select all
Dim lngCLI As Long
Dim strSQL As String
lngCLI = 52
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
HUMMMM...HansV wrote: ↑26 Nov 2024, 08:50Try
Code: Select all
Dim lngCLI As Long Dim strSQL As String lngCLI = 52 strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _ lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
The query work in Access ide, and in vb 6.0 sql return null recorset!
Mistery
Code: Select all
Dim lngCLI As Long
Dim strSQL As String
lngCLI = 52
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
Debug.Print strSQL
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
RS.Open strSQL, CON, adOpenForwardOnly, adLockReadOnly
Do While Not RS.EOF
Debug.Print RS.Fields(0).Value & "-" & RS.Fields(1).Value
RS.MoveNext
Loop
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
Try
Code: Select all
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
WORK!HansV wrote: ↑26 Nov 2024, 11:51Try
Code: Select all
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _ lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
tKS BRO
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
BRO...HansV wrote: ↑26 Nov 2024, 11:51Try
Code: Select all
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _ lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
instead to use the filed ANAGRAFICA.IDPAT, possible to use the value in myvar, similar "-1-12-58"
note:
In this case not important to use the where for ANAGRAFICA.IDCLI
Last edited by sal21 on 01 Dec 2024, 17:09, edited 1 time in total.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
Code: Select all
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
lngCLI & " AND '-' & " & myvar & " & '-' Like '%-' & PATOLOGIE.ID & '-%'"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
HansV wrote: ↑01 Dec 2024, 17:08But myvar should look like "1-12-58", not "-1-12-58"Code: Select all
strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _ lngCLI & " AND '-' & " & myvar & " & '-' Like '%-' & PATOLOGIE.ID & '-%'"


-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
MY SQL:
SELECT * FROM ANAGRAFICA, PATOLOGIE WHERE "-1-" Like '*-' & PATOLOGIE.ID & '-*'
in effect i need to extract * from ANAGRAFICA where in IDPAT contain 1
in my case:
54 DE RUGGIERO GIOVANNI
56 VERDI ALESSANDRO
57 GRANDE PICCOLA 9
SELECT * FROM ANAGRAFICA, PATOLOGIE WHERE "-1-" Like '*-' & PATOLOGIE.ID & '-*'
in effect i need to extract * from ANAGRAFICA where in IDPAT contain 1
in my case:
54 DE RUGGIERO GIOVANNI
56 VERDI ALESSANDRO
57 GRANDE PICCOLA 9
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VERY STRONG query
Code: Select all
strSQL = "SELECT * FROM ANAGRAFICA WHERE '-' & IDPAT & '-' Like '%-1-%'"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4576
- Joined: 26 Apr 2010, 17:36
Re: VERY STRONG query
Ops.,.HansV wrote: ↑08 Dec 2024, 19:18Code: Select all
strSQL = "SELECT * FROM ANAGRAFICA WHERE '-' & IDPAT & '-' Like '%-1-%'"
I Need also a where IDCLI from ANAGRAFICA.
Sorry.
For example: 59