LukeWygas
asked on
Add data to a query from an unlinked table
I have a query (qrySimpleInterest). The entries in it are ordered sequentially by trxSeq. I need to add a new column SecVal. I have another table tblValRetroSecur (this contains a field CaseNo, this is the link between the data in qrySimpleInterest and tblValuation). This has a field RetroVal. Both qrySimpleInterest and tblValRetroSecur are ordered by date. I need to pull into qrySimpleInterest the field RetroVal for the dates that are appropriate. I have set this out in a table below as that may be easier to understand (the dates are not equal but are ordered in chronological order, so Date1 comes before Date2 etc).
tblValRetoSecur qrySimpleInterest
Date1 Val1 Date2
Date3 Val2 Date4
I need qrySimpleInterest to then have (in field SecVal)
Date2 Val1
Date4 Val2
It does not matter that Val2 comes along on Date3. I just need to find the last valuation in tblValRetroSecur for the relevant date in qrySimpleInterest.
Sorry this is not easy, I am very new to this and struggling to start. Thanks for your patience and help.
tblValRetoSecur qrySimpleInterest
Date1 Val1 Date2
Date3 Val2 Date4
I need qrySimpleInterest to then have (in field SecVal)
Date2 Val1
Date4 Val2
It does not matter that Val2 comes along on Date3. I just need to find the last valuation in tblValRetroSecur for the relevant date in qrySimpleInterest.
Sorry this is not easy, I am very new to this and struggling to start. Thanks for your patience and help.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Please don't do any more work on this. I think I can solve the problem with a function. If I have any problems I will post another comment. Otherwise I think that your comment started the train of thought going which should produce the result! Thanks
ASKER
I thought it was harsh not to award any points. Your comment started me thinking along the right lines and then I solved the problem.
ASKER
Date Valu
1/1/04 £30,000
1/1/05 £50,000
1/1/06 £75,000
The query qrySimpleInterest has a number of fields, the most important of which is date and Valu. I want the valu field from tblValRetroSecur to go into the qrySimpleInterest. However the dates are not the same, so for example, with the following dates I would need:
Date Valu
1/6/04 £30,000
1/12/04 £30,000
1/2/05 £50,000
1/3/05 £50,000
1/1/06 £75,000
In effect I need to pull down the last valuation that is relevant for a particular date.
I am totally stuck. One idea I had was to order tblValRetroSecur by date order. Would a dlookup function then produce the right result?
Thanks for your help with this!