Queries :: Select Distinct Field And Showing Other Fields?

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 Replies


ADVERTISEMENT

Select DISTINCT - Selecting Two Fields

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

Queries :: Either Or Select Distinct Row

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

Queries :: COUNT With Select Distinct On Expression

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

Queries :: Select Distinct For Combobox - Show Only Unique Values

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

Queries :: SELECT DISTINCT Not Removing Duplicate Records In Staging Table

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

Need Distinct Records Of Whle Table But Distinct On One Field

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

Queries :: Append Distinct Records Only If Field Have Changed

Jun 5, 2013

Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.

Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.

View 14 Replies View Related

Select Distinct

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

Select Distinct

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

SELECT DISTINCT Help...Please

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

SELECT DISTINCT Help

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

How To Select Distinct IDs Only

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

Select Distinct In A Query

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

Problem With SELECT DISTINCT? Please Help!

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

How To Use Variable In SELECT DISTINCT

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

Queries :: Records Not Showing With Blank Numeric Fields

Jan 27, 2014

I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:

Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;

View 1 Replies View Related

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 2 Replies View Related

Reports :: Select Distinct - Text Box Value On Activate

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

Added Fields To Table But Not Showing Up In Form Field List

Aug 3, 2005

I have a form based on a table and I just added 3 fields to the table. When I go to the form to add these fields, they do not show up in the field list. I've also tried creating a textbox and going to the Data Tab and choosing the data source but those 3 new fields are still not showing up.
How can I go about adding these 3 fields?

Thanks in advance

View 2 Replies View Related

Forms :: Multi-select Listbox To Display Distinct Values From Table

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

Queries :: Showing A Field Based On Another Field

Jul 8, 2014

I was wondering if it is possible to determine weather to show a field based on the values from another field.

Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values

So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.

View 3 Replies View Related

Creating Non-Calculated Fields In Select Queries

Nov 14, 2005

Hi All!

I am trying to create an updateable non-calculated field in a select query. Specifically, I want to create a Yes/No data type called "Fail".

This is a multiple-user application so I can't solve the problem by using append and delete queries to modify records in a table.

This has turned into a much stickier problem than I anticipated. I would appreciate any insight or advice you have.

View 2 Replies View Related

Queries :: Form To Select Which Fields Show In Query

Oct 8, 2013

I want to create form with 10 checkbox, each checkbox is linked to a field "name, Address, TelephoneNumber, BuissinessName" I would like to know if it is possible for the user to check the box and then the field will show in query. if only name is check then only name will show in the query.

I have tried using [Forms]![Formname]![CheckboxName] as criteria but this didn't work it when I had more then one checkbox and when the one check box was not checked nothing showed in the forms.

View 5 Replies View Related

Queries :: Conditionally Choose Fields In SELECT Clause

Feb 28, 2014

I have a table with 2 fields:

CODEDATE (text)
DATE1 (date)(dd/mm/yyyy)

RECORDS SAMPLE:

Code:
-------------------------------------
CODEDATE DATE1
-------------------------------------
TT-10/10/2013 01/01/2012
ASDFDFDF 01/02/2013
RR-18/06/2012 05/08/2012
------------------------------------

I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.

I wrote that:

Code:
SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound
FROM MyTable
WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1])
Between 1/1/2013 And 31/12/2013)

The query above extra extract some records without sense for me, I tried many other syntax without success.

View 12 Replies View Related

Queries :: Allowing Users To Select Multiple Table Fields As Query Criteria

Apr 22, 2013

I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved