Min/Max query 1

[dds82]

I have a table whose only input is going to be dates. Is there a way to find out the earliest and latest dates in the entire table, not just in a single column?

Thanks.

 

[scriverb]

A union query would do this for you. The number of Selects in this union query would be equal to the number of date fields in your record.

Example: Table Name - Table1: Date1, Date2, Date3, Date4, Date5, . . . DateN

Select Min(Table1.Date1) as EarliestDate, Max(Table1.Date1) as LatestDate
From Table1

UNION Select Min(Table1.Date2) as EarliestDate, Max(Table1.Date2) as LatestDate
From Table1

UNION Select Min(Table1.Date3) as EarliestDate, Max(Table1.Date3) as LatestDate
From Table1

UNION Select Min(Table1.Date4) as EarliestDate, Max(Table1.Date4) as LatestDate
From Table1

UNION Select Min(Table1.Date5) as EarliestDate, Max(Table1.Date5) as LatestDate
From Table1;

To Create a UNION Query in Design Mode click the Queries tab and Click New. Close the Tables selection window. From the Query Menu select SQL Specific and then select UNION. This is where you copy and paste the above code to create the UNION SQL. You will have to modify the code to reflect your table and your fields. You can continue to add additional UNION Selects... based upon the number of dates in your record.

Good luck and let me know if this is what you were looking for. Bob Scriver

 

[dds82]

Sort of...

I've tried that query already; it gives me a list of the early and late dates in each column. I need a query to pull the earliest and latest date out of that list generated by this query.

Thanks.

 

[scriverb]

That is quite easy. Save the first query and name it qryUNIONMinMaxDates. Create another query with the following SQL:

SELECT Min(qryUNIONMinMaxDates.EarliestDate) AS MinOfEarliestDate, Max(qryUNIONMinMaxDates.LatestDate) AS MaxOfLatestDate
FROM qryUNIONMinMaxDates;

After you change the first query to match your table and fieldnames then run this query and you will receive two columns but only one row of the Min Date and Max Date for the database.

Try it.
Bob Scriver

 

[dds82]

Yes, in that situation it is quite easy.

The problem is that creating the query in the database isn't an option. I need to be able to generate SQL dynamically that does the same thing as what you just suggested.

Thanks.

 

[scriverb]

I just I don't understand the need to do this dynamically within VBA code. You table and field names are stable correct? The data is entered. Now run the query at anytime to obtain the two date values.

What could be happening to require this code to be generated dynamically. I have done this kind of thing many times but from your question I don't understand the situation yet.

Please advise in detail. Bob Scriver

 

[dds82]

It's not a VBA program. ;-)

I'm using C++ to interface with Excel spreadsheets via the Jet ODBC drivers. Since I can't save a query into an Excel spreadsheet, I need a way to do this all dynamically. The column names are unknown until run time, as are the number of columns.

So...

Assuming I know the number of columns and their names, how can I do this without saving a query into the database?

Thanks.

 

[scriverb]

If you can pass the number of columns and the column names through an attached Excel spreadsheet this can be done dynamically. But, how do you expect to trigger this process? I can write the VBA code in a database Class module and can connect to the spreadsheet through a predetermined path and file name but how do you expect to trigger the code?

Let me know on this as well as the info that I need as listed above. Bob Scriver

 

[dds82]

