MS Access Query Min for a specific month

[30percent]

Hi,


I'm new to MS Access


I have a table with the following columns: Date, Open Price, Close Price, High Price, and Low Price.


I'm trying to query the minimum Low Price for records >#12/01/2015#


On my query, I would like it to show the corresponding row for Date, Open Price, Close Price and High Price as well.


Wonder if someone could explain to me how to do that?

Thank you!

 

[Micron]

Create a query that gets the Min([Low Price]) where the Date is >#12/01/2015#. Go to sql view and copy the sql statement.
Create another query that uses the other fields you want, and in the Low Price field of the query design grid, paste your select statement from the other query. This statement has to be enclosed in brackets () and does not need the ending semicolon you probably brought over. That is the basic idea of how to construct a subquery statement for a query, but it's off the top of my head. Hopefully I have described it accurately enough.

This technique will likely prove useful in the future, so you should research the subject. Here is a good source that explains the concept
Microsoft Access tips: Subquery basics

 

[30percent]

Hi,


thank you for your direction!


I wanted to select the minimum of [Low Price] from #12/01/2015# < MYTABLE.DATE < #12/15/2015# showing the following fields Date, [Open Price], [Close Price], [High Price], and [Low Price].


This is what I have:


SELECT DATE, [Open Price], [Close Price], [High Price], [Low Price]
FROM MYTABLE
WHERE (((MYTABLE.DATE)>#12/1/2015# And (MYTABLE.DATE)<#12/15/2015#));


However, I couldn't figure out how to incorporate the criteria Min value from [Low Price]

I tried the following but it wouldn't work. I changed to Min([Low Price]).


SELECT DATE, [Open Price], [Close Price], [High Price], Min([Low Price])
FROM MYTABLE
WHERE (((MYTABLE.DATE)>#12/1/2015# And (MYTABLE.DATE)<#12/15/2015#));

 

[Micron]

Did you review the link? If you were attempting to write a subquery as suggested, I ought to see the word SELECT in there two times but I don't.

 

[30percent]

Hi Micro,


thank you for your help!


I tried this:


SELECT DATE, [Open Price], [Close Price], [High Price], [Low Price]
FROM MYTABLE
WHERE (SELECT MIN([Low Price]) FROM MYTABLE
WHERE (((MYTABLE.DATE)>#12/1/2015# AND (MYTABLE.DATE)<#12/15/2015#));


It ran but did not pull up MIN for [LOW PRICE] for the columns requested between #12/1/2015# AND #12/15/2015#. Basically, it just pulls up all the data for the 5 columns beyond the date interval requested.


And I was trying things around, if I take the Min() off, I got an error message "At most one record can be returned by this subquery".


I was able to bring up MIN[LOW PRICE] for the interval #12/1/2015# AND #12/15/2015#using just the subquery, but I'm not able to bring up the other columns. If I add the other columns to the subquery, I got the error message that "Your query does not include the specified expression 'DATE' as part of an aggregate function."


Subquery only works but I need to include other columns:


SELECT MIN([Low Price]) FROM MYTABLE
WHERE (((MYTABLE.DATE)>#12/1/2015# AND (MYTABLE.DATE)<#12/15/2015#))

 

[Micron]

If you look at the link again, you will see that for the SELECT example (not the WHERE NOT EXISTS example), the designer has used aliases in the subquery statement for the table AND the value (as a field) being returned. See MeterReading AS Dupe... If asked, I would have said that this was required for any subquery statement, but I guess not. I do not see where you aliased the table or field names that are common to both parts of your query, and I'm pretty sure the FROM part for the main query has to be at the end, which you have not done either. Note that the example does not use a semicolon for the subquery either. Not sure if it doesn't matter - I leave it out as a matter of course. Maybe something like

SELECT MYTABLE.[DATE], MYTABLE.[Open Price], MYTABLE.[Close Price], 
MYTABLE.[High Price], MYTABLE.[Low Price],
  (SELECT MIN Dupe.[Low Price]) FROM MYTABLE as Dupe WHERE 
   Dupe.[DATE]>#12/1/2015# AND Dupe.[DATE]<#12/15/2015#) AS MinDate 
FROM MYTABLE;

The example also uses the complete table.field reference whereas you have not. Again, for this type of query, it is what I'd do. So you have a lot of variances between the example and what you tried. Not sure if I picked the best version of yours to tweak as you've posted a few, but I think you'll get the idea if my example doesn't work.

Last but certainly not least, Date should NEVER be used for a field/table/object name. It's getting to be so repetitive for me to say this that I've finally bookmarked the page I send everyone to!
Microsoft Access tips: Problem names and reserved words in Access

You should also read up on naming conventions while you're at it. Here's one (but I don't follow it religiously)
General: Commonly used naming conventions

I'll use frm, ctl, tbl, qry, rpt, cmd, sfrm, txt, lst, opt, lng, int, dbl, lbl, cmb, chk, img, str, var etc. without worrying about sub-groups for objects like queries.. To each their own; just adopt something and be consistent. Just NO spaces or special characters (save for underscore, which I never use) opting instead for lowerUpperCase style of naming.

 

[xenou]

Just to add on, your query from post 5 does not even have the correct number of parentheses and should not even run at all. Plus it makes no sort of comparison to the fields you are selecting.

SELECT DATE, [Open Price], [Close Price], [High Price], [Low Price]
FROM MYTABLE
WHERE (SELECT MIN([Low Price]) FROM MYTABLE
WHERE (((MYTABLE.DATE)>#12/1/2015# AND (MYTABLE.DATE)<#12/15/2015#));

What you need is more like:

SELECT
	[DATE], [Open Price], [Close Price], [High Price], [Low Price]
FROM 
	MYTABLE T1
WHERE [Low Price] = 
			(
				SELECT MIN([Low Price]) FROM MYTABLE
				WHERE (((MYTABLE.DATE)>#12/1/2015# AND (MYTABLE.DATE)<#12/15/2015#))
			)

It's still confusing what you want here. Can you provide some sample data and hoped-for results? This query may not return anything more than the same record you'd get without a subquery, depending on what your data looks like. Or it might find two records with the same low price mark. Somewhat strange. What is the reason for the query? What are you trying to discover from this data? Do you have data for only one stock symbol and where are the symbols, otherwise?

Note, Agree with Micron don't use Date as a field name, as a matter of course. But if you do then ALWAYS enclose it in brackets:
SELECT [Date] From ...