Find Lowest Value In ID Field?

Feb 2, 2008

I'm using a filter on the form load of one of my forms that potentially would have 3000 records. The idea is to speed the process up a little.

I'm using:

If IsNull(Forms!frmProjectMain!ProjectID) = False Then
DoCmd.GoToRecord , , acFirst
end if
Me.FilterOn = True

Which works fine. However, I'd rather it was the ID with the lowest numeric value that was displayed and filtered rather than the last record chosen. i.e. 1 instead of a random number.

Does anyone know how i'd go about writing code that would tell access to go to the lowest ID to filter?

thanks for any help I receive here

View Replies

Find The Lowest Number And Place The Number And Column Name Into In Field

Dec 15, 2005

I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2

ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4

Where the new column name contains the MIN number in the row and all of the associated duplicates column names.

View 1 Replies View Related

Query For Lowest 2nd Lowest And 3rd Lowest Value

May 24, 2006

Hello there...

I have a data set as follows:

Product CompanyA CompanyB CompanyC .... CompanyZ

aaa 2.3 2.4 2.5
bbb 3.4 - 3.1

and so on...

The value represents the price of products...

I want to make a query that will show me results in following heads..

Product MinimumPrice CompanyName
aaa 2.3 CompanyA
bbb 3.1 CompanyC

How can I proceed??

Looking for some help

Regards,

View 1 Replies View Related

Return Lowest Value Only?

Jan 16, 2008

hi,

I have a table that contains itemcodes and prices. An example of the records would be:

<Item Code> <Price>
Product001 �34.56
Product001 �49.23
Product001 �23.22
Product001 �98.43
Product002 �12.45
Product002 �54.34
Product002 �25.51
Product002 �76.84
Product002 �14.97

I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:

<Item Code> <Price>
Product001 �23.22
Product002 �12.45

Does anyone know the criteria code in the query design view for this problem?

I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.

Thank you for your help.

Dean

View 3 Replies View Related

Finding The Lowest Average

Oct 24, 2006

View 6 Replies View Related

Only Show Lowest Cust ID Per Account

Nov 3, 2006

Hello,

I have searched through the forum, but cannot find a suitable answer to my question, so here I am.

I wonder if you could help me?

I have a list of Customers each of which has a Customer ID then I have a list of accounts which may have more than one customer ID.

For this senerio I would like to only show the record that has the lowest customer ID.

I.E. If one account has two customers take the lowest customer ID.

Does anyone know how to do this in SQL or VBA?

Thanks for all your help.

View 2 Replies View Related

Selecting Lowest Values In A Query?

Feb 23, 2006

Hi all - I have a table that contains Client ID, Supplier Name, Order Number, Order Date, and Price.

Example -

Client ID -- Supplier Name -- Order Number -- Order Date -- Price

1111111 Supp A 222222 1/2/06 1,000
1111111 Supp B 222222 1/2/06 2,000
1111111 Supp C 222222 1/2/06 4,000
1111111 Supp D 222222 1/2/06 500
1111111 Supp E 222222 1/2/06 1,200

I need to determine the 2 lowest prices per order number. In this case that would be Supp D - 500, and Supp A - 1000.

I am using the Top function and sorting the price in ascending order to get the lowest two prices. The problem is that it selecting the lowest 2 prices of all the orders on the table, not each individual order. I tried grouping on the other fields, but still came up with incorrect results.

Any ideas? Thanks!

B

View 3 Replies View Related

Filtering For Lowest Date Out Of Duplicate Records

Mar 13, 2008

I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.

View 4 Replies View Related

Forms :: Selectively Update One Value In A Table By Lowest Date

Mar 17, 2014

I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.

The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.

Summary:
One "Game"
Several "GameCopy" using "Game" as a foreign key
Reservation table with possibly several reservations for the same Game - not GameCopy(s)

In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.

Would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.

Obviously if I simply:

UPDATE Reservation SET Reservation.Available = Date()
WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;

...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.

View 5 Replies View Related

Merging 2 Queries Of Same Data To Get Lowest Price And Matching Items

Sep 4, 2006

I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.

View 3 Replies View Related

Cannot Find Field Name, Pls Pls Help

Jun 14, 2006

I am desinging a report, I cannot see some fileds in Sorting and grouping. The query that I am runing has those fieds, How can I get to see all the fileds in sorting and grouping?

Thanks

View 1 Replies View Related

Trying To Find A Field

Apr 26, 2005

When opening a form i get asked for a parameter value for a field that has been deleted. it obvioulsy still exists somewhere but I cannot find it. i have analysed performance, all tables, forms and queries but it doesn't come up with this filed anywhere. the field name has even been spelt wrong so i thought it would be easy to track! Help - what else can i do to find where this parameter is being called from??

View 4 Replies View Related

General :: Deduct Quantities Based On Date - Start From Lowest Date

Sep 9, 2014

I have attached a sample of a database.

Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10

For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.

View 8 Replies View Related

Queries :: Find Field Name Not Field Value?

