Is it possible to get the max values from the columns in 1 table and copy them into another or would this not be recommended. The more I think about it the more I begin to think it isn't such a good idea.

Here's an example what I'm looking for:

TEST_TABLE
--------------------------------------
NAME     AGE     SCORE     LAPS
Devin     22      202       16
Brian     36      185        8
Heather   18      236       12

Is there a series of queries I can write to get me the max values found in each column? The rows are irrelevant as the max values will be used for formatting in a form.

I want the end table to look something like this:

MAX_TABLE
--------------------------------------
MAX_AGE     MAX_SCORE     MAX_LAPS
   36          236           16

Or would it be simpler to write some sort of query in the form for this? Is that even possible? I'm using Access 2007.

Why doesn't this query work?

INSERT INTO MAX_TABLE (MAX_AGE, MAX_SCORE, MAX_LAPS)
SELECT MAX(AGE), MAX(SCORE), MAX(LAPS)
FROM TEST_TABLE;

I get a wonderfully helpful error stating:
"Reserved error (-3087); there is no message for this error."

I got it to work. Figured out how to get the query builder to do it for me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.