Modify sql to in Row Source to grab single instance when filtered by date and has same date

[newbieX]

I have a row source that has the following sql:

SELECT t1.Name, t1.Address, t1.[Phone], t1.Email, t1.Office, t1.[SubDate]
FROM Primary AS t1
WHERE (((t1.Office)=[Forms]![frmInvoices]![cbo_Office]) AND ((t1.[SubDate])=(SELECT Max(t2.[SubDate]) FROM Primary t2 WHERE t2.Name=t1.Name GROUP BY t2.Name)));

User selects the name of the Office from a combobox. This value is passed to the record source SQL statement for the combobox cbo_Name.



The SQL and selects the name, address, phone, and email filtered by last date invoice submission occurred so I can be assured I have the most recent contact information for the Office selected.

From there I can select the Name from this pull down list or add new one.

Overall this works great except for the occasional record that has two invoice submissions on the same day. How can I rewrite this code to grab just one instance of this? I tried putting DISTINCT at the beginning of the statement but got even more multiple instances of the same name.

Again. Thanks in advance for any and all guidance.

[orange]

What is the default format of the subDate field? You could try Long Date and see if it changes anything.
Do you have a PK on the record? You could look for Max(PKId) where the Max(subdate) occurs---this isn't 100% since PK is just a number and could even be negative.

[June7]

Won't be simple.

So Primary can have multiple contact records for each office? Is Name a unique value in Primary? Is there an ID primary key field in Primary? Is it saved as foreign key in Invoices?

Name is a reserved word. Should not use reserved words as names.

[newbieX]

Primary has an unique id. InvoiceID is Autonumber.
Date format is short. No time values are stored.
Name is not an unique value in Primary, which is why I am trying to pull a single instance of the name's latest entry.
Noted about Name being a reserved word. I'll see if I can change that for them.
There is only one table. (Imported from a spreadsheet I suspect). It probably should have been given the name Invoices though as that is its true function.

I think Oranges's response about using Max(PKid) is spot on and I actually thought of that, I just wasn't sure how go about writing the SQL for this. Come to think of it I may not even need to use the date. I may just need to grab the latest name entry based on highest InvoiceID. Whaddayathink? If so, how would I write the SQL for this.

I realize the database is not normalized but it is not my database to screw with. I am just designing them a data entry form (lowly grunt worker here ).

Besides, now I just want to know how to do it. I hate unsolved mysteries. (So even if I go for the MAX(InvoiceID) how would I solve this "grab first instance" issue using date also.) Want to learn this too.

[June7]

A TOP N nested query might get what you want. Review http://allenbrowne.com/subquery-01.html#TopN

Another approach uses domain aggregates, however, they can be slow performers in query.

SELECT * FROM Primary WHERE InvoiceID = DLookup("InvoiceID", "Primary", "[Name]='" & [Name] & "' AND SubDate=#" & DMax("SubDate", "Primary", "[Name]='" & [Name] & "'") & "#");