How to use another table fields as a criteria for MS Access

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.

Solution 1

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.

Solution 2

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.

Comments

[ParthShah]

Thanks for the solution sir. Just a small correction in it. instead of cdate it should be vdate in the query. However, still just a small problem. I am not able to see the respective "vdate" of that row item. I also need a field for that which show me on which date that minimum value was there. Thanks in advance.

[Don George]

Of course the correct variable is VDate - small typo when I set up the sample table. I'll post another answer with an approach to your 2nd question.

[Yawar]

There is definitely a join between the tables--it's a cross join with filtered results because of the where clause.