Modify a query recordset to complete information (MS Access)

im working in a project in Microsoft access to query the date, hour and a quantity of products by line of production. However, I need to display all of the 24 hours of production in a column but cannot do it since I don’t have records for the missing hours. I’m using Totals to sum the quantity of products produced.

Is there a way to add the missing hours to my query if I lack of records? Or maybe doing it another way?

I have tried making a table with the hours (Table: Horas) of the day and making a relationship to display all hours and left in blank the other missing fields. It didn’t work

This is my query:

SELECT Consulta.[Date Epei], Horas.Hora, Consulta.Hr, Sum(Consulta.Racks) AS Actual, 
IIf(Weekday([Date Epei])=6 And [Hr] Between 5 And 18,0,
IIf(Weekday([Date Epei])=7 And [Hr] Between 4 And 20,0,
IIf(Weekday([Date Epei])=1,0,-Int(-[Ln100]/24)))) AS Plan,
([Actual]/[Plan])*100 AS [Cumplimiento(%)]
FROM Horas 
LEFT JOIN ([Plan Lineas] 
RIGHT JOIN Consulta ON [Plan Lineas].Dia = Consulta.[Date Epei]) ON Horas.Hora = Consulta.Hr
WHERE (((Consulta.Line)="Linea 100"))
GROUP BY Consulta.[Date Epei], Horas.Hora, Consulta.Hr, 
IIf(Weekday([Date Epei])=6 And [Hr] Between 5 And 18,0,
IIf(Weekday([Date Epei])=7 And [Hr] Between 4 And 20,0,
IIf(Weekday([Date Epei])=1,0,-Int(-[Ln100]/24))));

The If statements will change the [Plan Lineas] field to a value of 0 if it’s Friday between 5 and 6pm, Saturday between 4 and 8pm or Sunday. (I know it’s messy, and will try to reduce it in the future).

“Consulta” is a query where I made some calculated fields from various tables.

This is my first time using Access, and started looking if there’s a way using VBA,. So any help or guidance I will appreciate it.

Answer

You can join to a derived table of totals for 24 hours.

Beware DST shifts in the times of shift changes!

Advertisement

Answer