|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
 |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
Free Web 2.0 Code Generator! Generate data entry and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!
|
|
#1
May 6th, 2008, 05:47 PM
|
|||
|
|||
General - MAX Command In MS Access
I have a master table (Table 1) which contains 600 products, and their daily price for the whole year.
ie a 600 x 365 = 219,000 row table (approx). Prices may rise or fall daily, depending on competition. I also have a second table (Table 2) that lists all the 600 products. Is it easy to 'make a query' that will list each product (from table 2), but with each maximum price (from Table 1) ? ie I want a query 600 rows long, that shows the maximum price reached, for each product. Making tables is ok, and making queries is ok - it's the MAX command I'm not sure about. Thanks.
|
|
#2
May 6th, 2008, 06:21 PM
|
||||
|
||||
|
So your tables look essentially like this?
Code:
Table 1: ProdCode ProdDate ProdPrice Table 2: ProdCode ProdDescription So that you can link the product information in Table 2 to all the entries in Table 1? We would ordinarily refer to Table 2 as the "master table", but that's not important. Sure, you would just use a "Totals Query" (use the "Totals" icon on the toolbar in Design View of the query, that looks like a Sigma) and use the Max function on the Total line. You could also do it as an SQL statement, using GROUP BY and the MAX() function.
__________________
Experience is the thing you have left when everything else is gone. Last edited by don94403 : May 6th, 2008 at 06:23 PM. Reason: I thought I could reproduce a Sigma, but I can't! Looks okay in the editor, but not when posted.
|
|
#3
May 6th, 2008, 10:29 PM
|
|||
|
|||
|
This seems to work provided I have only 2 columns, being [Product Code] and [Price].
But if I try this with 4 columns, it wont work. The 4 being [Prod Code] [Prod Desc] [Price] [Date]. Would this be right ? (I see what you mean about the master table, ooops).
|
|
#4
May 7th, 2008, 08:21 AM
|
|||
|
|||
|
To get what you're after I think you'll have to do it in a 2 step process the first query to find the highest price for each product code. The second query to find the date(s) that the product had the maximum price.
Let's say you have the following table: Code:
ProdCode ProdDate ProdPrice 1 1/1/2008 1.00 1 1/2/2008 1.05 1 1/3/2008 1.03 If you try to find the date on which the maximum price was in effect and do it in one step you're going to get a query result that looks like this Code:
ProdCode ProdDate ProdPrice 1 1/3/2008 1.05 Because it's finding the maximum value for each column rather than the value associated with the maximum value of one column. You could probably do it with a DMAX function but it would only return one value as opposed to all the days that the maximum price was in effect. Now, if you just care about the most recent date the maximum price was in effect you can use the following: Code:
SELECT Tbl_ProdPrices.ProdCode, Max(Tbl_ProdPrices.ProdPrice) AS MaxPrice, DMax("[ProdDate]","Tbl_ProdPrices","[ProdPrice] = " & Max([prodprice]) & " ") AS DateofPrice
FROM Tbl_ProdPrices
GROUP BY Tbl_ProdPrices.ProdCode;
Assuming that you use the example that don provided and name the table Tbl_ProdPrices.
__________________
---------------- If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!
|
|
#5
May 8th, 2008, 11:07 PM
|
|||
|
|||
|
Mmmm.
I've set up a smaller database, with the layouts suggested above. Only five products with 5 dates each... one rising (/) one falling (\) one flat (-) one peaking (^) one troughing (v) I pasted in the query (as all tables/fields have now got the same names). The query works in that it picks up correctly the product code with the correct highest price. But it incorrecly picks up the dates, in fact the date is always the last date regardless of when the high point was. (The 'rising' product did had it's high price on the last day). I can probaby continue without needing the date, although that would be nice. What are your thoughts, guys. Easy to do, or one for the 'too hard today' basket ? I dont mind either way.
|
|
#6
May 9th, 2008, 08:04 AM
|
|||
|
|||
|
Code:
SELECT Tbl_ProdPrices.ProdCode, Max(Tbl_ProdPrices.ProdPrice) AS MaxPrice, DMax("[ProdDate]","Tbl_ProdPrices","[ProdPrice] = " & Max([prodprice]) & " ") AS DateofMaxPrice, Min(Tbl_ProdPrices.ProdPrice) AS MinPrice, DMax("[ProdDate]","Tbl_ProdPrices","[ProdPrice] = " & Min([prodprice]) & " ") AS DateofMinPrice
FROM Tbl_ProdPrices
GROUP BY Tbl_ProdPrices.ProdCode;
This is the query I used with this dataset: Code:
ProdCode ProdDate ProdPrice ID 1 1/1/2008 $1.00 1 1 1/2/2008 $1.05 2 1 1/3/2008 $1.02 3 2 1/4/2008 $5.00 4 2 1/5/2008 $4.50 5 2 1/6/2008 $4.75 6 and I got this result: Code:
ProdCode MaxPrice DateofMaxPrice MinPrice DateofMinPrice 1 $1.05 1/2/2008 $1.00 1/1/2008 2 $5.00 1/4/2008 $4.50 1/5/2008 Which, as far as I can tell, is what you wanted for the max and min. The only assumption I made is that you wanted to show the most recent date where the maximum or minimum price was in effect.
|
|
#7
May 11th, 2008, 05:24 PM
|
|||
|
|||
quote "The only assumption I made is that you wanted to show the most recent date where the maximum or minimum price was in effect."
Yes - that was a correct assumption, thanks. I'll retry with the above data today (Mon). Im still learning MS Access, so I'm sure the problem is at my end, somewhere. Thanks everyone, for your help so far. Last edited by velohead : May 11th, 2008 at 05:25 PM. Reason: spelling
|
|
| Viewing: ASP Free Forums > Database > Microsoft Access Help > General - MAX Command In MS Access |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
