Select Distinct on one Column

Have a look at this article
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

and this answer
https://www.experts-exchange.com/questions/28357107/SELECT-DISTINCT-on-one-column-with-multiple-columns-returned-ms-access-query.html

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

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;

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

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

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

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

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

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

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

Great!

/gustav