Access Unique Query

Would be helpful to have a little more info about your table structure.

I would start by creating a query that gives you the "most recently updated" email address from that table,  it would look something like:

SELECT tbl_email.*
FROM tbl_email
INNER JOIN (
SELECT PersonID, Max(dateField) as MostRecentDate
FROM tbl_email
GROUP BY PersonID
) as PersonLatestEmail
ON tbl_Email.PersonID = PersonLatestEmail.PersonID
AND tbl_Email.[DateField] = PersonLatestEmail.MostRecentDate

This would give you all of the fields (tbl_email.*) from your email table for the most recent email address change in your email table.  You could then use this query as a subquery joined to your other two tables.

BTW, I generally include a DefaultAddr (yes/no) field in my PeopleEmail table to indicate the preferred email address,  which would avoid all of the above.

Thx.

There is a field for default email address but this data is being filtered on a date-time stamp for a different table - specifically [Jobs.Date_LastUpdate], as below.

Does this SQL help to clarify? I see the First(qryCustomers.eMail_Primary) grouping in the SQL command that was inserted by the query builder. Not that familiar with the SQL and how this would affect results.

SELECT IIf([Quote_Flag],"No","Yes") AS Group_Client1,
z_JobType.JobTypeName AS Group_JobName,
qryCustomers.NameFirst AS [First Name],
qryCustomers.NameLast AS [Last Name], qryCustomers.NameFull AS [Full Name],
First(qryCustomers.eMail_Primary) AS [eMail Address],
Jobs.Date_LastUpdate

FROM z_JobType INNER JOIN (qryStaff INNER JOIN (qryCustomers INNER JOIN Jobs ON qryCustomers.Cust_ID = Jobs.Cust_ID) ON qryStaff.Staff_ID = Jobs.ProjectLeader) ON z_JobType.JobTypeName = Jobs.JobType_ID

GROUP BY IIf([Quote_Flag],"No","Yes"), z_JobType.JobTypeName, qryCustomers.NameFirst, qryCustomers.NameLast, qryCustomers.NameFull, Jobs.Date_LastUpdate, Len([eMail_Primary]), Jobs.VoidJob

HAVING (((Len([eMail_Primary]))>4) AND ((Jobs.VoidJob)=False))

ORDER BY First(qryCustomers.eMail_Primary), Jobs.Date_LastUpdate DESC

Hey,

I may have got it now. SQL result is...

=====
SELECT First(IIf([Quote_Flag],"No","Yes")) AS Group_Client,
First(z_JobType.JobTypeName) AS Group_JobType,
First(qryCustomers.NameFirst) AS [First Name],
First(qryCustomers.NameLast) AS [Last Name],
First(qryCustomers.NameFull) AS [Full Name],
qryCustomers.eMail_Primary AS [eMail Address]

FROM z_JobType INNER JOIN (qryStaff INNER JOIN (qryCustomers INNER JOIN Jobs ON qryCustomers.Cust_ID = Jobs.Cust_ID) ON qryStaff.Staff_ID = Jobs.ProjectLeader) ON z_JobType.JobTypeName = Jobs.JobType_ID

GROUP BY qryCustomers.eMail_Primary

HAVING (((First(Len([eMail_Primary])))>4) AND ((First(Jobs.VoidJob))=False))

ORDER BY qryCustomers.eMail_Primary, First(Jobs.Date_LastUpdate) DESC;
=====
This was not quite intuitive but this article helped...
http://support.microsoft.com/kb/292634

I had originally set the column with duplicates to First and left the others defaulted to GroupBy but I needed to do the opposite. That is what I did not think was intuitive - will have to look closer.

I've requested that this question be closed as follows:

Accepted answer: 0 points for DanielT's comment #a39707442

for the following reason:

The URL provided is a solution to my original enquiry. Had everything OK except the setup for grouping was incorrect with the GROUPBY and FIRST settings essentially backwards from what they needed to be.

Hey Fyed,

I believe I understand.

Is that not because you cannot be certain of the order of records? But I have specified in the query a sort parameter by email and date. I know on the sample set of data the correct results were returned as I analyzed in Excel post export and there were no duplicates remaining. However, I am checking to see if there are any expected results missing now.

Considering the query, would this not return reliable results?
How else can the query return records based on unique values for only one field without writing  a routine to walk through the data set?

Fyed,

Well, I had a query that I expected a return of 1600 records for. The result produced 1590 so it took a little digging but found that 10 records were indeed not included in the set that should have been. But there was nothing common about the missing records. Most were not duplicated and there was nothing to indicate why they would be skipped. Two of the ten records missed WERE duplicated entries (just for the email address) but neither record made it into the list.

Is this the reliability issue you were referring to?
Odd that it would "miss" 10 records of 2000 (0.5%). Really odd.

The data set in this case will still do fine but it would be good to have a reliable solution.
I will check on changing the usage of (FIRST).

No, those "Missing" records will have something to do with the HAVING clause or from the INNER JOINS of the various queries.

