Another query query. (1 Viewer)

[John Sh]

I think I know the answer to this one but I'll ask it anyway.
Is there a way to use a combobox.rowsource as the input to a query, rather than build a new query every time.
To explain:
My form is used with 6 different tables. That means 6 queries for a combobox and another 6 queries to reduce the results to one instance of each.
For instance query1 returns 1,1,1,1,2,2,2. using query1 as input for query2 my combobox only shows 1,2. Both query1 and query2 are aggregate queries.
Can this be achieved with the combobox.rowsource, ?

 

[jdraw]

Can you give us more info? What exactly is the query(ies) involved and tell us about the comboboxes. Use a mock up if necessary to link the pieces together to clarify your need.

 

[John Sh]

Can you give us more info? What exactly is the query(ies) involved and tell us about the comboboxes. Use a mock up if necessary to link the pieces together to clarify your need.

Without spending hours picking out bits of tables etc here is the code for two of the queries, the others are the same except for the table.
The first aggregate query selects the family names from the table but there are many repeats since each family is represented by many genus's.
there could be up to 3000 families with hundreds of genus for each family. Obviously the result of such a query is somewhat unwieldy.
The second aggregate query reduces the list to one instance of each family name.

The "onload" event of the form determines the form recordsource and then the appropriate queries.
So each table requires four initial queries and then four more secondary queries to achieve the desired result.

If I can somehow use the "combobox.rowsource" as the input for the secondary query, I would only need one secondary query to satisfy any table used as the recordsource.

I have included a screenshot of the form.

The primary query.

SELECT 
Main.Family
FROM Main
GROUP BY Main.Family, Main.BoxNo
HAVING (((Main.BoxNo)>0))
ORDER BY Main.Family, Main.BoxNo;

The secondary query.

SELECT QNBFamilyH.Family
FROM QNBFamilyH
GROUP BY QNBFamilyH.Family
ORDER BY QNBFamilyH.Family;

And the form_load event.

Private Sub Form_load()
    Dim sCaption As String
    sTable = getArgs(Nz(Me.OpenArgs), 1)
    sArgs = getArgs(Nz(Me.OpenArgs), 2)
    sCaption = getCaption(sTable)
    Me.RecordSource = sTable
    Me.Visible = True
    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM counted"
    DoCmd.SetWarnings True
     If sTable = "Main" Then
         Me.cboFamily.RowSource = "QNBFamilySH"
         Me.cboInfra.RowSource = "QNBInfraH"
         Me.cboCollect.RowSource = "QNBCollectSH"
         Me.cboBox.RowSource = "QNBBoxH"
     ElseIf sTable = "Dtv_records" Then
         Me.cboFamily.RowSource = "QNBFamilyDTV"
         Me.cboInfra.RowSource = "QNBInfraDTV"
         Me.cboCollect.RowSource = "QNBCollectDTV"
         Me.cboBox.RowSource = "QNBBoxDTV"
     ElseIf sTable = "National Parks Collection" Then
         Me.cboFamily.RowSource = "QNBFamilySN"
         Me.cboInfra.RowSource = "QNBInfraNP"
         Me.cboCollect.RowSource = "QNBCollectSN"
         Me.cboBox.RowSource = "QNBBoxNP"
     ElseIf sTable = "SBCollection" Then
         Me.cboFamily.RowSource = "QNBFamilySS"
         Me.cboInfra.RowSource = "QNBInfraSB"
         Me.cboCollect.RowSource = "QNBCollectSS"
         Me.cboBox.RowSource = "QNBBoxSB"
    ElseIf sTable = "Ourimbah Collection" Then
         Me.cboFamily.RowSource = "QNBFamilySO"
         Me.cboInfra.RowSource = "QNBInfraOur"
         Me.cboCollect.RowSource = "QNBCollectSO"
         Me.cboBox.RowSource = "QNBBoxOur"
    End If

And the form image

 

[June7]

I wonder why 6 tables can use one form. If these tables are identical in structure, why not have 1 table?

Since you are using query objects, and table and field names cannot be dynamic in query object, would need to use VBA to modify query object - if I understand what you are describing.

 

[John Sh]

I wonder why 6 tables can use one form. If these tables are identical in structure, why not have 1 table?

