Find last record in a one to many relationship in vba Access

Hi, I have a QUOTE database with many quotes. Each quote has 1 to many entries in the table QUOTECRM. I need to have a query which will display all quotes (as per QUOTE table) together with the last entry in QUOTECRM. The last entry in QUOTECRM will have the highest CRMID number. Thank you so much for any help! Cheers Michael
mpimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what fields from table QUOTE, QUOTECRM do you need to show?
what is the field that link the two table?
Rey Obrero (Capricorn1)Commented:
try this query


select A.*
from quotecrm as A
Inner join (
select quotecrm.id, max(quotecrm.crmid) as MaxCrmID
from quotecrm
group by quotecrm.id
) As B
On A.ID=B.ID and A.crmid=B.MaxCrmID

save the query as QueryCRM

now create another query using table Quote and QueryCRM with join in field ID

ID is the field that links the two table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ineuwCommented:
Below are the explanations of the steps in the code. I should point out that there is an error, because it assumes that there is always a middle name. Look at my comments at the end of each line.

Private Sub cmdInitials_click()

Dim strInput As String, stroutput As String, strFirstName As String, strLastName As String, strMiddleInit As String, str3A As String, str3 As String ', be sure to remove the comma at the end str5 As String
Dim intInput As Integer, sngInput As Single, int3A As Integer, int1 As Integer, int2 As Integer, int3 As Integer, int4 As Integer

' The code assumes that there is always middle name:
' using test name John C. Smith = JCS is correct (13 characters)
' test name John Smith = JSS which is wrong (10 characters)

  strFirstName = Left(txtInput.Value, 1) ' extracts J
  int1 = InStr(txtInput.Value, " ") ' value of the first name length 5 characters (until the space)
  int2 = int1 + 1 ' increments the numeric value for next character position, +1 skips the space following J
  strMiddleInit = Mid(txtInput.Value, int2, 1) ' extracts the 1st character of the middle name from the string C.
  int4 = Len(txtInput.Value) ' length of John C. Smith
  str3A = Mid(txtInput.Value, int2, (int4 - int1)) ' extracts the leftover string starting from the 6th position (13-5) the 8 character length of C. Smith
  int3A = InStr(str3A, " ") ' locates the value of the middle initial length 2 characters (until the space) C.
  strLastName = Mid(str3A, (int3A + 1), 1) ' extracts the 1st character of the last name from the 4th position of C. Smith
  stroutput = UCase(strFirstName) & UCase(strMiddleInit) & UCase(strLastName) ' assembles the initials.
  Me.lblOutPut.Caption = stroutput

End Sub
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
@ineuw,
you seem to have posted on the wrong thread
ineuwCommented:
Yes, I did. This was my first activity here and seems to have lost the question, It included a downloadable db and an explanation of the code was requested.
mpimAuthor Commented:
Thank you so much - worked perfectly!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.