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

KitaYama

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
This is a part of a simplified relationship in a database.

2024-05-09_08-51-37.png


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

  • Database1.accdb
    2.1 MB · Views: 28
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:22
Joined
Feb 19, 2002
Messages
43,854
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

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
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

  • Database1.accdb
    2.1 MB · Views: 34
Last edited:

plog

Banishment Pending
Local time
Yesterday, 20:22
Joined
May 11, 2011
Messages
11,705
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;
 

KitaYama

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
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.

Thanks for your time.
 

plog

Banishment Pending
Local time
Yesterday, 20:22
Joined
May 11, 2011
Messages
11,705
This should be TotalTime:

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

KitaYama

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
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
 

plog

Banishment Pending
Local time
Yesterday, 20:22
Joined
May 11, 2011
Messages
11,705
How do you want the dates to be given to the query? Calculated? Input from form? Other?
 

KitaYama

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
From two unbound combo boxes in 3 different forms.
The same function will be executed from different forms.

Thanks.
 

ebs17

Well-known member
Local time
Today, 03:22
Joined
Feb 7, 2020
Messages
2,041
Passing parameters to a query - variants:
 

KitaYama

Well-known member
Local time
Today, 10:22
Joined
Jan 6, 2022
Messages
1,634
Passing parameters to a query - variants:
Much appreciated.
I think I'm OK with my questions.

Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:22
Joined
Feb 19, 2002
Messages
43,854
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
    qd.Parameter!MinDate = DateAdd("yyyy", Date())
Set rs = qd.OpenRecordset
 

Users who are viewing this thread

Top Bottom