Mar 11, 2014

I'm trying to convince Access to find a value and then convert said value into the name of the field it came from.

Below is my current code:

Code:
LowestMark:
Minimum([AvgOfOpening1],[AvgOfConfidentiality1],[AvgOfEmail1],[AvgOfTenureStatement1],[AvgOfOwnership1],[AvgOfClearExplanation],[AvgOfProbing1],[AvgOfUseofTools1],[AvgOfResolveBeforeS2S1],[AvgOfBridgeSolutions],[AvgOfSummarizeWrapUp1],[AvgOfOfferFurtherAssistance1],[AvgOfOneFaceOneVoice1],[AvgOfAppreciateBrandCox1],[AvgOfInteractwithCustomer1])

(Minimum is from a module that finds the lowest value in the list.)

I need my query to tell me which field gives that value.

Example:

Agent Name AvgOfEmail1
instead of:
Agent Name 41.7%

how to make this work?

View 6 Replies View Related

How Do You Find The Most Common Value In A Field

Mar 27, 2008

I am OK with a number field as I can put it in Excel and use =Mode().

But that wont do text such as lastname, suburb etc. I can do suburb by doing postcode but no such equivalent from some others I want to do

View 4 Replies View Related

How To Find Out All The Tables Contain A Particular Field

Jul 3, 2006

i want to find out all the tables that contain a particular field. eg.employee_ID exists in employee table and other tables, how i can find out what are the other tables? can i run a search query to do this?

View 2 Replies View Related

Find A Field In A Form

Aug 28, 2006

I am trying to locate a field in the form. The field is existing in the Tab Order list. But when I use the tab to locate the field in design or run mod, the tab never jump to that particular field.

Can anyone tell me how I can locate the field (in order to change the properties of this field)?

View 1 Replies View Related

Modules & VBA :: Using LIKE In A Field To Find A Value

Mar 16, 2015

I'm lost on how to use the LIKE to find a value in a field.

I try to use the open folder if a field contains "Retail" or any other containing as a part. So, I could have just "Retail" "New Retail" and then act on it.

How can I use the below proper?

Code:
If Me.Business_Type Like "*" & "Retail" Then

View 4 Replies View Related

Find All Queries (or Reports) Using A Particular Field?

Jul 21, 2005

I've inherited a database with many queries (most based on a single table). There is a requirement to change the name of a few fields in the table. Is there a way to find ALL queries/reports that are utilizing the fields that need to be changed so I know what queries need to be modified?

View 3 Replies View Related

Find First Word In Field From Query

Feb 15, 2007

Can anyone help me in creating a field from a query which will pull the first word only from a specific field.

Thanks.

View 2 Replies View Related

Date Field Find Today

Aug 21, 2007

I have a date/time format field.

"8/21/2007 10:02:34 PM" is what my column shows.

What would I add to the criteria to the query to find all records added today?

at the end of the day my user needs to run a query that will show everything from today. Thanks.

View 5 Replies View Related

Find A City In The Middle Of A Field

Jan 11, 2008

Ok Im having trouble with this, I need to remove cities from an address field but don't know a simple query that does this. Can any body help

heres an example

Address1 City
10/F, HONG KONG LAI CHI KOK EXCHANGE IIHONG KONG

Now I want to be able to pull Hong kong out of the address1 field but without searching in the address field with *HONG KONG* as this would involve checking every city in a table.

Please help!

View 1 Replies View Related

Find Record And Update A Field

Jan 29, 2014

Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Complaint", dbOpenDynaset)

[Code] ....

I find the record, and then try to update a field. The drop down menu field gets updated after it is initially changed from blank to a value, but it never changes after that though the re!C02 field has the correct newer data when I change it again.

View 14 Replies View Related

Find Table And Field For A Form Control

Jan 7, 2008

Given the firmname and textbox name is it possible to programmically get the fieldname and table name where the data for that control is held.

I can use .controlsource and .recordsource

But is possible that .controlsouce is an alias of the actual fieldname.

Similarly the recordsource could be a query, from that I want to get the actual table, complicated say if two tables in the query had a field with the same name (even if only one was referenced)

Thanks.

View 3 Replies View Related

Find Duplicates That Don't Match Single Field

Jun 5, 2007

Hello all,

I am sure this must be simple, but I can't get my head around it.

I have a query that looks at client bank account numbers, and finds duplicates.

There are a LOT of duplicates, because we have several accounts for the same person.

What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.

I am trying to find any records where the same account number is being used on more than one account.

All the fields are in a single table.

This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;

Thank you!

View 1 Replies View Related

Find Records With A Duplicate Field Entry

Jul 24, 2007

Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)

Bank Account # "=" Fund #

For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that

Bank Account #.Currency = Fund #.General Ledger #

Sorry, if that's kinda cryptic, but here's an example:

Bank Account - ABCD123
Currencies operated in - CAD, USD
Fund # - F30

Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.

Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.

Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).

Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.

View 2 Replies View Related