Topics Search

Only select first row of repeating value in a column in SQL

Only select first row of repeating value in a column in SQL
Views: 427
I want to prune rows where the value of Col1 is repeated and only select the first occurrence. How can I do this in SQL?
Sponsored Links:

Similar posts...

 

Select an item from a multi column combo box programmatically

Select an item from a multi column combo box programmatically Icon
I try to select a value from a multi column combo box programmatically. When you do that with VB6 you just set the listindex property to the desired row (minus 1). The fact is that in access you can't do that because this property is read only. I googled on that and I found that you can set the combo box to Me.Combo = Me.combo.column(1, DesiredIndex). This does the trick but I believe it just sets the text property of the combo box and doesn't select the row.

How can I select the entire row so that I can use the Column(0,Combo.listindex) value?
 

Pass field value selected in Combo Box to SQL Query

Pass field value selected in Combo Box to SQL Query Icon
My form displays a combo box populated by a SQL SELECT statement. The user will select a row from the combo box. I then want the value in a field of the row selected in the combo box to be used as the selection criteria in a SQL statement.

The SQL statement is a nested SELECT and the value selected in the combo box is the selection criteria in the inner select statement.

The problem is how to pass the value selected in the combo box to the SQL query.

Also, I have the following problem . When I call the SQL query by clicking a button, a window popups asking me to enter the value for the parameter. I don't want this popup to appear. What I want to achieve is that after the user selects a row in the combo box, I want the field value to be passed to the SQL statement's WHERE clause and the SQL should execute and display the rows selected.
 

Limitations on WHERE Clause in lookup query for a table column

Limitations on WHERE Clause in lookup query for a table column Icon
I have a Access Table with two columns. Each column is defined a lookup column with an underlying SQL query.

When a user selects a value for Column A, I'd like the lookup query in Column B to show only values related to the value chosen for Column A. In other words,

SELECT OtherTable.AttrX FROM OtherTable WHERE OtherTable.AttrY = ColumnA;

I can't seem to get the WHERE clause to recognize the value chosen for Column A for the specific row of data being modified.

Is this a limitation of a lookup query in Access or I'
 

Copy from one column to another

Copy from one column to another Icon
Using SQL Server 2008. I have Googled this and can’t find anything. Should be simple. I created a new field (column) in an existing table. I want to take the data from one column and place it into the new column.

Obviously, getting it in the correct row is essential. The datatypes of the two columns are different. and the one I am copying from isnvarchar, with the data in this format:

1/7/2008 6:43:05 PM
 

copy 2 Excel columns into an Access table

copy 2 Excel columns into an Access table Icon
I have 2 columns in an Excel 2010. I need to select each column starting in row 10 (Column A and H). I need to select data until the first blank row (can't select entire column).

I need to import these 2 columns into an Access table (2010).

I'm working on a method, but it feels like maybe I'm overthinking it. I've created a macro in Excel to copy column A & H to a new Excel workbook. Then I'm doing an import into Excel. Is there a way to do this directly through Access (without the Excel Macro)?
 

The Basics of SQL - Structured Query Language

The Basics of SQL - Structured Query Language Icon
SQL is a relational database that establishes relationships between tables of data. Each table is assigned a unique name to identify its collection of columns and rows. Each row represents a record. The record contains fields that are identified by column names. Each column (or field) has specific attributes, such as data type and format.
 

Optimization of TOP-clause in SELECT

Optimization of TOP-clause in SELECT Icon
I have a table with a lot of rows. The rows have a type field, and for every type of row there are a number of rows, each with a unique number (in a number field).

I want to retrieve the row with the LOWEST number, for a certain type. To make this search fast, there is an index in the table - sorting the rows by TypeField-NumberField.

I retrieve the row I want with this SQL-statement: "SELECT TOP 1 * FROM TableX WHERE TypeField='123' ORDER BY NumberField". The SQL parser should be able to find the row I want very quickly - using the index.

But this doesn't work. When there were only a couple of thousand rows of each row-type, everything seemed to work fine. But now we have almost 200 000 rows of one of the row-types - and asearch for that type takes several seconds.

I thought the Access driver would optimize my query. And only read one record. The index contains the NumberField (within every type), so the first row found would be the one to return. But it seems like all 200 000 records are retrieved, and THEN the first row only is returned.
 

Using a lookup column as row source

Using a lookup column as row source Icon
I'm trying to set up a combo box that will search my records and I've gotten it to work, however I'm trying to fix the values it displays.

The row source for the combo box is

SELECT [Match].[MatchID], [Match].[Home], [Match].[Away] FROM [Match];
, with the fields Home and Away being lookup fields themselves, with rowsources
SELECT [Team].[TeamID], [Team].[TeamName] FROM Team;

So the search combo box displays MatchID, Home and Away fine, but it shows the TeamID column of Home and Away, and I would like it to show the TeamName column. Is there a way to use the column function (I.e. Column(#)) in the row source?
 

Display column totals in a datasheet

Display column totals in a datasheet Icon
Microsoft Office Access 2007 provides a new tool called the Totals row that simplifies the process of summing a column of data. You can also use the Totals row for other calculations such as averages, counting the number of items in a column, and finding the minimum or maximum value in a column of data.
 

value of a listbox column (Multi Select: none))

value of a listbox column (Multi Select: none)) Icon
I'd like to set my listbox to a "default" value, but the bound column is an ID, where as the value I'd like to find is in another column of the listbox. But I can't seem to figure out how to search the columns of the listbox except for the bound column. This is a simple listbox (No multiselect).

By way of example, I want the listbox to default to the 8/6/2011 row (note that the PK is in column 0 which is hidden, so the dates are in column 5):

I have been able to accomplish this in the interim by iterating the bound column (which is the primary key), and then doing a DLOOKUP() on the table. This seems rather a long way to go tofind the values that are right there in the listbox!

Surely there's a way to scan the column values?