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 Replies

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

Finding The Lowest Average

Oct 24, 2006

View 6 Replies View Related

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 5 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

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

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

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

Return Criteria Or Return All

May 10, 2007

Hi:

Using MS Access 2000.

Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.

criteria: [Enter date - mm/dd] Or >#1/1/2001#

When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.

I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.

Any help is appreciated.
:confused:

View 5 Replies View Related

Return Zero Value

Mar 28, 2007

Hi,

In a query, I am using the following code to return the average length of each tenancy. This works fine unless the length is 0. If so, it only brings back 'blank'. How can I change this code to return a 0 value?

Tenure Length AVG: IIf([Average Tenure Length]>0,Format([Average Tenure Length],"Fixed",Format(0,"Fixed")))

Thanks in advance

Lee

View 2 Replies View Related

How To Do A Carriage Return

Oct 4, 2005

Hi guys

Im trying to set my label to a string.

Something like

myLabel.caption = StringOne & chr(13) & StringTwo

The problem is I just get funny symbols instead of the carriage return.

Please help

Thank you in advance

View 2 Replies View Related

Carrage Return

Mar 22, 2008

Hi There,

Can anyone tell me how to send a Carrage Return in Access? When I try to use the 'SendKeys with{Enter}', Access starts to run in an endless loop that seems to be some Wizard. All I want to do is go to a new line within the same text field.

Can any one help me? I'm an amature at Access and VBA.

View 7 Replies View Related

Return To Null

Apr 24, 2006

Hi all

i would like to know if there is a way to return a field to null

thanks in advance

View 2 Replies View Related

Return To Null

Apr 24, 2006

Hi all

i would like to know if there is a way to return a field to null

thanks in advance

View 1 Replies View Related

Serach - Return

Oct 17, 2006

I am creating a database for a hyperthetical car hire company. I have created three tables, customer table, car table and bookng table. The customer table is linked to the booking table using a Customer ID (autonumber). The car table is linked to the booking table using NumberPlate. The primary key for the booking table is Booking ID.

The cars are catagorised in different groups e.g. hatchback, sports car etc. The customer will choose a car type which is entered into the booking table. They will also enter the dates for which they want to hire their car.

I want my database to take these dates and the type of car, search through the car table and return a list of cars of that type which are availiable on these dates.

Can this be done???

View 1 Replies View Related

Expression? Return 1 Of Many

Aug 19, 2005

When running a query, I want to hide duplicates records for a given field. PO# will have many duplicates, depending on how many part numbers were order on the same PO#. Other unique fields are not displayed. Can an expression help with this? If not, how do I go about hiding them in a query?

Current Query results:

DATE PO#

08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD

Desired Query results:

08/18/2005 ABCD

Thanks in advance with any help!

View 2 Replies View Related

Null Value To Return 0

Dec 12, 2005

When the SSN field is null I would like this query to return : 000000000, is this possible and if so could you show me how? Thanks

SELECT
[CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID AS [IVA #],
[CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN AS SSN,
Sum([CASEHEADS RECEIVING NON WELFARE MONEY].AMT_DISBURSE) AS AMT,
[CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE AS [DATE] INTO [CASEHEADS RECEIVING NON WELFARE MONEY TBL2]

FROM [CASEHEADS RECEIVING NON WELFARE MONEY]

GROUP BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID, [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE
ORDER BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID;

View 3 Replies View Related

Getting Count To Return Zero

Jul 20, 2006

Is there a way while using the count query to make it return zero when using the criteria (not like "N") instead of a blank datasheet.

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

Return Greatest Value

Oct 31, 2006

I have a table that has multiple Account Numbers and their Balances, I need to only pull the records that have the largest value.

Example: Acct# Balance
2 $1.00
2 $500.00
2 $300.00
3 $25.00
3 $400.00

I need my query to pull only these records Acct # Balance
2 $500.00
3 $400.00

Please help me figure out how to only pull those records

View 1 Replies View Related

Return Different Values......

Jan 29, 2007

Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)

I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?

I am sure this is fairly easy.......it has just got me stumped!!

Many Thanks.

Frank.

View 2 Replies View Related

Can Count Return A Zero?

Apr 10, 2008

I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows:
Jan 3
Feb 4
The other two vatagories however have no non-compliant records for January so they return as follows:
Feb 2
Feb 1
I wanted to tie the three queries together in the form for reporting:

Cat 1 Cat 2 Cat 3
Jan 3 0 0
Feb 4 2 1

Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.

View 2 Replies View Related