How Can I Select Only The Rows I Need?

Feb 4, 2008

I have spent so much time on this but I cannot get it return only those rows where "Sorter" is unique and rate is the lowest for that group (Sorter)

The table looks like this:

Sorter Lender Loan Rate Price APR
101 1 $49,999.00 5.250% -0.450% 6.256%
101 2 $49,999.00 5.500% -0.875% 6.344%
101 3 $49,999.00 5.750% -0.750% 6.486%
101 4 $49,999.00 5.875% -0.893% 6.536%
102 2 $100,000.00 5.250% -0.560% 6.070%
102 1 $100,000.00 5.500% -0.875% 6.169%
102 3 $100,000.00 5.750% -0.750% 6.308%
103 3 $125,000.00 5.250% -0.560% 6.036%
103 2 $125,000.00 5.500% -0.875% 6.134%

What I want to select and what I am looking for would look like this:

Sorter Lender Loan Rate Price APR
101 1 $49,999.00 5.250% -0.450% 6.256%
102 2 $100,000.00 5.250% -0.560% 6.070%
103 3 $125,000.00 5.250% -0.560% 6.036%

Any help will be greatly appreciated.

Bob

View Replies

Select Last 4 Rows Of A Table

Dec 17, 2004

Is there any way to get the last 4 rows which have been entered into the table?

Thank You.

View 2 Replies View Related

Queries :: Select All Rows Except Last

Jan 22, 2015

How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.

At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:

Code:

UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));

View 1 Replies View Related

SELECT Certain Rows Based On Criteria

May 8, 2007

Hi all,

Very very new to Access, moved department a week ago and inherited a couple of databases that need tweaking. Although I'm learning I am stuck by something very simple and can't find it by searching this forum or Google :)

I have a query that originates from 2 tables, there is one custom record made from 2 of the fields -

Free Stock: [stk_stkqty]-[stk_allstk]

Now I want a record that shows -

Product where the free stock = backorder stock

so theroetically my guess as a beginner is -

SELECT * FROM [Product] WHERE [Free Stock] <> [Back Order]

But it fails miserably on syntax. Can someone point me in the right direction, and I apologise for my lack of understanding in advance.

Boofuls :)

View 5 Replies View Related

Modules & VBA :: Select Rows From Different Reports

Apr 9, 2014

Im working on a DB with multiple tables. My DB consists in a lot of tables, each one for a different element and his specifications (e.g.: transmitter, sensor, relay). The user can create loops selecting different elements (All the elements of the same loop have the same Loop ID).

I want that when the loop is finished the user can select one of the loops he has create and see all the elements and specifications he has selected for this loop. The problem is that when I want to show in a form or report all the elements the user has selected before I cant select each different element from his table and show in a form or report.Until now I have this code in a form to select an element from a report and show it, but it doesnt work very well.

-) List3 is a list box in my form where is the list of elements to select.
-) Boton1 is the button in my form to open the report when the element is selected
-) Transmitter Specifications is the report with the list of elements and his specifications.

Option Compare Database
Option Explici
Private Sub boton1_Click()
On Error GoTo Err_boton1_Click

[code]....

View 1 Replies View Related

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Modules & VBA :: Programmatically Find And Select Item In Multi Select List Box

Apr 23, 2015

I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?

View 2 Replies View Related

Forms :: Select Record On Subform To Select Default Value In Combo Box?

May 6, 2014

I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.

Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.

I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.

What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?

View 1 Replies View Related

List Box One Click Select/deselect With Multi Select

Aug 28, 2004

Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!

View 1 Replies View Related

Select Values In Multi Select Listbox

Aug 19, 2005

i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).

But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..

hope someone can help me out

View 1 Replies View Related

Modules & VBA :: Getting Error End Select Without Select Case?

Sep 5, 2014

When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".

Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

[Code].....

View 3 Replies View Related

Converting Rows To Columns And Columns To Rows

Jul 31, 2013

I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff

I would like to covert it to....

Customer 000462 000224

Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.

View 12 Replies View Related

Queries :: Select Query To Gather Results Of Other Select Queries

May 11, 2014

I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.

e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B

I want a select query that returns 1 row showing (6 items):

Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.

I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.

View 3 Replies View Related

Select Group By Having In (SELECT), HELP !

Jun 28, 2006

This is driving me nuts guys...

I need a query to show only those Company's that have a relationship to ALL Departments. These are the tables:

tblCompanies
CompanyID

tblUserDepts
UserDeptID

tjxCompanySubscriptions
CompanyID
UserDeptID

tjxCompanySubscriptions is a junction table that defines which Department has a subscription to a Company.

I need to know which Company's are subscribed by ALL Departments.

Probably simple, but not for me!

Scott

View 2 Replies View Related

UPDATE Rows

Sep 11, 2005

Hi Im trying to update a row this is the SQL im using

UPDATE HouseEntry
SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1)
WHERE
EntryNo=40;

When I run it I get an error saying "Operation Must Be an Updatable Query"

If I physically put it in the date e.g SET [Date]=#09/15/2005# It works
so there must be a problem with my Select Statement.
Any ideas???
Cheers
Bikeboardsurf

View 4 Replies View Related

Repeating Rows

Dec 25, 2005

hi
my problem is when i export data form table or query to textfile it give me repeating rows my computer is win me
help me

View 2 Replies View Related

Individual Rows

Mar 23, 2007

Hi,

refer to the (record.jpg)

the whole record is identified as " rst.RecordCount " ( red color box).
how to identify each individual rows ( blue color box)?

View 2 Replies View Related

Run Out Of Table Rows

Jun 1, 2007

The following is for a successful pet grooming business.

I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed

View 10 Replies View Related

Max # Rows In A Table

Jun 14, 2007

I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?

View 4 Replies View Related

Yes/No Boxes In 2 Rows

Mar 11, 2008

Hello there,

I am making a roster for 25 people. In my form, the start of the roster is 0700hours until 1900 hours (increments of 15 minutes each) so I have 48 check boxes, which are ticked when one is rostered on for that time.

I am getting crossed eyed looking across the screen at these 48 tick boxes each for Monday through to Friday.

I have searched high and low and cannot find anything on how to have 24 check boxes (7am to 1pm) in one row and the other 2 dozen (for the next 6 hours until 7pm) on the next row, to be less painful on the eyes.

Can this be done?

I would be grateful for any ideas on how to start.

Thank you.

Regards,
Ligaya

View 11 Replies View Related

Selecting Rows

May 17, 2006

Hallo Everyone,

I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.

aravind.s

View 1 Replies View Related

Return All Rows

Aug 16, 2006

I hope this is a simple query becuase my brain has drawn a blank on it.

I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.

Any help appreciated!

Dave

View 7 Replies View Related

Deleting Rows

Aug 30, 2006

Hello, Does anyone have any suggestions on how to delete certain rows? For example rows 10,000 to 17,000. I know in MS SQL server you can use Rowcount, but I believe there is no Rowcount in Access?

View 1 Replies View Related

Rows To Columns

Feb 27, 2007

Hi all,

Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc

transform it to

701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....

Thanks in advance

View 3 Replies View Related

Combining Rows

Jan 30, 2008

View 3 Replies View Related

Two Rows Of Tabs

May 9, 2006

I would like to create a form with a double layered tab format that has the look and feel of the MS Access Options form under Tools.

What code or technique is required to make this work? For example, how do I make sure when the user selects the front row or back row of tabs that only this form is displayed.

I hope this is clear.

Dwight

View 2 Replies View Related