Solved

Access Unique Query

Posted on 2013-12-09
22
335 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)
ID: 39707354
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
ID: 39707369
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
ID: 39707442
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:DanielT
ID: 39710477
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
ID: 39707504
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
ID: 39707720
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
ID: 39707759
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)
ID: 39708211
No, those "Missing" records will have something to do with the HAVING clause or from the INNER JOINS of the various queries.
0
 
LVL 2

Author Comment

by:DanielT
ID: 39708576
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)
ID: 39709104
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
ID: 39710346
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
ID: 39710591
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
ID: 39727698
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
ID: 39727738
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
ID: 39727789
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
ID: 39869183
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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