# SolvedNeed some advice on a Total query (1 Viewer)

#### KitaYama

##### Well-known member
This is a part of a simplified relationship in a database.

I need a query that shows Machine name, ProductNo, ProductName, ProcessNo, Count of manufactured parts and the total time used for manufacturing, during a specified date interval.
The only way I could reach this result, was to write 2 queries:
qryResults:
SQL:
``````SELECT
C.MachineName,
P.ProductNo,
P.ProductName,
M.ProcessNo,
M.StartedFrom,
M.EndedOn,
M.CycleTime,
M.Detachment,
[CounterTo]-[CounterFrom] AS Quantity,
([Quantity]*([CycleTime]+[Detachment])) AS TotalProcessTime
FROM
tblProducts AS P INNER JOIN (tblMachines AS C INNER JOIN tblManu AS M ON C.MachinePK = M.MachineFK) ON P.ProductPK = M.ProductFK
ORDER BY
M.StartedFrom;``````

Then built another query based on the first one.
qryResultsFinal:
SQL:
``````SELECT
q.MachineName,
q.ProductNo,
q.ProductName,
q.ProcessNo,
Sum(q.Quantity) AS QuantityOfSum,
Round(Sum([TotalProcessTime]*24),2) AS TotalTime
FROM
qryResults AS q
WHERE
q.StartedFrom >#2024/5/2# AND q.EndedOn <#2024/5/7#
GROUP BY
q.MachineName, q.ProductNo, q.ProductName, q.ProcessNo
ORDER BY
Round(Sum([TotalProcessTime]*24),2) DESC;``````

The final query shows the expected results. But since I'm not that good in sql, I need to ask for some advice.
1. Is there any better (simpler) way to reach this final result?
2. As you see the date duration is hard coded into second query. I need to open the query in vba and work on the result with different date.
OpenRecordset("SELECT * FROM qryResults_FINAL WHERE StartedFrom >#2024/5/3# AND EndedOn <#2024/5/5#")
throws "Too few parameters" error.
If I remove the WHERE clause from the second query, still I'm facing the same error.
How can I open a recordset of the second query at different dates?

A sample database with a few record to test is attached.
Thanks you.

PS : At present, I create the sql of the second query dynamically in VBA and am not using the saved one.

#### Attachments

Last edited:
q.StartedFrom >Forms!yourform!StartedFrom AND q.EndedOn < Forms!yourformEndedOn

If the form fields are unbound, make sure you set the format property for each to short date so Access knows they are date datatypes.

q.StartedFrom >Forms!yourform!StartedFrom AND q.EndedOn < Forms!yourformEndedOn

If the form fields are unbound, make sure you set the format property for each to short date so Access knows they are date datatypes.
I have 2 major problems with your solution.
1. The query will be called from different forms. So I can not bind it to a specific form.
2. Even if I go this root, the query opens without error. But when I want to access its recordset, I receive the same error.
The following line gives me "Too few parameters" Error even when the form is opened.
Set rs = CurrentDb.OpenRecordset("qryResults_Final")
You may want to check the attached database to see what I mean.

If I misunderstood your solution, a little more detailed one is much appreciated.
Thanks.

#### Attachments

Last edited:
You should be able to just merge your queries. Take the SELECT / WHERE / GROUP BY /ORDER BY of the second, the FROM of the first, make the SELECT/WHERE/GROUP BY table references point correctly, then use algebraic replacement to make the calculated fields use their underlying calculations.

Code:
``````SELECT
C.MachineName,
P.ProductNo,
P.ProductName,
M.ProcessNo,
Sum([CounterTo]-[CounterFrom]) AS QuantityOfSum,
Round(Sum(([Quantity]*([CycleTime]+[Detachment]))*24),2) AS TotalTime
FROM
tblProducts AS P INNER JOIN (tblMachines AS C INNER JOIN tblManu AS M ON C.MachinePK = M.MachineFK) ON P.ProductPK = M.ProductFK
WHERE
M.StartedFrom >#2024/5/2# AND M.EndedOn <#2024/5/7#
GROUP BY
C.MachineName, P.ProductNo, P.ProductName, M.ProcessNo
ORDER BY
Round(Sum( ([Quantity]*([CycleTime]+[Detachment]))*24),2) DESC;``````

You should be able to just merge your queries. Take the SELECT / WHERE / GROUP BY /ORDER BY of the second, the FROM of the first, make the SELECT/WHERE/GROUP BY table references point correctly, then use algebraic replacement to make the calculated fields use their underlying calculations.

Code:
``````SELECT
C.MachineName,
P.ProductNo,
P.ProductName,
M.ProcessNo,
Sum([CounterTo]-[CounterFrom]) AS QuantityOfSum,
Round(Sum(([Quantity]*([CycleTime]+[Detachment]))*24),2) AS TotalTime
FROM
tblProducts AS P INNER JOIN (tblMachines AS C INNER JOIN tblManu AS M ON C.MachinePK = M.MachineFK) ON P.ProductPK = M.ProductFK
WHERE
M.StartedFrom >#2024/5/2# AND M.EndedOn <#2024/5/7#
GROUP BY
C.MachineName, P.ProductNo, P.ProductName, M.ProcessNo
ORDER BY
Round(Sum( ([Quantity]*([CycleTime]+[Detachment]))*24),2) DESC;``````
I'm receiving a Quantity Enter Parameter Value when I try to open your query.
I tried to replace [Quantity] with [QuantityOfSum], I received a "Subqueries can not be used in the expression" Error.
I tried to replace [Quantity] with Sum([CounterTo]-[CounterFrom]), I receive a syntax error.

This should be TotalTime:

Code:
``   Round(Sum((( [CounterTo]-[CounterFrom])*([CycleTime]+[Detachment]))*24),2) AS TotalTime``

This should be TotalTime:

Code:
``   Round(Sum((( [CounterTo]-[CounterFrom])*([CycleTime]+[Detachment]))*24),2) AS TotalTime``
@plog Million thanks for the correction.
Works perfect. It seems that I have the answer to my first question.

Now I'll be waiting to see if anyone can advice on my second question : How to open this query in vba for a different duration of dates.
Thanks again

How do you want the dates to be given to the query? Calculated? Input from form? Other?

From two unbound combo boxes in 3 different forms.
The same function will be executed from different forms.

Thanks.

Passing parameters to a query - variants:

Passing parameters to a query - variants:
Much appreciated.
I think I'm OK with my questions.

Thanks.

If you have to call the query from multiple forms, you can use TempVars.

If you want to open a recordset with it, this is the code I use:
Code:
``````Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset

Set db = CurrentDB()
Set qd = db.Querydefs!myqueryname
qd.Parameter!CustID = Me.CustID
Set rs = qd.OpenRecordset``````

Replies
5
Views
784
Replies
8
Views
620
Replies
12
Views
2,015
Replies
5
Views
1,344
Replies
19
Views
2,366