Solved

Select Distinct on one Column

Posted on 2015-02-04
12
117 Views
Last Modified: 2015-02-05
Hello,

I have been trying different things to get this query to select distinct on only one field:  TractorPlate but can't seem to get it right.  

Any help would be great!


SELECT MGNameAddressPhone.FName, MGNameAddressPhone.LName, MGNameAddressPhone.LicenseNumber, MGSponserLocationBadge.Company, MGSponserLocationBadge.Make, MGSponserLocationBadge.TractorState, MGSponserLocationBadge.TractorPlate, MGSponserLocationBadge.TrailerState, MGSponserLocationBadge.TrailerPlate, MGSponserLocationBadge.TruckNumber, MGSponserLocationBadge.TrailerNumber
FROM MGNameAddressPhone INNER JOIN MGSponserLocationBadge ON MGNameAddressPhone.PersonID = MGSponserLocationBadge.PersonID;
0
Comment
Question by:Ernest Grogg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40590447
You can use an aggregating query and select Group By on TractorPlate only.

Then decide if you want Min or Max for the other fields for lowest/highest value, or First/Last for some more or less random single value. If you cannot decide, you will have to either also Group By on these or leave them out.

It all depends on the result you expect.

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40590811
I have tried using the MAX and MIN and GroupBy, but always get a syntex error and I read the article and I even saw the question but couldn't get it to work for me

Here is what I did but got the error that FName is not part of the aggregate function.

SELECT MGNameAddressPhone.FName, MGNameAddressPhone.LName, MGNameAddressPhone.LicenseNumber, MGSponserLocationBadge.Company, MGSponserLocationBadge.Make, MGSponserLocationBadge.TractorState, MGSponserLocationBadge.TractorPlate, MGSponserLocationBadge.TrailerState, MGSponserLocationBadge.TrailerPlate, MGSponserLocationBadge.TruckNumber, MGSponserLocationBadge.TrailerNumber
FROM MGNameAddressPhone INNER JOIN MGSponserLocationBadge ON MGNameAddressPhone.PersonID = MGSponserLocationBadge.PersonID
GROUP BY MGSponserLocationBadge.TrailerPlate;
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40590844
You have to apply one or the other to all fields:

SELECT
    First(MGNameAddressPhone.FName),
    First(MGNameAddressPhone.LName),
    Min(MGNameAddressPhone.LicenseNumber)

and so on.

It may not make much sense, so:

> It all depends on the result you expect.

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40591176
Maybe I am not expressing what I need.

I have a form that users are filling in.  I click on a button and a search form comes up (split form)

This runs a search on the LicenseNumber, and brings up all previous records with that Number,

What I want it just the unique records under that License for the TractorPlate, TrailerPlate  I don't want to see all the records....

They select the  correct record and copy that back into anothe form they were working on to finish the record.

Using the Min/Max and trying to use a WHERE Clause doesn't work.  Or at least for me.

Screen shots are attached...
screen1.pngScreen2.png
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40591208
Then you need something like this:

SELECT MGNameAddressPhone.FName, MGNameAddressPhone.LName, MGNameAddressPhone.LicenseNumber, MGSponserLocationBadge.Company, MGSponserLocationBadge.Make, MGSponserLocationBadge.TractorState, MGSponserLocationBadge.TractorPlate, MGSponserLocationBadge.TrailerState, MGSponserLocationBadge.TrailerPlate, MGSponserLocationBadge.TruckNumber, MGSponserLocationBadge.TrailerNumber
 FROM MGNameAddressPhone INNER JOIN MGSponserLocationBadge ON MGNameAddressPhone.PersonID = MGSponserLocationBadge.PersonID
Where MGSponserLocationBadge.TractorPlate = Forms!YourFormName!TractorPlate

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40591326
I already have something like this, but it is under the form Load above which the form is using the query.

There isn't any information as of yet for TractorPlate it is under the Search.

So here:

