DanielT
asked on
Access Unique Query
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)
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)
ASKER
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_P rimary) 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_P rimary) 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_P rimary), Jobs.Date_LastUpdate DESC
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_P
SELECT IIf([Quote_Flag],"No","Yes
z_JobType.JobTypeName AS Group_JobName,
qryCustomers.NameFirst AS [First Name],
qryCustomers.NameLast AS [Last Name], qryCustomers.NameFull AS [Full Name],
First(qryCustomers.eMail_P
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
HAVING (((Len([eMail_Primary]))>4
ORDER BY First(qryCustomers.eMail_P
ASKER
Hey,
I may have got it now. SQL result is...
=====
SELECT First(IIf([Quote_Flag],"No ","Yes")) AS Group_Client,
First(z_JobType.JobTypeNam e) AS Group_JobType,
First(qryCustomers.NameFir st) AS [First Name],
First(qryCustomers.NameLas t) AS [Last Name],
First(qryCustomers.NameFul l) 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_Primar y])))>4) AND ((First(Jobs.VoidJob))=Fal se))
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 may have got it now. SQL result is...
=====
SELECT First(IIf([Quote_Flag],"No
First(z_JobType.JobTypeNam
First(qryCustomers.NameFir
First(qryCustomers.NameLas
First(qryCustomers.NameFul
qryCustomers.eMail_Primary
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_Primar
ORDER BY qryCustomers.eMail_Primary
=====
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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).
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.
ASKER
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_Primar y])))>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.
=====
SELECT
First(IIf([Quote_Flag],"No
Max(z_JobType.JobTypeName)
Max(qryCustomers.NameFirst
Max(qryCustomers.NameLast)
Max(Trim([NameFull])) AS [Full Name],
qryCustomers.eMail_Primary
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_Primar
ORDER BY qryCustomers.eMail_Primary
=====
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?
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?
ASKER
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.
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.
ASKER
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,Tri m([NamePre fix]) & " " & Trim([NameFirst]) & " " & Trim([NameLast]),Trim([Nam ePrefix]) & " " & 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_BusinessEx t, Customers.BestTime, Customers.eMail_Primary, Customers.PrefContactMetho d_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.PrefContactM ethod = Customers.PrefContactMetho d_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!
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,Tri
FROM z_Province INNER JOIN (z_PrefContact INNER JOIN Customers ON z_PrefContact.PrefContactM
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!
ASKER
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!
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!
ASKER
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?
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?
ASKER
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".
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".
ASKER
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.
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.MostRece
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.