Since you are using query objects, and table and field names cannot be dynamic in query object, would need to use VBA to modify query object - if I understand what you are describing.

Yes, the tables are close to identical but represent collections from different identities and are, therefore, kept separate.
I haven't looked into it yet but I might be able to get around the problem by using a QDF with parameters.
The main problem is trying to keep track of the growing stack of queries and being more creative with naming conventions.
There is also the problem of maintenance, which gets worse with each new cluster of queries.

 

[June7]

"collections from different entities" - 1 table and have another field to identify these entities.

"new cluster of queries" - this implies poor db design, a stable db should rarely need design changes

 

[John Sh]

Actually the solution was quite simple.
The output of the primary query is appended to a temporary table.
The family combo's source is then the secondary query that has the temptable as it's source.
I still need a separate query for each of form's recordsources but only one query to satisfy all of the source tables.

so query1 becomes:

INSERT INTO tempfamily ( Family )
SELECT Main.Family
FROM Main
GROUP BY Main.Family, Main.BoxNo
HAVING (((Main.BoxNo)>0))
ORDER BY Main.Family, Main.BoxNo;

and query2 becomes;

SELECT TempFamily.Family
FROM TempFamily
GROUP BY TempFamily.Family
ORDER BY TempFamily.Family;

I don't know why I didn't think of this as I have used the same approach on other forms that use multiple tables.
I still require an append query for each table but only one select query to cover the lot.

I thank you for your input.
John

 

[Galaxiom]

Yes, the tables are close to identical but represent collections from different identities and are, therefore, kept separate.

You should take notice of what June said.

It is a design mistake to have six tables, and you will be using clumsy workarounds forever if you continue down your current path.

 

[John Sh]

You should take notice of what June said.

It is a design mistake to have six tables, and you will be using clumsy workarounds forever if you continue down your current path.

It would be really nice if you people would stop telling me what I am doing is "wrong".
"Different" it may be, but "wrong" it is not simply because, for my situation, it is the only way I can do it and "IT WORKS"
As for the solution being clumsy, I find it much less clumsy to have one temporary table than having six extra queries coming out of my ears.

I do, very much, appreciate the help I get from this forum but I get heartily sick of people, who don't appreciate my circumstances, telling me I am doing the wrong thing.

 

[plog]

it would be really nice if you people would stop telling me what I am doing is "wrong".

Ok, don't listen to us. Listen to yourself. Who does the above statement paint in a poor picture?

The road you want to take is unpaved, has no gas for a thousand miles, is covered in nails and eventually comes to a dead end. And your mad because the townsfolks you asked directions from refuse to tell you where that road starts. Instead they want to know where it is your actually going so they can give you the best route.

 

[Galaxiom]

It would be really nice if you people would stop telling me what I am doing is "wrong".

You clearly have not learnt about databases and refuse to do so. I would recommend you go back to working with spreadsheets. They are more suited to you way of thinking.

 

[June7]

Ah, now I remember you.

I can appreciate db structure that defies conventions (guilty) but don't think I have committed to this extreme.

I will endeavor to avoid advising on your design in the future.

 

[isladogs]

It would be really nice if you people would stop telling me what I am doing is "wrong".
"Different" it may be, but "wrong" it is not simply because, for my situation, it is the only way I can do it and "IT WORKS"
As for the solution being clumsy, I find it much less clumsy to have one temporary table than having six extra queries coming out of my ears.

I do, very much, appreciate the help I get from this forum but I get heartily sick of people, who don't appreciate my circumstances, telling me I am doing the wrong thing.

I suspect you have managed to alienate many of the people who have tried to help you both in this thread and in the past.
Your method may well work for you
However, it isn't the only way it can be done and it definitely isn't the best method of doing so.

I'll also keep clear in future threads

 

[mike60smart]

It would be really nice if you people would stop telling me what I am doing is "wrong".
"Different" it may be, but "wrong" it is not simply because, for my situation, it is the only way I can do it and "IT WORKS"
As for the solution being clumsy, I find it much less clumsy to have one temporary table than having six extra queries coming out of my ears.

I do, very much, appreciate the help I get from this forum but I get heartily sick of people, who don't appreciate my circumstances, telling me I am doing the wrong thing.

Would it be possible for you to upload a screenshot of your Relationship Window?