Need query to give results of the most current record

[parsok]

I'm new to access and I'm stuck. I have created a database of our laptops and when someone borrows a laptop. Created a query and a report with the necessary information for a person to sign accepting responsibility when using a laptop. I need to be able to print out the loan agreement for the laptop information that was just entered on the form. When I print I get all the forms that were ever entered for the laptop, not the most recent.

In the query I have entered the following for the criteria:[Forms]![Loans]![SerialNumber]. Furthermore, I have sorted so the most recent and correct entry is the first one to print.

And this is what is entered for the report:
Private Sub equipmentreport_Click()
On Error GoTo Err_equipmentreport_Click

Dim stDocName As String

stDocName = "equipment"
DoCmd.OpenReport stDocName, acPreview

I just don't need all the subsequent records after the most recent. Any assistance will be greatly appreciated.

Thanks in advance.

 

[bethabernathy]

You need to enter criteria in the query that is unique to the equipment just entered. What is the unique field for each laptop?

Beth

 

[parsok]

Every laptop has a unique serial number. However, that same serial number may be in the laptop loan table multiple times depending on how many times it has been loaned.

 

[bethabernathy]

So, is your laptop loan table a part of the query? Beth

 

[parsok]

Yes, it is. I could add a field to the laptop loan table called uniqueid and let it autonumber. But then how do still get this accomplished?

 

[bethabernathy]

1) Are you linking the tables in the query by the serial number field?

2) Do you want to display the data from the laptop loan table in your results?

Beth

 

[parsok]

Yes, I am linking two tables; one is the laptop loan information and the other table is the laptop information (this includes the model of the laptop etc.)

Yes, there is data from both of these tables that need to be displayed in the loan agreement report.

 

[THoey]

I think you need to add something to specify the greatest date. Have you tried adding the following to your WHERE clause:

AND CheckOutDate =
(
SELECT MAX(CheckOutDate)
FROM MyTable
WHERE SerialNumber = [Forms]![Loans]![SerialNumber]
)

Change the names to match yours and then cut and paste this into the SQL view of the query. Terry M. Hoey

 

[parsok]

I'm not familiar with SQL. Where do I put the above statement

SELECT TOP 1 [Laptop].[SerialNumber], [Laptop].[Model], [Laptop].[Modem], [Laptop].[InventoryDate], [Loans].[LoanedTo], [Loans].[Service], [Loans].[LoanedDateTo], [Loans].[LoanedDateFrom], [Loans].[WorkOrderNumber], [Loans].[Phone], [Loans].[ReceivingPerson], [Loans].[ApprovingOfficial], [Loans].[CDRom], [Loans].[FloppyDrive], [Loans].[PhoneCord], [Loans].[UniqueID]
FROM Laptop INNER JOIN Loans ON [Laptop].[SerialNumber]=[Loans].[SerialNumber]
WHERE ((([Laptop].[SerialNumber])=[Forms]![Loans]![SerialNumber]))
ORDER BY [Laptop].[SerialNumber];

 

[THoey]

Try this:

SELECT [Laptop].[SerialNumber],
[Laptop].[Model],
[Laptop].[Modem],
[Laptop].[InventoryDate],
[Loans].[LoanedTo],
[Loans].[Service],
[Loans].[LoanedDateTo],
[Loans].[LoanedDateFrom],
[Loans].[WorkOrderNumber],
[Loans].[Phone],
[Loans].[ReceivingPerson],
[Loans].[ApprovingOfficial],
[Loans].[CDRom],
[Loans].[FloppyDrive],
[Loans].[PhoneCord],
[Loans].[UniqueID]
FROM Laptop INNER JOIN Loans ON [Laptop].[SerialNumber]=[Loans].[SerialNumber]
WHERE ([Laptop].[SerialNumber]=[Forms]![Loans]![SerialNumber]) AND
([Loans].[LoanedDateTo] =
(
SELECT MAX([Loans].[LoanedDateTo])
FROM [Loans]
WHERE [Loans].[SerialNumber] = [Forms]![Loans]![SerialNumber]
))
ORDER BY [Laptop].[SerialNumber];
Terry M. Hoey

 

[bethabernathy]

I think what you need to do is to add the table name where the date is pulling from ie:

[Loans]![Serialnumber]

This needs to go into the field section of the query (ie the top row).

Then in the criteria section of the query enter the serial number and that should segregate out the data.

Beth

 

[parsok]

O.K. I've tried both of these suggestions (not at the same time.)

Syntax errors occur with the SQL idea.

Beth,

Regarding your suggestion, it still wants to print every loan that a particular laptop has ever had instead of the most current. I really appreaciate everyone's help. Do you have any more ideas?

 

[THoey]

What are the syntax errors? I cannot recreate what you have there, so if you tell what is happening, I can adjust my "mindset". Terry M. Hoey

 

[bethabernathy]

Can you zip up the database and email it to me at

beth@integratedresourcemgmt.com

I can look at it and see what is going on.

Beth

 

[parsok]

Thanks, I appreciate your help. It's working.

 

[THoey]

Can you tell us what you did to fix it? Terry M. Hoey

 

[bethabernathy]

The join properties on the query were set
incorrectly. I set it so that "Output All Fields" is set to "Yes" and "Top
Values" is set to "All."