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
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?
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.
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?
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";
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];
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?
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);
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.
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<>"";
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?
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?
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.
I've got a code that allows me to select one or many names from a listbox on a form and return data relevant to the name(s) selected from a query. The following code is triggered by a button on the form...
Private Sub Toggle4_Click() 'Set it all up for CSM selection Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[code]....
(Toggle 10 goes to the code for the second listbox which has the same code with different tables refered to giving two selections in the query.)
What I want to do is replace the "warning if nothing found" with a code to show data against all the names in the list box if nothing is selected in the listbox.
So I have a multiselect listbox. The list that it shows depends on what is selected in a separate combo box.I want to be able to select multiple items from the listbox, and then be able to leave the record, and when I come back to it, to still have those items highlighted. Right now in my form, as I go through records, if I highlighted items 1,2 and 3 on a record, those first three lines will stay highlighted as I move through different records, rather than changing to what had been selected for each record.
I've already stored the actual selected values in a subtable. I just want to have it highlighted again so that you can see what was previously selected.
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 .......
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
