Select Smallest Value

Oct 24, 2005

Hi,
I am a beginner at Access and have tried to search here for help regarding a calculated text field.

I have a problem with a form where I want the smallest of previous values to appear in a text field. I have tried to use the expression builder and iif statements in the control source of the calculated field but I cant make it work. I want to calculate the smallest value for every record, not the smallest value in all records.

Hope someone can help...
Thanks, Sofia.

View Replies


ADVERTISEMENT

Nth Smallest Price By Row

May 15, 2007

I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...

Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1


Thanks for any ideas....

View 4 Replies View Related

Modules & VBA :: Achieve Smallest Possible Number From Among The Largest

Oct 4, 2014

I need to find best combination using Loop to count "NumerOfSheets" To achieve smallest possible number from among the largest. Taking into account additional blocks to allocate. My table before running code looks like

Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2
2 B 200 1
3 C 100 1

At the beginning I was using code (I had no additional blocks):

Code:
Dim recIn As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl1;"
Set recIn = CurrentDb.OpenRecordset(strSQL)
While Not recIn.EOF

[Code] ....

It worked! But now I have new field in my main form "Forms!frmGlowny!FreeBlocks" Where I keep number of blocks to allocate (additional blocks which I can allocate in the column "Blocks"). This filed is count by another code. What is important now, this is positive integer (usually no more than 20). I need find best way to allocate my free blocks. What is best way? - The largest number from "NumerOfSheets" should be as small as possible.

