SELECT query between a table and an existing query
SolvedBiankaBo Posted messages 71 Registration date Status Membre Last intervention -
Hello there!!
It's me again!
I need help with my SQL code..
I believe it's impossible to make a SELECT query with a join between a table and a query, right?
Let me put you in context; maybe I also don't have the right way to proceed..
I have two attached tables, each with a primary key that usually corresponds, except that their data types currently dissociate them.
- Table 1 (dbo_GPM_N_FACT) contains the [EMPR] field in text format of 7 characters
- Table 2 (dbo_GPM_E_ELE_DOSSIER) contains the [FICHE] field in numeric format, so the number of characters varies between 1 and 7.
Here is an example of possible correspondences:
[EMPR] => [FICHE]
0001234 => 1234
0078956 => 78956
2145687 => 2145687
You can understand that I'm unable to establish a relationship between the two since their data types are not the same and I cannot change the data types contained in an attached table (unless I'm mistaken?).
After some thought, I created query A (07-01_GPI_SOLDE_ANN_DEM_RECH) from table 1 and I added a calculated column EMPR2: [EMPR]+0, which transforms my text format data to numeric format and therefore removes the leading zeros from the data. Now [EMPR2] perfectly corresponds to [FICHE]. If I manually create a second query, i.e., query B, to join my query A to my table 2 by inserting a relationship between the corresponding columns, it gives me exactly what I need! Yeah!
However, it's never that simple.
Rather than manually creating my query B, I need to write it in SQL, and that's where it no longer works. I thought that using the SQL code from my existing query and adapting it to VBA code would work, but I feel like I can't make a SELECT query from an existing query, is that true?
Here is my non-functional SQL code based on my existing query A (07-01_GPI_SOLDE_ANN_DEM_RECH) and my table 2 (dbo_GPM_E_ELE_DOSSIER):
Dim base As DAO.Database Dim enr As DAO.Recordset Dim SqlD As String Set base = CurrentDb SqlD = "SELECT [dbo_GPM_E_ELE_DOSSIER].[FICHE], [07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR], [dbo_GPM_E_ELE_DOSSIER].[NOM], [dbo_GPM_E_ELE_DOSSIER].[PNOM], [07-01_GPI_SOLDE_ANN_DEM_RECH].[ANNEE], [07-01_GPI_SOLDE_ANN_DEM_RECH].[SommeDeSOLDE]" & _ " FROM [dbo_GPM_E_ELE_DOSSIER] INNER JOIN [07-01_GPI_SOLDE_ANN_DEM_RECH] ON [dbo_GPM_E_ELE_DOSSIER].[FICHE] = [07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR2]" & _ " WHERE ((([07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR]) " & strRechRef & ") AND (([dbo_GPM_E_ELE_DOSSIER].[NOM]) " & strRechNom & ") AND (([dbo_GPM_E_ELE_DOSSIER].[PNOM]) " & strRechPNom & "));" Set enr = base.OpenRecordset(SqlD)
Runtime error 3061 - Too few parameters 2 expected on line: Set enr = base.OpenRecordset(SqlD)
I tried to simplify my code to find the source of the problem by removing my WHERE condition, but the result is the same. This leads me to believe that the existing query is the issue.
Here is also the code for my existing query A in case you need it:
SELECT dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, Sum(dbo_GPM_N_FACT.SOLDE) AS SommeDeSOLDE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 AS EMPR2 FROM dbo_GPM_N_FACT GROUP BY dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 HAVING (((dbo_GPM_N_FACT.ECO)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte33]) AND ((dbo_GPM_N_FACT.ANNEE)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte14]) AND ((Sum(dbo_GPM_N_FACT.SOLDE))>0) AND ((dbo_GPM_N_FACT.STATUT_FACT)<>"X"));
Am I on the right track or is there a miraculous solution to all of this?
Thank you very much once again!
3 réponses
Hello,
At first glance, I would say that you are falling back on a limitation that you recently encountered:
You cannot use a query in VBA that uses data from a form.
I haven't (yet) tried to understand your overall logic. So I don't know if you're on the right track.
Assuming you're on the right track, you can continue by replacing, in your query B,
INNER JOIN [07-01_GPI_SOLDE_ANN_DEM_RECH]
with
INNER JOIN ( SELECT dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, Sum(dbo_GPM_N_FACT.SOLDE) AS SommeDeSOLDE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 AS EMPR2 FROM dbo_GPM_N_FACT GROUP BY dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 HAVING (((dbo_GPM_N_FACT.ECO)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte33]) AND ((dbo_GPM_N_FACT.ANNEE)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte14]) AND ((Sum(dbo_GPM_N_FACT.SOLDE))>0) AND ((dbo_GPM_N_FACT.STATUT_FACT)<>"X")) ) AS [07-01_GPI_SOLDE_ANN_DEM_RECH]
Which gives, I think,
SqlD = "SELECT [dbo_GPM_E_ELE_DOSSIER].[FICHE], [07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR], [dbo_GPM_E_ELE_DOSSIER].[NOM], [dbo_GPM_E_ELE_DOSSIER].[PNOM], [07-01_GPI_SOLDE_ANN_DEM_RECH].[ANNEE], [07-01_GPI_SOLDE_ANN_DEM_RECH].[SommeDeSOLDE]" & _ " FROM [dbo_GPM_E_ELE_DOSSIER] INNER JOIN " & _ " ( SELECT dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, Sum(dbo_GPM_N_FACT.SOLDE) AS SommeDeSOLDE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 AS EMPR2 " & _ " FROM dbo_GPM_N_FACT " & _ " GROUP BY dbo_GPM_N_FACT.ECO, dbo_GPM_N_FACT.EMPR, dbo_GPM_N_FACT.ANNEE, dbo_GPM_N_FACT.STATUT_FACT, [EMPR]+0 " & _ " HAVING (((dbo_GPM_N_FACT.ECO)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte33]) " & _ " AND ((dbo_GPM_N_FACT.ANNEE)=[Forms]![03-B1_Ajout_Creance_GPI].[Texte14]) " & _ " AND ((Sum(dbo_GPM_N_FACT.SOLDE))>0) AND ((dbo_GPM_N_FACT.STATUT_FACT)<>"X")) ) " & _ " as [07-01_GPI_SOLDE_ANN_DEM_RECH] ON [dbo_GPM_E_ELE_DOSSIER].[FICHE] = [07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR2]" & _ " WHERE ((([07-01_GPI_SOLDE_ANN_DEM_RECH].[EMPR]) " & strRechRef & ") AND (([dbo_GPM_E_ELE_DOSSIER].[NOM]) " & strRechNom & ") AND (([dbo_GPM_E_ELE_DOSSIER].[PNOM]) " & strRechPNom & "));"
Then, you need to use the technique you know well to "extract" the two uses of the form from the SQL query, and evaluate them in VBA.
.
I'm trying to understand what you want to achieve, in order to see if it is possible to do all of this more simply.
I think you can easily join the two tables without touching the content of the tables:
SELECT bnkb_empr.EMPR, bnkb_fiche.FICHE FROM bnkb_empr , bnkb_fiche where [EMPR]+0=FICHE;
However, I believe this won't really help you, as your problem is the use of the fields from the form.
Maybe this will help you simplify your queries.
Yessssssss!!!! This is exactly what I needed!
It works perfectly!
You are excellent!! You perfectly understood my request!
I knew we could make this simpler and I also knew that I was committing to a well-known path that I find difficult to understand, so I am extremely happy with your response.
I therefore tested your proposed WHERE condition on a small scale and once I confirmed that everything was working as it should, I added the other columns with their conditions. It turned into HAVING, but it's exactly the formula that meets my needs! Here is my final SQL code:
SqlD = "SELECT [dbo_GPM_N_FACT].[EMPR], [dbo_GPM_E_ELE_DOSSIER].[FICHE], [dbo_GPM_E_ELE_DOSSIER].[NOM], [dbo_GPM_E_ELE_DOSSIER].[PNOM], " & _ "[dbo_GPM_N_FACT].[ECO], [dbo_GPM_N_FACT].[ANNEE], Sum([dbo_GPM_N_FACT].[SOLDE]) AS [SommeDeSOLDE], [dbo_GPM_N_FACT].[STATUT_FACT]" & _ " FROM [dbo_GPM_N_FACT], [dbo_GPM_E_ELE_DOSSIER]" & _ " GROUP BY [dbo_GPM_N_FACT].[EMPR], [dbo_GPM_E_ELE_DOSSIER].[FICHE], [EMPR]+0, [dbo_GPM_E_ELE_DOSSIER].[NOM], " & _ "[dbo_GPM_E_ELE_DOSSIER].[PNOM], [dbo_GPM_N_FACT].[ECO], [dbo_GPM_N_FACT].[ANNEE], [dbo_GPM_N_FACT].[STATUT_FACT]" & _ " HAVING ((([dbo_GPM_N_FACT].[EMPR]) " & strRechRef & ") AND (([EMPR]+0)=[FICHE]) AND (([dbo_GPM_E_ELE_DOSSIER].[NOM]) " & strRechNom & ") " & _ "AND (([dbo_GPM_E_ELE_DOSSIER].[PNOM]) " & strRechPNom & " ) AND (([dbo_GPM_N_FACT].[ECO])='" + CStr([Forms]![03-B1_Ajout_Creance_GPI].[Texte33]) + "') AND (([dbo_GPM_N_FACT].[ANNEE])=" + CStr([Forms]![03-B1_Ajout_Creance_GPI].[Texte14]) + ") AND " & _ "((Sum([dbo_GPM_N_FACT].[SOLDE]))>0) AND (([dbo_GPM_N_FACT].[STATUT_FACT])<>'X'));"
Thank you so much for your speed and your talent!
Have a good day!