1. User opens data entry form
2. User entes License Information
3. User clicks button to search for previous Vehicle Information
4. The OnLoad of Search form runs, filtering the data to show just that person's information based on the License Info
5. ****here is where I want to show only Unique records on the search form:  see the screenshot above.  right now it shows every record under that License Number (using the query)  so using the Where clause with trying to select distinct on TractorPlate and this is where I am confused on how to accomplish this.
6. They will select the appropriate information and click copy on the Search form.
7.  This then moves the data into the DataEntry Form to continue finishing the record.

Here is the code for the OnLoad of Search Form

Private Sub Form_Load()
 If CurrentProject.AllForms("frmLicenseSearch2").IsLoaded Then
     Forms!frmLicenseSearch2.Visible = False
     Me.txtSearch.SetFocus
     Me.txtSearch = Forms!frmLicenseSearch2.LicenseNumber
     DoCmd.ApplyFilter "", "[LicenseNumber] Like ""*"" & [Forms]![Query3]![txtSearch] & ""*""", ""

 End If

Then when the user selects the correct record (the right Tractor and Trailer Info) this is then copied to the DataEntry form:

Private Sub Command37_Click()
     Forms!frmLicenseSearch2!frmLicenseSearchSub2.Form!Company = Me.Company
     Forms!frmLicenseSearch2!frmLicenseSearchSub2.Form!Make = Me.Make
     Forms!frmLicenseSearch2!frmLicenseSearchSub2.Form!TractorState = Me.TractorState
     Forms!frmLicenseSearch2!frmLicenseSearchSub2.Form!TractorPlate = Me.TractorPlate
     DoCmd.Close
     Forms!frmLicenseSearch2.Visible = True
         
End Sub

(On my previous question I had the issue of the data being saved...so found that I needed to put the button to get to this search form on the subform...)......

Anyway

I want only Unique records to show on the Search form (screen shot above)


Maybe there is a way to add to the DoCmd ApplyFilter to show unique records on the TractorPlate?

I don't know what to do or how to accomplish this...

Hope that makes sense...
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40591362
I'm not sure what you mean with Unique. On the screendump you have blacked out the license numbers. I guess these are all Unique, then the records are all Unique.

Maybe you need a parent/sub form setup. There is a wizard for creating such.

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40591397
The License # blacked out are all the same.  They are being filterd by the DoCmd.ApplyFilter  this pulls only that License Number.

I want then to only pull unique records on the TractorPlate.

In this case, there are more than one record under that License.  I don't want to see every record for that License but only that License and all unique records for the Tractor Plate.

If you look on the Screenshot you see the same TractorPlate multiple times.  I only want to show it 1 time, not every record associated with that License.  So for this license there should be 1 record showing.  If there was more than one TractorPlate under that License, then only show that those records 1 time.

So  My search is for License# 1234 and it should show:

License#1234       TractorPlate: 1234567890
License#1234       TractorPlate: 789456123
License#1234       TractorPlate: 123456


Not:
License#1234       TractorPlate: 1234567890
License#1234       TractorPlate: 1234567890
License#1234       TractorPlate: 1234567890
License#1234       TractorPlate: 789456123
License#1234       TractorPlate: 789456123
License#1234       TractorPlate: 789456123
License#1234       TractorPlate: 123456
License#1234       TractorPlate: 123456
License#1234       TractorPlate: 123456
License#1234       TractorPlate: 123456
License#1234       TractorPlate: 123456
License#1234       TractorPlate: 123456
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40591430
OK, but then a simple Distinct should do.

Then you can't use a split form as the details (top pane) will be distinct as well.
You will need a master form with a child (sub) form.

But then you have it turned upside/down. Normally, master/child is for, say, orders on the parent and order lines on the child. So you may have to rethink your design.

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40591466
I got ya.  So, I will recreate using the parent/child and using the simple GroupBy that way I see the unique records for that license
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40591493
Great!

/gustav
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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