Suppose that this example Forms!frmGlowny!FreeBlocks = 1 (so it's very simple example). So Let's find where I should allocate my 1 free block (I need do it by hand, because I don't have a code:/).

Combination 1

Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 3 117
2 B 200 1 200
3 C 100 1 100

Combination 2

Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2 175
2 B 200 2 100
3 C 100 1 100

Combination 3

Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2 175
2 B 200 1 200
3 C 100 2 50

The smallest possible number from among the largest is in the combination No. 2 (because the largest = 175 so it is smallest from all largest numbers of combinations), so now I know that my 1 free block should be added to B order to column "Block". It's very simple example because I have only A;B;C oders and 1 block to allocate. But When I will have e.g orders: A;B;C;D;E;F;G;H and 14 blocks to allocate count by hand will be terrible...

View 14 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

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

Select *

Jun 28, 2007

I have a confession to make - I am using select * in parts of my db.

Everything I read says not to do this, ie I should be selecting just the columns I need.


However what I need is a consistent "Presenation" of my data ie say Company Details - whatever kind of report or view is run, the company details must always contain the same data.


So I have a specific View_CompanyDetails which holds exactly the data my users want.

Literally 10s or Hundreds of other views or sps will then include the SELECT * FROM View_CompanyDetails for the user.


If I follow the general advice of never using SELECT * - if for example it beacme critical to have differnat data in View_CompanyDetails - I would have to go any manually change potentailly hundreds of other objects?

IN essence I would be explitly choosing may data in View_CompanyDEtails and then being forced into explicitly choosing the data again (redundantly cos View_CompanyDetails is the data I really want)


IN essence can I use SELECT *

?

It seems ridiculous not too? Or is there something else I need to think about?


Cheers

View 2 Replies View Related

Should I Use Select First?

Aug 2, 2005

I'm having trouble with a select first query. I'm getting an error saying whichever item follows my select first statement (in this example TariffID) "is not included in the aggregate function." See example. Background is below.

SELECT First (SchedulePage) as FirstPage, TariffID, TariffCPUCID, ScheduleID, [C&E]
FROM Tariffs
WHERE (((FirstPage) Is Not Null) AND ((ScheduleID)=[Forms]!...))
ORDER BY FirstPage, Tariffs.[C&E] DESC;


Background:

I have a table where records are assigned a Page. I need a query to pull up only one Page per page number. (ie - there are ten Page 1, and five Page 2, but I only need one of each). Preferably, this would pull up the one that is Current and Effective ([C&E], yes I know it's bad naming, oops), but the purpose of this query is to help when something gets messed up, so I can't rely on C&E being correct, and thus need to bring up the page, even if there isn't one marked C&E.

Long story short, I thought that by sorting by C&E, I could pull up only the first record. Similarly, I tried using Min to pull up the lowest C&E, though this may not work if there isn't one marked C&E.

View 1 Replies View Related

Select ... (select ...);

Apr 5, 2006

I need to build a query that concatenates the values in my table as follows:

The table:

monkey
horse
pelican
zebra

The query output:

monkeymonkey
monkeyhorse
monkeypelican
monkeyzebra
horsemonkey
horsehorse
horsepelican
horsezebra
pelicanmonkey
pelicanhorse
pelicanpelican
pelicanzebra
zebramonkey
zebrahorse
zebrapelican
zebrazebra

I thought the following would do the trick, but it didnt ...
SELECT table.animal & (SELECT table.animal FROM table) FROM table;

I get an error that says that the subquery max. has 1 record :confused:

Help!! :o

View 2 Replies View Related

Sql Select

Mar 24, 2005

I have a form with a textbox and a button, when I type a value in the textbox and click the button I want it to check a record in a table for a value if it is then, I will do step A if it is not there then I am going to do step B.

What is wrong with the code below place code in on click event of button:

View 2 Replies View Related

SELECT A,b FROM (SELECT X,y) ...

Sep 2, 2004

I want to create a query (b) from a first query (a) without saving the first one in the Access database. Here is the example, which does not work:

SELECT a.call_date, COUNT(a.extension) AS call_in
FROM (SELECT DISTINCT call_date, extension
FROM tblabsenteeism
WHERE call_date=#9/1/2004# AND type In ('FMLA','Personal','Sick')) a
GROUP BY a.call_date

Is it possible in Access to Select .. from (select...)?
Any insight is greately appreciated.

Thanks,
Dan

View 5 Replies View Related

Select Value

Sep 15, 2004

Hi all

I have a combo box, based on a query, with four category options. When the form opens I want the combo box to show the category previously entered for the item, based on the categoryid in a text box on the same form.

The user still needs to be able to change the category to one of the other options in the combo box, if they want.

Please help

View 1 Replies View Related

Need Help With Select

Jan 10, 2005

I have a table with the following data.

Code: uidWeight MaxWeight Description150 50Kg 260 60Kg370 70Kg480 80Kg590 90Kg6100100Kg7125125Kg8130130Kg+

When the user enter a weight of 131 or more I want to display the description of the maximum weight in the table, the same if the user enter a weight less than 50, then I want to display the minimum weight in the table. Any other weight and I want to display the description range of the weight => to the weight entered.

Anyone who can help me with the query?
Regards

View 4 Replies View Related

Help With Value AND Select

Jan 17, 2005

I have one form.In this form i have one field with name Count
(interger)
I want with VB code to give value in this field and the value is return from
Select count(*) ....

How i can do it???


Thanks

View 5 Replies View Related

Select..in[???]

Sep 3, 2005

Hello World!

I would like to know how is the syntax of the IN option of the SELECT statement. Iīm looking for this during a while and I didnīt find anything helpful!

Thanks and sorry for my beginer english!
Gleyson

View 1 Replies View Related

[sql][select]

Oct 17, 2005

Is this a valid statement please!

Code:mySQL = "SELECT * FROM tblWebContent WHERE lineOrder = '"& options &"'"

lineOrder is an Autonumber field!
options = 50

Run the above code, i get an error:

Quote: Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

View 2 Replies View Related

Using Select Case

Jul 24, 2005

Can I use a [system number] value stored in a table in a select case?

Select Case [system number] "Tble system numbers"

Case 42144
do this

Case 88754
do this

???

Jon

View 6 Replies View Related

Need A Little More Help With A Select Case

Jul 26, 2005

I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?


Dim dbs As DAO.Database
Dim rst AS DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")
While Not rst.EOF And Not rst.BOF
Select Case rst![System Number]
Case 42144
' do this
Case 88754
' do this
Case Else
' do this
End Select
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set dbs = Nothing

View 4 Replies View Related

SELECT Statement

Oct 27, 2005

I am trying to narrow down a list for a combobox by specifing one of the fields value on form as a acriteria
SELECT location.LOCNAME
FROM location;
WHERE (((location.LOCCLIINIT)=[location].[LOCCLIINIT]));

I get all list of locations' name instaed of location names with client code as displayed n current form.

View 1 Replies View Related

How To Use Select And Update Together

Feb 27, 2006

I have 2 tables with the following fields.

OrderInfo
PO Number
Status

CustomerInfo
Purchase Order Number
Customer Name
Customer Phone
Order Status

This is what I am trying to do:

get the Status for a the PO Numbers from the first table and update the Order Status field for those same PO Numbers in the second table.

This is how I am doing it currently:

I created a select query and saved it as ExistingPO. This is what ExistingPO looks like.

SELECT OrderInfo.*
FROM CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.[Purchase Order Number] = OrderInfo.[PO Number];

and then I created another query which makes use of this first select query. The second query looks like this

UPDATE CustomerInfo INNER JOIN ExistingPO ON CustomerInfo.[Purchase Order Number] = ExistingPO.[PO Number] SET CustomerInfo.[Order Status] = ExistingPO.Status;

But I was wondering is there was a way to combine the first query and the second query into one query?

View 1 Replies View Related

Listbox Select ALL

Mar 29, 2006

I have the following code where C is the listbox control

wCount = C.ListCount
Screen.ActiveForm.Painting = False
For wCounter = 0 To wCount
.Selected(wCounter) = True
Next
Screen.ActiveForm.Painting = True

This works fine - but is slow if a listbox has lots of records.

Is there a quicker way to select all items in a listbox?

(Both simple and extended - maybe this occasion will never happen that someone wants to select so many items that speed becaomes a factor, but I would like to have this covered just in case)

Thanks - Paul

View 2 Replies View Related

Select Query

Jun 9, 2006

I wondered if someone could help

I am querying a table using a select query. The data I am pulling from the table is in format:


Period Year Value

0 2006 1000
1 2006 100000
2 2006 500
3 2006 5000
4 2006 50000
5 2006 200000


In my query, I wish to seperate:

Period 0 + value

from

Periods 1-5 which should be cumulative.

Thanks

Paul

View 3 Replies View Related







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