Avoid multiple queries 2

[TrekBiker]

I have ended up with 12 queries derived from the same one, just changing the criterion value on one field each time. The queries are launched from a set of individual buttons on the form. How can I simplify this? The objective is to keep the number of queries down because there are already too many of them. I don't want to use a query that looks up the criteria in a separate form. The only places the names appear are in labels on the form beside related data for each criterion.

 

[Andrzejek]

You need to provide more information, like ... your query.
And if you mark what keeps changing and based on what, that would be nice.

Just a guess here....

Dim strSQL As String

strSQL = "Select ... From ... Where 1+1=2 "

Select Case Something
    Case X
        strSQL = strSQL & " And FieldA = 1234"
    Case Y
        strSQL = strSQL & " And FieldA = 9876"
    Case Z
        strSQL = strSQL & " And FieldA = 88.66"
End Select

strSQL = strSQL & " Order By 1"

DB.Execute strSQL

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

You might be able to use a single query and just change the SQL as noted at faq701-7433.

Are the "12 queries derived from the same one" simple select queries? Are they used are record sources of reports or forms?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thank you both for replies.

Just to clarify,the original query is simple, yes, called qrySales with a field ContainerType whose criterion will be changed for each derivative.

I want to avoid creating more fixed queries like qrySales-Bottle, qrySales-Cask, qrySales-Barrel, etc for ContainerType = Bottle, then Cask, then Barrel, etc.

Well done on being top of the chart, Duane! Will look at the FAQ.

 

[dhookom]

TrekBiker,

You still didn't suggest how the queries are being used.

BTW:

DB.Execute strSQL

is used for action queries so I don't believe it will help in your situation unless you are updating, deleting, etc.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Duane,
My code sample was for displaying the idea only




---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
I do appreciate your "1+1=2 ". I use something similar to build SQL statements without affecting performance.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Sorry, I'd had extra words in my original version that I then edited. I've been using just Docmd.OpenQuery to open the separate queries qrySales-Bottle, etc.

 

[dhookom]

If you are simply opening the queries, I would change the SQL property of a single query and then open it. This requires that each user have their own front-end which is pretty much the standard way applications are built for multiple users.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

I seem to be sowing confusion here. This is already one frontend with everyone having a copy. I just want users to be able to click one of the Container options and view the query output, so getting VBA to inject the criteria value for the button being pressed. Clicking the Bottled button puts "Bottled" into qrySales then opens the query output.

 

[Andrzejek]

So your qrySales is something like:
[tt] Select FieldX, FieldY, ContainerType, FieldZ From SomeTable[/tt]

And (I guess) you have a table:[pre]
ID ContainerType
1 Bottle
2 Cask[blue]
3 Barrel[/blue]
[/pre]
And you run your 12 queries based on 12 ContainerTypes, so for Barrel it would be
[tt]
Select * From qrySales
Where ContainerType = [blue]3[/blue][/tt]

Is that right?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

You can do exactly what you require by changing the SQL of a saved query. The link I provided has some sample code. If you are confused by the code or solution, please provide your new query name and information about how you would modify the existing query.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Soory, I'll need to return to this as it's 9pm in the UK and my wife needs some company!

 

[TrekBiker]

Thanks again both of you, and sorry my lack of expertise is taking so much of your time.

Andy - yes that's the sort of structure. Try as I will I can't get the syntax right for the Select statement with ContainerType = Bottle,say. Even getting it right I'm not sure how then to display the results in the way a fixed query would.

Duane - qrySales has many components as it subtracts data from queries for Credits and Sales, and has a join to a table having ContainerType as one of its text fields (no separate ID being used). I need qrySales to remain unchanged as it's used elsewhere but want to be able to display its content limited to a particular ContainerType when chosen. So for example replicating a fixed query qrySales-Bottle exactly like qrySales but with ContainerType = Bottle.

I did try your sample code, building a table and query exactly as you suggested, then running it from a form with date selector. But it was leaving qrySales with the last used ContainerType.

 

[dhookom]

TrekBiker,

You don't need to change the qrySales. Just create a new query based on qrySales:

qryFilteredSales

SELECT * FROM qrySales

Then use the function I recommended in your code:

Dim strSQL as String
Dim strOldSQL as String
Dim strQueryName as String
strQueryName = "qryFilteredSales"
strSQL = "SELECT * FROM qrySales " & _
    "WHERE ContainerType = '" & Me.ContainerTypeControlOnForm & "'"
strOldSQL = fChangeSQL(strQueryName,strSQL)

If you can't figure this out, please provide the actual field name you want to filter from qrySales as well as the control on your form that will have the value you want to specify as the filter.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Duane and I try to help you with some ideas and samples of code.
You came here to get some help with your issue. But we have not seen a single line of code from you. All what we can do is guess.

Please, help us to help you.
Show here some code (formatted as code) that you have. You don't need to include all 12 versions of your query, 2 queries would be sufficient.


---- Andy

There is a great need for a sarcasm font.

 

[TrekBiker]

Sorry. This is the SQL for the fixed query from which I derive the extra 12.

