querydef with parameters (1 Viewer)

[John Sh]

I have a series of almost identical queries that I would like to put into a qdf with parameters.
I have used these in the past but my knowledge of SQL is not up to the complexity of this .
A variant of the query shown is used as input for a bevy of count queries.
There is also a set of append queries with similar parameters.

The basic premise is that 8 tables are scanned for instances of a particular error. The results are applied to a menu.
The menu selection then determines which of the tables will be worked on. So with 8 tables and 3 queries for each table, things start to get out of hand, hence the call for qdf help

The basic query is not the problem, it is the criteria and the use of a second and third joined tables as part of the criteria
A sample query is listed below, can it be implemented as a qdf with parameters.
The "Main" table name is the only part of the query that needs to be parameterised.
There are 8 tables that use a variation of this query. Is it worth creating a qdf or do I stay with multiple queries?
The SQL below is created from the query design window.

SELECT Main.Family, Main.Genus, Taxon.genus, Bad_Family.Family
FROM (Main LEFT JOIN Taxon ON Main.Family = Taxon.family) LEFT JOIN Bad_Family ON Main.Family = Bad_Family.Family
GROUP BY Main.Family, Main.Genus, Taxon.genus, Bad_Family.Family
HAVING (((Taxon.genus) Is Null) AND ((Bad_Family.Family) Is Null))
ORDER BY Main.Genus;

 

[June7]

Table and field names cannot be dynamic in query object.

Could use VBA and DAO QueryDefs to modify query object.

These 8 tables have same structure?

 

[John Sh]

Could use VBA and DAO QueryDefs to modify query object.

These 8 tables have same structure?

I was asking about using querydefs with parameters for the table name.
And yes, the structures of the tables are identical.

What I'm looking for is something like the code below but with the linked tables and criteria.

Set qdf = oDB.CreateQueryDef("", "INSERT INTO (Tablename, AccessionNumber,Family,Genus) VALUES (sTablename,accNum,sFamily,sGenus) ;")
    Set rs = oDB.OpenRecordset("main", dbOpenDynaset)
    rs.MoveFirst
    DoCmd.SetWarnings False
        Do
            If (rs![BoxNo] = frm.cboBox) And ((rs![Infrafamily] = iVar) Or (rs![Family] = iVar)) Then
                bIsInfra = IIf(Nz(rs!Infrafamily, "") <> "", True, bIsInfra)
                qdf.Parameters("[AccNum]") = rs!AccessionNumber
                qdf.Parameters("[sFamily]") = rs!Family
                qdf.Parameters("[sGenus]") = rs!Genus
                qdf.Parameters("[sTablename]") = rs!tablename
                qdf.ReturnsRecords = False
                qdf.Execute

 

[MajP]

As already started, you cannot pass a table or field name as a parameter. They are only criteria.

 

[John Sh]

As already started, you cannot pass a table or field name as a parameter. They are only criteria.

Yes. I was misreading my own code. Looks like I'll just have to have a bucket full of queries!!

 

[June7]

Why are these 8 identical tables not 1 table with another field for category?

Can build SQL statement dynamically.

Set qdf = oDB.CreateQueryDef("", "INSERT INTO " & Me.tbxTable & "(AccessionNumber, Family, Genus) VALUES (" & rs!accNum & ",'" & rs!Family & "','" & "'" & rs!Genus & "')")

But then, why not just Execute the constructed SQL.

CurrentDb.Execute "INSERT INTO " & Me.tbxTable & "(AccessionNumber, Family, Genus) VALUES (" & accNum & ",'" & rs!Family & "','" & "'" & rs!Genus & "')"

 

[ebs17]

The "Main" table name is the only part of the query that needs to be parameterised.

If the fields and conditions are the same, but a table (Main??) is interchangeable, your database schema is not yet mature.
You often have problems with queries when the necessary structures are incorrect.

Show the database schema! Add descriptions.

 

[John Sh]

If the fields and conditions are the same, but a table (Main??) is interchangeable, your database schema is not yet mature.
You often have problems with queries when the necessary structures are incorrect.

Show the database schema! Add descriptions.

While the table structure is the same they are 8 totally separate sets of data from 8 different sources and must be kept separate, it is just the way it must be.
I will not enter into discussions about primary keys or normalisation because what I have is what I must have and it works for my purpose.
I am not having problems with queries, I was looking for a way to reduce the numbers but that is not to be in this instance.
To infer the structures are "incorrect" simply indicates you don't know what I have, and nor you should, because I have not mentioned them.

 

[CJ_London]

because what I have is what I must have and it works for my purpose.

Looks like I'll just have to have a bucket full of queries!!

Yup