Select Distinct From Multiple Columns In Same Table
Sep 7, 2006
I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.
The following works for one column:
SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";
Are there any suggestions?
Thanks
View Replies
ADVERTISEMENT
Jan 29, 2015
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
View 14 Replies
View Related
Jul 1, 2013
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
Table: EVAP Database
Combo: Combo1
Filter Listbox: Filter1
View 6 Replies
View Related
Oct 22, 2014
I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox
Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code:
Dim ItemIndex As Variant
For Each ItemIndex In Me.lstPayPeriods.ItemsSelected
If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then
Date1.SetFocus
Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex)
End If
Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code:
Dim CurrentRow As Integer
Dim FirstDate As Date
For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1
If Me.lstPayPeriods.Selected(CurrentRow) Then
Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
View 2 Replies
View Related
Sep 15, 2006
Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......
View 1 Replies
View Related
Aug 9, 2005
I need to select two fields in a select DISTINCT query
Select tbl_Aplaws.AplawID, DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
anyone know how to make this work?
View 1 Replies
View Related
Sep 13, 2005
Hello,
I have a strange problem: one of my comboboxe is looking up a column in one of my table. Some of the record can be duplicated so I decided to change SELECT to SELECT DISTINCT in my SQL for the row source but it doesn't seem to change anything. Is anybody's got an idea why?
View 1 Replies
View Related
Nov 15, 2007
Ok , I will try and explain this as best I can.
I have a database and would like to run a DISTINCT query ( which I can do no problems ) I would also like to count the number of records at the same time. Say for example my database looked like this
Name Fruit
Adam Apple
Carrie Apple
Hollie Apple
Joe Apple
Adam Orange
Carrie Orange
Hollie Orange
Joe Orange
What I would like it to do is Select the distinct names and list them once ( I can do this bit )
Adam
Carrie
Hollie
Joe
I would then like to count the number of records found for each name and put it next to the relevant name
for example Adam (2) (one apple and one orange)
How would I write the actual query
I hope this makes sense.
Thanks in advance
Adam
PS , great site btw
View 10 Replies
View Related
Sep 18, 2006
I'm making a form for my school that needs to show every parents name, along with the child they are the gaurdians of. When i use SELECT DISTINCT [Parent Menu PTC].Relations_FullName FROM [Parent Menu PTC]; as the source code it shows only one of each parents name, btu when i use that; I can't see a divorced parents child (it shows bboth parents but only one kid, and they have two kids...) Can any1 help?
View 2 Replies
View Related
Jul 9, 2012
I am trying to run a query that queries and returns several data fields with one of them being an ID field. I want to select distinct IDs only, however, I don't think just adding a 'distinct' qualifier prior the ID field will do it--the only field that I want to be distinct is the ID, the others it doesn't matter.
how to add a 'distinct' qualifier to a query that just applies to one field only (the ID field)?
Select distinct ID, firstName, lastName, DOB, City from Person where City = "NYC";
View 1 Replies
View Related
Oct 2, 2007
I am working in Access 2003. I have a combo box based on the query below that works perfectly except that it shows duplicate Department Names in the combo box. I have not been able to find any way to show the Department Name only once. I have tried putting in SELECT DISTINCT in the string, but to no avail I recieve an error :
ORDER By clause(DepartmentMembers.[DepartmentMembers]) conflicts with DISTINCT.
Any light you can shed on this subject would be greatly appriciated. I have been researching forums for over two days and have not been able to generate a solution to this. Thank you in advance!
SELECT Departments.[Department Name], DATA.[BUILDING LOCATIONS], DATA.[REPORTS TO], DATA.[DIRECTOR NAME], DATA.[NUMBER OF PERSONNEL], DATA.[PC NAMING CONVENTION], DATA.[CISCO SWITCH(ES) ATTACHED], DATA.[SERVER-BASED APPLICATIONS USED], DATA.[DEPARTMENT SHARE (Z:DRIVE)], DATA.[EXTERNAL SERVERS], DATA.[Department ID]
FROM (DATA INNER JOIN DepartmentMembers ON DATA.[Department ID]=DepartmentMembers.[Department ID]) INNER JOIN Departments ON (Departments.[Department ID]=DepartmentMembers.[Department ID]) AND (DATA.[Department ID]=Departments.[Department ID])
ORDER BY Departments.[Department Name], DepartmentMembers.[Department Members];
View 14 Replies
View Related
Jul 6, 2005
I am wanting to use the SELECT DISTINCT clause to choose unique records from a DB but I need my query to give me all of the fields of the table. When I run a SQL statement such as:
Code:SELECT DISTINCT ContactID, FirstName, LastName, ContactType, SpecialtyFROM Contact_Specialties
I do not get distinct Names because the full row is distinct yet the ContactID is not distinct on its own.
How do I SELECT the all of the fields that I want while only having the ContactID be distinct?
Please help I am a newbie.
DrMeager
View 7 Replies
View Related
Aug 12, 2014
Is it possible to change this query to search for either a Name or by an employee ID number? The query runs a report on after update. Currently I can only enter the name not the employee ID
Code:
SELECT DISTINCTROW First(Inventory.Employee) AS FirstOfEmployee, Inventory.[Employee ID]
FROM Inventory GROUP BY Inventory.[Employee ID] ORDER BY First(Inventory.Employee);
View 6 Replies
View Related
Nov 18, 2011
Is it possible to use a variable in the first line of the following code?
Code:
SELECT DISTINCT [June-11].[Full Name], [June-11].[Level 8 Name]
FROM [June-11]
WHERE ((([June-11].[Level 6 Name])="SMITH, BOB") AND ((Exists (SELECT * FROM [July-11]
WHERE [June-11].[Full Name] = [July-11].[Full Name]))=False));
I have a combo box that lists months and I want to be able to choose which month goes in the SELECT DISTINCT line of code. So, where it says [June-11], I would like it to reference my combo box so I can select the month.
View 1 Replies
View Related
Aug 9, 2005
Hi
I am trying to use a query to populate a combo box. works fine as I am using one table. I want to select DISTINCT records which works. However i need the ID of that record in the select statement. Anyone know how to select two things in a select distinct query??
Select DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
this works. but i need to select the tbl_Aplaws.AplawID as well. throws errors
View 1 Replies
View Related
Dec 9, 2014
I have the following SQL which returns rows of distinct numbers that are calculated from a field.
SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;
ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307
I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:
1307
1311
1401
etc...
I just want NumberOfCalls to show "3". Is there an easy way to do this?
View 5 Replies
View Related
Sep 16, 2014
I have a Child Report with underlaying records from a SELECT DISTINCT.
I prints records of prices, I need to show "some_text" if the price is "0".
Which event should I use?
I tried ChildReport On_Activate but could not pull it off.
View 2 Replies
View Related
Jun 4, 2013
How to query Select Distinct field + showing other fields? E.g.:
Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
[Code] ......
View 7 Replies
View Related
Oct 20, 2014
I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.
The Row Source is set to
SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;
And the combo is bound to column 2
View 12 Replies
View Related
Aug 26, 2014
I want to count data from my table ( Table ) and to display result like a dashboard (Desired result ). To count this do i need to write query for every column and then link to final column? i have totally 300 columns to count I am attaching Table & desired result images;
View 5 Replies
View Related
Oct 14, 2014
I am working with other data that has been created by someone else.
There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.
So far I have tried using this SQL:
SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T
[Code] .....
But it is not working - Is it to do with the field names or am I entering something incorrectly?
View 8 Replies
View Related
Oct 22, 2014
I know how to have multiple columns fill a lookup in a combobox both from a table and a query. But I need to have the other fields that aren't saved by the combobox saved in the neighboring columns. So, my primary table is a master list of chemicals to be analyzed along with their respective registry numbers. I know what most of the programmers say about repetitive data being bad form etc. These names and registry numbers will NEVER change, so I'm not worried about a change causing problems later. I'm trying to build a separate tables that will have specific chemicals and the methods that they are analyzed under that effectively copy from the master list, but add their own quality control criteria. Further more, not all of my clients need all of the chemicals that are available for every method. It should be noted that not all chemicals are analyzed by the same methods and that some methods will have some of the same chemicals as others. I need the registry numbers because this is what the analysis software uses to uniquely identify each chemical and I need the name because names are easier for me. Long story short, I need both of these pieces together. It was suggested to have a macro copy the remaining columns from the dropbox in the table to the other columns in my table, but I'm not sure how to do this (I'm still very new to Access and my VB is very rusty). I understand how to do this for a form, but when client reporting lists become involved later on, this will make my database very bloated to have a form to populate each respective table.
View 1 Replies
View Related
Sep 15, 2014
Doing Ctrl + ' will put in the value from the cell above it. But I want to do this across 4 columns. On one row, I am selecting across 4 columns and doing Ctrl + ' and only the first cell populates with the value above it.
Is there a way to get the 3 other columns to populate with the value of them (the values are different across all columns).
View 2 Replies
View Related
May 19, 2015
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
View 14 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
May 21, 2013
I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.
View 6 Replies
View Related