SELECT qrySalesDetailsByCaskGroup.Customer, Customers.CompanyName, qrySalesDetailsByCaskGroup.CaskGroup, qrySalesDetailsByCaskGroup.CaskType, qrySalesDetailsByCaskGroup.BeerName, [SaleQuantity]-Nz([CreditQuantity]) AS Quantity, [TotalSales]-Nz([TotalCredit]) AS Sales, [Quantity]*[perFirkin] AS FirkinsEquivalent, [Sales]/[Quantity] AS AveragePrice
FROM ((qrySalesDetailsByCaskGroup LEFT JOIN qrySalesCreditsDetailsByCaskGroup ON (qrySalesDetailsByCaskGroup.Customer = qrySalesCreditsDetailsByCaskGroup.Customer) AND (qrySalesDetailsByCaskGroup.CustomerID = qrySalesCreditsDetailsByCaskGroup.CustomerID) AND (qrySalesDetailsByCaskGroup.BeerName = qrySalesCreditsDetailsByCaskGroup.BeerName) AND (qrySalesDetailsByCaskGroup.CaskType = qrySalesCreditsDetailsByCaskGroup.CaskType) AND (qrySalesDetailsByCaskGroup.CaskGroup = qrySalesCreditsDetailsByCaskGroup.CaskGroup)) INNER JOIN [Cask Types] ON qrySalesDetailsByCaskGroup.CaskType = [Cask Types].CaskType) INNER JOIN Customers ON qrySalesDetailsByCaskGroup.CustomerID = Customers.CustomerID
ORDER BY qrySalesDetailsByCaskGroup.BeerName;

This would be the fixed query version for 'Bottled', which I was hoping could be derived in VBA by supplying 'Bottled' as a Where condition.

SELECT qrySalesDetailsNet.Customer, qrySalesDetailsNet.CompanyName, qrySalesDetailsNet.CaskGroup, qrySalesDetailsNet.CaskType, qrySalesDetailsNet.BeerName, qrySalesDetailsNet.Quantity, qrySalesDetailsNet.Sales, qrySalesDetailsNet.FirkinsEquivalent, qrySalesDetailsNet.AveragePrice
FROM qrySalesDetailsNet
WHERE (((qrySalesDetailsNet.CaskGroup)="Bottled Beer"))
ORDER BY qrySalesDetailsNet.Customer, qrySalesDetailsNet.BeerName;

I'd abbreviated the names in my original post to make the situation simpler to explain.

 

[dhookom]

Did you create your new query qryFilteredSales?
Did you add the function I suggested to a new module?
Did you compile your code?
Where are you getting the value of "Bottled Beer" from other than hard-coded? Is there a combo box the user can select the CaskGroup?

So you code in your form might look something like:

Dim strSQL as String
Dim strOldSQL as String
Dim strQueryName as String
strQueryName = "qryFilteredSales"
strSQL = "SELECT * FROM qrySales " & _
    "WHERE CaskGroup= '" & Me.ContainerTypeControlOnForm & "' " & _ 
    "ORDER BY Customer, BeerName"
strOldSQL = fChangeSQL(strQueryName,strSQL)
DoCmd.OpenQuery strQueryName

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

This is just my opinion, but your "fixed query" may benefit from aliases for tables' names:
[tt]
SELECT SD.Customer, C.CompanyName, SD.CaskGroup, SD.CaskType, SD.BeerName,
[blue]SaleQuantity[/blue]-Nz([blue]CreditQuantity[/blue][red], 0[/red]) AS Quantity, [blue]
TotalSales[/blue]-Nz([blue]TotalCredit[/blue][red], 0[/red]) AS Sales, [blue]
Quantity[/blue]*[blue]perFirkin[/blue] AS FirkinsEquivalent, [blue]
Sales/Quantity[/blue] AS AveragePrice
FROM ((qrySalesDetailsByCaskGroup SD
LEFT JOIN qrySalesCreditsDetailsByCaskGroup SC
ON (SD.Customer = SC.Customer)
AND (SD.CustomerID = SC.CustomerID)
AND (SD.BeerName = SC.BeerName)
AND (SD.CaskType = SC.CaskType)
AND (SD.CaskGroup = SC.CaskGroup))
INNER JOIN [Cask Types] ON SD.CaskType = [Cask Types].CaskType)
INNER JOIN Customers C ON SD.CustomerID = C.CustomerID
ORDER BY SD.BeerName;
[/tt]
[blue]Blue[/blue] fields are not fully qualified (which tables are they coming from?)
It is a good idea to specify the value in Nz function, otherwise you get an empty string, which is not very 'proper' in subtracting values.


---- Andy

There is a great need for a sarcasm font.

 

[TrekBiker]

Duane, brilliant thanks, I've now got it working as I want.
- Created qryFilteredSales as a copy of the original fixed query qrySalesDetailsNet
- Have your function in a new module
- Did compile, which I do every time I add significant blocks of code
- I'm picking up the CaskGroup for each type from a set of label captions for fields with related total calculations

Andy, thanks too for your contributions and last suggestions, which make a lot of sense.

I'm sorry I didn't make my original questions clear enough, lesson learned, very many thanks.