Accessing a field from a table

[mike_g]

Hi!

I'm making a test database for DVD rentals. When A loan is made I have an update query that sets some fields in the DVD copy table. One of these fields needs to be set to the primary key of the loan made. This would be the last entry on the loans table. I don't have any idea what to put in the 'Update To' box in the query though.

Wrong code here, but this may help explain what I am trying to get:

Tables.Loans.LastLoan.Loan ID

Also heres an image of my query:

Help would be much appreciated. Thanks

 

[neileg]

It would be easier to to this in a form than using an update query. hard to give more advice wthout knowing your db structure.

 

[mike_g]

Hmm... I was kind of hoping this question would have a simple one line answer, but maybe its not that easy.

I am not sure if this helps but here are my tables (if thats what you meant by DB structure):

There is also a field in copies called 'Current Loan' that I forgot to drag out.

When the a new loan is made then the 'Current Loan' needs to be set to the new loans 'Loan ID'.

Cheers.

 

[neileg]

First of all, there are lots of threads about rental applications that it might be worth reviewing.

I don't think your approach is right, here. Rather than hold the ID of the Current Loan in the copies table, you can find the current loan with a query on the Loans table by finding the latest Rental Date where the Return Date is null.