How to use another table fields as a criteria for

Question

This is the MS Access related query.

I have a table with three columns: FName, FValue and VDate in MS Access.(The actual table is quite big but the following example is for reference.) !http://postimg.org/image/bx0grwoa3/

Now I want to get a following kind of query output: Get the minimum quarterly values for each unique name. For example: !http://postimg.org/image/je1w7gdi1/

So far I am able to get the output for one quarter by hardcoded criteria, by using the following (In MS Access)

SQL string is:

SELECT Table.FName, Min(Table.FValue) AS MinOfFValue, First(Table.VDate) AS FirstOfVDate
FROM [Table] LEFT JOIN [Table] AS Table_1 ON Table.FValue = Table_1.FValue
WHERE (((Table.VDate)>#3/31/2014# And (Table.VDate)<#7/1/2014#))
GROUP BY Table.FName;

Now instead of the putting date hard coded, I want the dates to be part of a table where Quarter name, from date and to dates are there and Access takes them one by one and give the desired output.

Thanks in advance.

Answer 1

Make a new tables "quarters" with fields for Yr, Qtr, Start and End. Start and End are date/time fields:

Quarters
Yr      Qtr Start       End

2014    1   1/1/2014    3/31/2014
2014    2   4/1/2014    6/30/2014
2014    3   7/1/2014    10/31/2014

Then use this query:

SELECT Quarters.Yr, Quarters.Qtr, Table.FName, Min(Table.FValue) AS FValue
FROM [Table], Quarters
WHERE (((Table.CDate)>=[start] And (Table.CDate)<=[end]))
GROUP BY Quarters.Yr, Quarters.Qtr, Table.FName;

Note - there is no join between the two tables in the query.

Answer 2

The 2nd problem is a bit more difficult than the 1st. My approach would be to use 3 separate queries to get the answer:

Query1 returns a record for each record in the original table, adding the year and quarter from the quarters table. Note that instead of using the quarters table, you could just as easily calculate the year and quarter from the date.

SELECT Table.FName, Table.FValue, Table.VDate, Quarters.Yr, Quarters.Qtr
FROM [Table], Quarters
WHERE (((Table.VDate)>=[start] And (Table.VDate)<=[end]));

Query2 uses the results of Query1 and finds the minimum values you need:

SELECT Query1.FName, Query1.Yr, Query1.Qtr, Min(Query1.FValue) AS MinValue
FROM Query1
GROUP BY Query1.FName, Query1.Yr, Query1.Qtr;

Query3 matches the results of Query1 and Query2 to show the data on which the minimum value was reached. Note that I made this a Sum query and used First(VDate), assumining that the minimum value may have occurred more than once and you need only the 1st time it happened.

SELECT Query1.FName, Query1.Yr, Query1.Qtr, Query2.MinValue, First(Query1.VDate) AS MidDate, Query1.FValue
FROM Query1 INNER JOIN Query2 ON (Query1.Qtr = Query2.Qtr) AND (Query1.FValue = Query2.MinValue) AND (Query1.FName = Query2.FName)
GROUP BY Query1.FName, Query1.Yr, Query1.Qtr, Query2.MinValue, Query1.FValue;

There's probably a clever way to do this all in one query, but this is the way usually solve similar problems.