Didn't have time to post this last night but changing the prior SQL to the following returned the expected number of records; there were otherwise no changes in the JOIN or HAVING clause. The two remaining FIRST functions were originally inserted by Access when the query was editing in the query builder. If they are removed the query does not work and MAX cannot be used for these (the query builder complains)...

=====
SELECT
First(IIf([Quote_Flag],"No","Yes")) AS Group_Client,
Max(z_JobType.JobTypeName) AS Group_JobType,
Max(qryCustomers.NameFirst) AS [First Name],
Max(qryCustomers.NameLast) AS [Last Name],
Max(Trim([NameFull])) AS [Full Name],
qryCustomers.eMail_Primary AS [eMail Address]

FROM z_JobType INNER JOIN (qryStaff INNER JOIN (qryCustomers INNER JOIN Jobs ON qryCustomers.Cust_ID = Jobs.Cust_ID) ON qryStaff.Staff_ID = Jobs.ProjectLeader) ON z_JobType.JobTypeName = Jobs.JobType_ID

GROUP BY qryCustomers.eMail_Primary

HAVING (((First(Len([eMail_Primary])))>4) AND ((Max(Jobs.VoidJob))=False))

ORDER BY qryCustomers.eMail_Primary, Max(Jobs.Date_LastUpdate) DESC;
=====

Since you mentioned avoiding FIRST(), I am unsure of this latest query even though in this current instance it returns all records now.

Do I understand correctly that qryCustomers will have the same customer data in most of the fields except the eMail_Primary field?  

A properly normalized database would have a table exclusively for customer email addresses, if you are allowing them to store more than one email address per customer.  This table might contain fields (CustID, eMailAddr, DefaultAddr, LastUpdated).

What does the SQL look like for qryCustomers?

Re: Admin Comment.
Yes, I know and agree!

That's why I requested that you cancel my request to close as solved. Without input from fyed I may not have caught the few records missed in the first query. Again, please cancel the close request.

fyed

The email addresses are intentionally limited to just two. The database is not intended to keep track of a variable number of email addresses. Not sure I understand your question/statement about the "same customer data in most of the fields". The customer table should be adequately normalized.

The Customer Query is essentially a SELECT statement simple but links customer fields to province and PrefContacts tables...

SELECT Customers.Cust_ID, Customers.SLUCustNo, Customers.NamePrefix, Customers.NameLast, Customers.NameFirst, IIf(Len([NameFirst])>0,Trim([NamePrefix]) & " " & Trim([NameFirst]) & " " & Trim([NameLast]),Trim([NamePrefix]) & " " & Trim([NameLast])) AS NameFull, Customers.Address1, Customers.Address2, Customers.City, Customers.Province, z_Province.Prov_Full, Customers.PostalCode, Customers.Phone_Cell, Customers.Phone_Home, Customers.Phone_Business, Customers.Phone_BusinessExt, Customers.BestTime, Customers.eMail_Primary, Customers.PrefContactMethod_ID, Customers.Comments, Customers.Spouse_NameFirst, Customers.Spouse_NameLast, Customers.Spouse_eMail, Customers.Date_LastUpdate, Customers.Date_Entered, Customers.Print_Flag, Customers.Sys_Flag, Customers.Intersection

FROM z_Province INNER JOIN (z_PrefContact INNER JOIN Customers ON z_PrefContact.PrefContactMethod = Customers.PrefContactMethod_ID) ON z_Province.Prov = Customers.Province

ORDER BY Customers.NameLast, Customers.NameFirst;

BUT - do you have any concerns with the SQL as posted above
(Posted on 2013-12-10 at 09:29:44ID: 39708576)
It has worked as expected and did not "miss" any data.

Thx!

Fyed,

Any comments on...
by: DanielTPosted on 2013-12-10 at 09:29:44ID: 39708576

That post (with your significant help!) is the current best solution as it produced correct results. However, Access insisted on the SELECT statement as shown and you had mentioned avoiding FIRST() which is not accomplished within that statement.

I know it's pretty close to Christmas now.
Please advise when you can - no rush at this point!

Yes - I requested it because I chose my answer as a solution. It "almost" was but input by Fyed pointed out a potential for missed records. He was right. So I asked for it to be reopened to enable assigned due-credit when the  question is closed. I would have closed by now except for Fyed comments about using FIRST() with the SELECT clause which of course were important.

At this point I would like to hear their comments on Access' insistence of using it as mentioned since if there is a way to avoid it, I will do so.

Make sense?

Frankly I do not understand how it cannot be entirely clear, despite my asking the question.

Your involvement was only requested to remove MY acceptance of MY answer. It was as simple as that. I am unsure of why there would be more involvement or any confusion after doing what was asked.

"Unmonitoring" is certainly best. I am less clear of why you'd (meaning admin monitoring of course - not any individual) still be "hanging around".

Although the code presented 'almost' worked, Fyed's point was accurate and not all records were returned. As there were no further comments from Fyed, I have assigned his answer pointing this out to be the correct answer as it was critical to getting the right results.