Solved Need some advice on a Total query (1 Viewer)

[KitaYama]

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:

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:

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.

 

[Pat Hartman]

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.

 

[KitaYama]

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.

 

[plog]

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.

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;

 

[KitaYama]

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.

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.

Thanks for your time.

 

[plog]

This should be TotalTime:

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

 

[KitaYama]

This should be TotalTime:

   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

 

[plog]

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

 

[KitaYama]

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

Thanks.

 

[ebs17]

Passing parameters to a query - variants:

Query to find records not between two dates

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it. I don't know what the actual field names are, but the logic is: < Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)

www.access-programmers.co.uk

 

[KitaYama]

Passing parameters to a query - variants:

Query to find records not between two dates

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it. I don't know what the actual field names are, but the logic is: < Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)

www.access-programmers.co.uk

Much appreciated.
I think I'm OK with my questions.

Thanks.

 

[Pat Hartman]

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:

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
    qd.Parameter!MinDate = DateAdd("yyyy", Date())
Set rs = qd.OpenRecordset