Query Builder: Only show first value of duplicates (1 Viewer)

carterlw12 · Oct 11, 2018

I am operating in the SQL View of the Query builder.
I have a list box on a form that is pulling values based off my query. The problem is I have several duplicate values. For instance, when I import files using a form, I have a column in the table update with the file name for every record that was in that particular file. So there could be like 100 records that all have FileName = rcokrons_20180719@1603 but each line has its OWN DatabaseID (primary key)...

I've tried two types of SQL statements.

Code:

SELECT DISTINCT TOP 5 ADP_Person_Import_tbl.DatabaseID, ADP_Person_Import_tbl.FileName
FROM ADP_Person_Import_tbl
ORDER BY ADP_Person_Import_tbl.FileName DESC;

'This gives me every instance of "rcokrons_20180719@1603" instead of just giving me 1 instance.

Code:

SELECT DISTINCT TOP 5 ADP_Person_Import_tbl.FileName
FROM ADP_Person_Import_tbl
ORDER BY ADP_Person_Import_tbl.FileName DESC;

'When I hit RUN to test, this gives me the first instance of each FileName, which is what I want, but 
when I go to Form View, nothing shows in the list box anymore....

June7 · Oct 11, 2018

What are ColumnCount and ColumnWidths set to? Should be 1 and nothing.

plog · Oct 11, 2018

Your title asks for 'First value', but you neither give us a way to define order on your table nor why 'First value' is important. It seems you simply want your drop down to display one filename along with an ID.

My advice is to try this query:

Code:

SELECT MIN(DatabaseID) AS FirstID, FileName
FROM ADP_Person_Import_tbl
ORDER BY FileName

If that doesn't work, you need to demonstrate your issue with example data. Provide 2 sets:

A. Starting sample data from your table. Include table and field names and enough data to cover every case.

B. Expected results of A. Show what you expect to end up with when you feed the data from A into your query.

carterlw12 · Oct 16, 2018

Neither of those worked.

I have over 46 fields in my table.. so to keep it simple this is what I have.

FileName, DatabaseID (primary key)

rcokrons_20180719@1603, 12
rcokrons_20180719@1603, 13
rcokrons_20180719@1603, 14
rcokrons_20180720@2000, 15
rcokrons_20180721@0600, 16
rcokrons_20180721@0600, 17
rcokrons_20180721@0600, 18
rcokrons_20180722@0900, 19
rcokrons_20180723@1000, 20

So, the filename is duplicated because there are multiple records that were imported from one file and I have code to stick the filename onto those records so I know which file they came from.

What I am trying to do, is display the 5 most recent files that were imported.

rcokrons_20180719@1603
rcokrons_20180720@2000
rcokrons_20180721@0600
rcokrons_20180722@0900
rcokrons_20180723@1000

My query is MAKING me have the Filename and DatabaseID (primary key) in the query or else it won't show anything in my box.

Does that make more sense?

plog · Oct 16, 2018

Does that make more sense?

Not it does not. This query satisfies your example:

Code:

SELECT FileName FROM YourTableName GROUP BY FileName

Please provide better example data.

carterlw12 · Oct 16, 2018

Nvm I figured it out.

Column Count = 1
Column Widths = 3"

Query Builder: Only show first value of duplicates (1 Viewer)

carterlw12

Registered User.

June7

AWF VIP

plog

Banishment Pending

carterlw12

Registered User.

plog

Banishment Pending

carterlw12

Registered User.

Similar threads

Users who are viewing this thread