Forget anything about VBA and how the code is triggered; I have that worked out already. (There's no VBA involved in this at all.) With an ADO recordset, you can get the number of columns in the spreadsheet and their names. There's no Access database involved either. An Excel spreadsheet is just as good a data source as an Access database. I just want to know how to write the SQL to get the data I need.

 

[scriverb]

Okay I get it. Give me a little time and I will give you some examples. You can then take it from there. Bob Scriver

 

[scriverb]

I have done this all within ACCESS only, not from outside of ACCESS but you seem to know what you are doing in that regard. As I am not now using ADO but only DAO I will give you how I would do it from within ACCESS with DAO and you can modify it to do it with ADO.

After you have dimensioned and set up your db and rs variables to access the Excel spreadsheet perform the following looping function.
Dim vSQL as string
vDataFields = # of columns
vFieldName = Name of fields
vTableName = Name of table or spreadsheet
rs.movefirst
vFieldName = rs("FieldName&quot
vSQL = vSQL + "UNION Select Min('" & vTableName & "." & vFieldName & "') as EarliestDate, Max('" & vTableName & "." & vFieldName & "') as LatestDate FROM " & vTableName & " "
rs.movenext

Do
vSQL = vSQL + "UNION Select Min('" & vTableName & "." & vFieldName & "') as EarliestDate, Max('" & vTableName & "." & vFieldName & "') as LatestDate FROM " & vTableName & " "

rs.movenext
Loop Until rs.eof

vSQL = vSQL & ";"


vSQL now has the code to be run to create the first recordset of the EarliestDate and LatestDate for each record.

If you need more on this get back with me.






Bob Scriver

 

[dds82]

As I understand it, this VB code does exactly the same thing as the first reply you posted. I need to be able to select the MAX and MIN values from that query. I haven't been able to do that without actually saving the query into the database, which I'm not going to be able to do.

This isn't legal SQL code (at least not the way I've tried it ), but it might give you a better idea of what I'm trying to do:

select min, max from (
    select (
        entire union select query generated dynamically
    )
)

Can this be done?

Thanks.

 

[MichaelRed]

Hmmmmmmmmm,

It is a sure bet that I'm out of this league. To much going on about where what is when stuff. Stillm there are a couple of procedures floating in the lost pool of previous posts, basNinVal ans basMaxVal which are intended to get the (what else?) Min and Max (respectively) of an arbitrary array. Using these, you coule add the calculated columns RowMax(the date fields) and the corrseponding RowMin. A query for the Min and Max of these fields would be the Min and max of the recordset.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[cmmrfrds]

The problem can be solved with a derived query. The derived part would be all the unions that you have already built. I have an example of a similar simple problem which I believe was submitted by Terry. Just put the union queries in the From clause.

Basic structure of the query.

Select
Min(MinA) As MinVal,
Max(MinA) As MaxVal
From (
(SELECT col1, min(col2) As MinA
FROM Tab1
WHERE col1 IN (1,2,3)
GROUP BY col1) As Qry

 

[scriverb]

I don't believe you can do what you want to do. I have tried the different combinations that work in our RDBC such as:

Select Min(EarliestDate), Max(LatestDate
FROM ( Select . . . UNION QUERY STUFF) T1;

and this doesn't work. The T1 is a Temp Table name for the Union and works in other SQL but not in ACCESS SQL.

Also tried performing the Min and Max on the AS named field in each of the Selects in the UNION with no luck.

If MichaelRed's suggestion doesn't work then the only possibility I believe is to fashion a way to use the stored ACCESS queries in an ACCESS database to do what you want to do. I realize that you don't want to do this but if you created an ACCESS database just for the purpose of running these queries and then updating an external table where you could then read the resuting two fields you would have achieved your goal.

Using the code provided have two queries in the database one. The UNION queries SQL property could be updated through code as was suggested. Then open the database where the AutoExec could perform an export of the results of the second query provided that gives you your two dates. That export could be directed to any location that you wanted and you could then read the results.

Just an idea. Bob Scriver

 

[cmmrfrds]

Hi Bob. I had trouble with a derived query in Access when it went through the query builder (Access changed it), but when using raw SQL it worked okay on the Access MDB. Did you run your test through the query builder? In this problem the query builder does not need to be involved.

 

[scriverb]

cmmrfrds: I didn't use the ACCESS design wizard. I wrote the SQL as follows but ACCESS still doesn't like it:
Select Min(EarliestDate) as MinDate, Max(LatestDate) as MaxDate
FROM
(Select Min(Table2.Date1) as EarliestDate, Max(Table2.Date1) as LatestDate
From Table2 UNION Select Min(Table2.Date2) as EarliestDate, Max(Table2.Date2) as LatestDate
From Table2 UNION Select Min(Table2.Date3) as EarliestDate, Max(Table2.Date3) as LatestDate
From Table2 UNION Select Min(Table2.Date4) as EarliestDate, Max(Table2.Date4) as LatestDate
From Table2) AS T1;

The problem is with the FROM clause. ACCESS requires it to be a table, a saved query, or a compound resulting from a JOIN statement.

Like I said before ACCESS just will not accept a temporary recordset(UNION SQL) as the identified "table?" in the FROM statement. Result: Syntax error in FROM statement Bob Scriver

 

[cmmrfrds]

Bob, I made a table to match your sql statement. I added some test data and then pasted your query into the sql view, by the way, nice job. It ran and returned the correct dates. I am going to give you a star for going through the effort of setting up the query and checking it out.

I am using Access 2000.

 

[scriverb]

cmmrfrds: Thanks for that star. I believe that a workaround to dds82 could be accomplished but we would have to have more information about the files and whether he was willing to actually open a copy of the database and have a module perform everything necessary from with that ACCESS database.

We will see if he responds.
Bob Scriver

 

[dds82]

Well, thanks for your help. I've made my own workaround for it. I do the processing I need outside the database, after I've retrieved all the data (which I need anyway).

But, if you want to puzzle over it for fun, if it were possible to go the SQL way, I wouldn't be using ACCESS at all. The format of the files is such that they're equivalent to an ACCESS table whose columns are formatted for DATE/TIME, except the number of columns is unknown until run-time.