I have a table with the product cost(A) and another table with the cost from invoices(B).
I would like to find from the A table the item cost and find the last invoice cost from B table
Anyone knows how to do this?
So what I understand you want to get the invoice from Table B, with same cost in Table A (Latest Date)
Then the last Cost 175 should attach with 1111 invoice no. correct ??
or you are considering any other logic ??
By reading your query what I can understand is you have to map invoice of table B to Table 1having the same cost & same ItemID.Just applymap it if that the issue.But i guess the requirement is something else.
Trim(ItemID) & '+' & Cost as KeyRC
Left join (RealCost)
Trim(ItemID) & '+' & Cost as KeyIC
You should manage the field names in order to avoid syntetic keys !!!
Hope this helps you.
I attached your answer, but did not solve the problem.
For example the second line in cost table is linked with invoice line "11111" and has to be linked to invoice "4444"
Many thank's for your patience