-
I am trying to create a report that grabs the top 20 records from a table. The table contains data on 6 different machines,
so I am assuming that I need to loop six times and pass a variable to the query which indicates the machine id. I have tried creating an integer array that contains machine_id as an integer, and this is passed to the query. The machine_id array stores the following ids : 1600, 1800, 3600, 4800, 6000, and 6001.My problem is when looping through the query, the SELECT statement has to be invoked more than once... How can I append the records to the query and pass the array (machine_id) to the query? The machine id determines what data to run the query on ( the query uses the TOP keyword).
GFK
-
you have to increment the array element in the bottom of your loop, so each time
you'll do the select statement, your variable will be different.
here a select statement with a variable
Data1.RecordSource = "select * from maintenance1 where eprtno = '" & strmsg & "'"
try working around this..
good luck
-
Thanks for the reply. I am still having problems though. It seems that I have to create a new query every iteration of the loop. I want to be able to run the code and have it loop through all machine id's returning the top 20 records based on different criteria, and return this recordset to a report. Would someone please supply some sample code if possible? TIA
-
If you know in advance all ID than why cannot you just pass 20 parameters in the same Query?
Seems to be simple
-
The query uses the TOP keyword and the select statement is:
SELECT DISTINCTROW TOP 20 tblTonnageData.Date, tblMachineData.MachineID, tblTonnageData.TonsProduced, tblTonnageData.WorkedHours, tblTonnageData.ManHours, qryFullOperatorName.OperatorName
In short, 20 parameters will not work. All 6 machines are stored in tblTonnageData, which contains more than 18,000 records. The TOP keyword will return the top number of records, but if I order the query by machineID, it will return the top 20 records for 1 machineID only (more than 20 records returned when ordered by ID). I need the data for all 6 different machines, thus I am assuming that I have to loop through the query with machineID set equal to each individual machine when using the TOP instruction. This is where using a variable or array comes in, and the loop of course.
This query is supposed to give a supervisor the top 20 records run over a time period. For example the supervisor may want to know what the top 20 tons produced were for each machine. The supervisor may want to know the top 20 coils produced on each machine, thus another query, with different criteria, yet the same syntax. Currently, I run the query and have the supervisor enter the machineID through an input box. This, however, is a little cumbersome when running data for six machines on three or more separate queries!
