recordsets and data

[ctuna02]

How many recordsets can you have open at once?
I wish to make a report by opening a a recordset and use a
list to look to the last instance of an item in the recordset
then move it to a temporary table or recordset where I can run a report on it.
I think I can open a a snapshot recordset and find the data
using FindLast . I then want to move the entire record to the
new recordset or table . I am a little bit confused as to what
commands I would use to move the data or if I can have the recordset open during this process. Can I copy the Entire
Record or do I have to go field by field?
The current table is a History table . The items are duplicated but have diffrent status depending on the date of entry, the date is an included field . I want to select only the most recent ones for a uptodate status report.
Could this also be done by running the list and looking for the
highest value date with a query?

 

[ctuna02]

I think the top function in querys only returns the highest values?
The situation I am trying to run a report for is the following. I am tracking the status of hardware. Each piece of hardware has a unique name which is in one of the fields. The status of the hardware or history goes into a table . There will be multiple instances of the same harware with a different status for diffrent dates. Some hardware that is not used much may have the same status for months. What i need to do is make report that pulls out the last instance and status of the hardware. So that is why I thought I would need to open a recordset and look for the last value for the each unique hardware type and then output it to a Table containing the the last Entry or status update written to the History Table.
If I used the query method I think I would have to run a filter for each unique piece of hardware then use the top funtion to find the most uptodate record maybe I could then append it to a table.

By the way thank you very much for the reply.

 

[ctuna02]

I read up a little on the top property . If I use the first field which is the unique hardware # Ie widjet#1 and the date field will it return the last instance of this particular piece of equipment having a record updated?

 

[Jon K]

If you need to pull the most recent records for all the hardware numbers, you should use the Max() function instead of Top 1.

Try these two queries (using the correct table name and field names.)

qryMaxDate:-
SELECT [HardwareNumber], max([DateField]) AS MaxDate
FROM [TableName]
GROUP BY [HardwareNumber];

qryForReport:-
SELECT [TableName].[HardwareNumber], [DateField], [fld3], [fld4],...etc
FROM [TableName] INNER JOIN qryMaxDate ON ([TableName].[DateField]=[qryMaxDate].[MaxDate]) AND ([TableName].[HardwareNumber]=[qryMaxDate].[HardwareNumber]);


If the second query returns the correct records, base your report on it.


If you want to, you can change the first query to a parameter query for the user to input one hardware number when the report is run:-

qryMaxDate:-
SELECT [HardwareNumber], max([DateField]) AS MaxDate
FROM [TableName]
WHERE [HardwareNumber]=[Enter a hardware #]
GROUP BY [HardwareNumber];


Hope it helps.

 

[ctuna02]

Thanks Jon

I think this has me on the right track . I can select the last records with this method. I think these two querys will give me the results I need.