Solved

Select Distinct on one Column

Posted on 2015-02-04
12
111 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
  • 6
  • 5
12 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 40590266
0
 
LVL 49

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
 
LVL 49

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 49

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
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!

 

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 49

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 49

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 49

Expert Comment

by:Gustav Brock
ID: 40591493
Great!

/gustav
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

20 Experts available now in Live!

Get 1:1 Help Now