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:

More topics

 

Color change of row based on a column value in Report

Preview
In MsAccess 2007,I want each row color to change based on a column value in the report.

For example: Select Name,Age,Salary from table 1 -creates 3 rows.

Name Age Salary
A 15 1000
B 25 2000
c 35 3000

if Salary column is less than 1001,row should be in red- Row 1

if Salary column is less than 2001,row should be in green -Row2

if salary is less than 3001, row should be orange-Row3 .
How to do conditional color formatting of the entire row in Msaccess2007 based on a column value of each row
 

Conditional formatting of row based on column value in Report

Preview
In MsAccess 2007,I want each row color to change based on a column value in the report.

For example: Select Name,Age,Salary from table 1 -creates 3 rows.

Name Age Salary
A 15 1000
B 25 2000
C 35 3000

if Salary column is less than 1001,row should be in red- Row 1

if Salary column is less than 2001,row should be in green -Row2

if salary is less than 3001, row should be orange-Row3 .
How to do conditional color formatting of the entire row in Msaccess2007 based on a column value of each row
 

How to get Identity value of Last Inserted Row

Preview
I am having a table with one of the Column as SerialNo whose datatype is Autonumber.

whenever I Insert a row I want to get the SerialNo of that row.

In Sql Server , there is @@Identity so if we want last inserted row Serialno we write like this.

Select @@Identity

I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007.

Please If any one knows the solution .help me out
 

Odd tabbing through Rows

Preview
I have created bunches of simple access DB, and never had this problem, it only has happened in on DB, and the form design was no different than before. Normally in the form in the row I enter figures, tab and go to the next column space in that row, however in the one form when I start off in the first column, I hit tab button and instead of taking me to the next column in the same row, it jumps down to the next column in the row BELOW the row I am working in, in other words it tabs down and to the right. Once I click using the mouse back into the cell I wanted, tabbing through the rest of the columns in that row work fine, its just going from column 1 to 2 that causes a row jump down
 

Select an item from a multi column combo box programmatically

Preview
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

Preview
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.
 

Creating a Grouping query

Preview
I was wondering if someone can help me out in creating a query or new table. I have 2 columns from the same table and one column has many of the same numbers but the other column has unique values. I want to group the repeating number and insert a new field containing all the unique numbers associated with the repeating one.
here is the sample:

Number1 Number2
etc.

here is what I would like the output to be

Number2 Repeating Numbers
 

Random row selection without selecting twice

Preview
I have below statement that select row from my table at random.

sql = "SELECT TOP 1 * FROM Question1to5table ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"

how do make the row selection unique.

:::: maybe mark a selected row on the dataset, if possible how do I do that ::::
 

Random row selection without selecting twice

Preview
I have below statement that select row from my table at random.

sql = "SELECT TOP 1 * FROM Question1to5table ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"

how do make the row selection unique.

:::: maybe mark a selected row on the dataset,
 

Limitations on WHERE Clause in lookup query for a table column

Preview
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'