How do I return only the maximum value of one table?

[Guest]

I have a problem that is driving me nuts. I am pulling several fields from
each table, but am connecting on a common field. I am trying to return only
the maximum value from Table 1 and connect that to Table 2. I tried several
ways and I continue to get the max value from table 1 and all of table 2.

Any suggestions?

Example:

Table 1 (ID) Table 2 (ID)
33 33
38 38
37 37
45 45 <---- Return Only This Entry

 

[Shaun Beane]

You'll want to use the dmax() function. Sample syntax is:

dmax("id","table 2")

hope this helps.

 

[Guest]

Divide and conquer is the strategy here. Create a totals query that selects
the Max of your field, then create a second query that joins the first to the
two tables of interest:

SELECT FieldList1, FieldList2
FROM (Table1 INNER JOIN [Max] ON Table1.Key = Max.MaxOfID) INNER JOIN Table2
ON Max.MaxOfID = Table2.Key;

Hope that helps.
Sprinks

 

[Van T. Dinh]

Try:


SELECT T1.*, T2.*

FROM [Table1] AS T1 INNER JOIN
[Table2] AS T2 ON T1.[ID] = T2.[ID]

WHERE T1.[ID] =
(
SELECT Max(T1Sub.[ID])
FROM [Table1] AS T1Sub
)

 

[Guest]

Van Dinh's reply is getting me close, but it's giving me an error. First,
let me clarify my question:

Table1 (Name) Table1 (ID) Table2 (ID) Table2 (Color)
Ann 33 33 Red
Ann 38 38 Purple <--Return
John 37 37 Yellow
John 45 45 Green
John 50 50 Blue <---Return

(Return the color for the MaxId for each person)

The tables are in another system and linked. The error I'm getting is that
it can't find the Table in the same location as where the file is stored.
It's like it forgot where the tables were in the first place and is trying to
find them locally. I'm sure it has something to do now with the bracketry,
but I'm not sure. I am used to programming in WinSQL, but it is obviously
not as user friendly on the font end as Access.

 

[Van T. Dinh]

OK. Your description is clearer: you want the max *per person* in Table1
which was not mentioned in your original post?

Try:


SELECT T1.*, T2.*

FROM [Table1] AS T1 INNER JOIN
[Table2] AS T2 ON T1.[ID] = T2.[ID]

WHERE T1.[ID] In
(
SELECT Max(T1Sub.[ID])
FROM [Table1] AS T1Sub
GROUP BY T1Sub.[Name]
)

 

[Guest]

I have read about creating a totals query but I do not understand the whole
concept. So far I've created a query that takes three fields and displays
the max for each field. Now I need the highest number from these three
fields to display on a report. Any help would be appreciated. Thanks in
advance.

Divide and conquer is the strategy here. Create a totals query that selects
the Max of your field, then create a second query that joins the first to the
two tables of interest:

SELECT FieldList1, FieldList2
FROM (Table1 INNER JOIN [Max] ON Table1.Key = Max.MaxOfID) INNER JOIN Table2
ON Max.MaxOfID = Table2.Key;

Hope that helps.
Sprinks

I have a problem that is driving me nuts. I am pulling several fields from
each table, but am connecting on a common field. I am trying to return only
the maximum value from Table 1 and connect that to Table 2. I tried several
ways and I continue to get the max value from table 1 and all of table 2.

Any suggestions?

Example:

Table 1 (ID) Table 2 (ID)
33 33
38 38
37 37
45 45 <---- Return Only This Entry