Solved

Access Unique Query

Posted on 2013-12-09
22
309 Views
Last Modified: 2014-02-18
I have a query that pulls data from 3x tables. For one of those fields I would like to output only one instance of a field (it is email address data).

I expected I could use the First function in grouping by including a date-time stamp field and sorting it in reverse order so that the "first" occurrence would indeed be the latest entry but when I run the query all records are still returned. Have not used grouping to do this before but read somewhere that it may be a solution to creating a unique query based on a single field value (vs the entire record).

Any ideas?

Goal: output a query as unique for ONE field value contained in the results

(this is in Access 2003 BTW, in case that is a factor)
0
Comment
Question by:DanielT
  • 12
  • 4
22 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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.
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
Comment Utility
Actually, I would look real closely at your results and make sure they are what you expect.  I avoid use of First( ) like the plague, because you frequently don't really get what you are looking form.

The best way to ensure you get either the earliest or latest entry for a group of records is to use a date/time or autonumber field and use the Min() or Max() function to get the minimum or maximum date/time or autonumber value for each grouping.  Then join that result set back to your table to get the rest of the fields of interest.
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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?
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
No, those "Missing" records will have something to do with the HAVING clause or from the INNER JOINS of the various queries.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:DanielT
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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?
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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.
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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!
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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!
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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?
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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".
0
 
LVL 2

Author Closing Comment

by:DanielT
Comment Utility
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.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ust html in ms/access WebBrowswer 11 41
MS SQL server Varchar and nvarchar, GMT_DATE 23 49
Help with SQL Query 23 39
Azure SQL DB? 3 13
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now