Dmax conditions in a query

[DocNice]

I need to find the largest and smallest values in a table based on the Primary Key for each record in a query.

For example.
Table1
ID
A
B
C

Table2
ID - Num
A - 1
A - 2
A - 3
B - 1
B - 2
B - 3
B - 4
C - 1
C - 2

I need it to return:
A - 1 | 3
B - 1 | 4
C - 1 | 2

I have searched through the site already and not found anything that I can get to work. I'm basically telling the query to select all Table1.ID and select Table2.Num only where it is equal to Table1. But I need to select only the max number. I'm close using this for the criteria.
=DMax("[Num]","Table2")

But I need to link it to the ID in Table1, otherwise it only returns one row, the highest in the entire table. I can't seem to find any combination of criteria to get it to work (ie: =Dmax("[Num]",Table2","[Table2.ID] = [Table1.ID]")

Any suggestions?
Thanks

 

[pbaldy]

Try this, since ID is text:

MaxNum: Dmax("[Num]","Table2","ID = '" & ID & "'")

 

[mresann]

Of couse, use "DMin" instead of "DMax" to get the smallest value.

 

[DocNice]

Shoot, actually it's the reverse. I rewrote things to make it simpler, but ID is a number and num is a text field. I tried it anyway, and it wouldn't accept the syntax, at least in the criteria (design view).

I took out MaxNum: and it accepted the syntax but returned a type mismatch, I assume because it's not text. I tried taking away some of the quotes but still no luck.

Any tweaks you'd suggest?

 

[pbaldy]

I put the MaxNum: in there because I thought you were trying to make a new field in a query. The syntax for a numerical value would be:

Dmax("[Num]","Table2","ID = " & ID)

 

[DocNice]

Excellent. Thanks for the great help and the fast response.

This works, but unfortunately I'm going to have to try another direction I think. Adding in the DMin field narrows it too much, and it also filters out any blanks, which I can't have. Thanks for your help though, and I'm going to look at other ways I might be able to tackle this.

 

[pbaldy]

If you're trying to achieve the result you specified in the first post, I'm not sure why you'd want this in the criteria. You'd want it as a new field. In fact the easiest way to get that result would be a totals query. Something like:

SELECT ID, Max(Num) AS MaxNum, Min(Num) AS MinNum
FROM Table2
GROUP BY ID

You might want to join in table 1 if there were missing items from table 2 that you still wanted to include.

 

[mresann]

SELECT [ID] & " - " & Min([Table1].[Num]) & " | " & Max([Table1].[Num]) AS Statement
FROM Table2
GROUP BY Table2.ID
ORDER BY Table2.ID;


Include a "Having" statement or a where clause to restrict the ID number if you want to include it as a parameter, you can also join table one on field ID if you wish.

 

[pbaldy]

SELECT [ID] & " - " & Min([Table1].[Num]) & " | " & Max([Table1].[Num]) AS Statement
FROM Table2
GROUP BY Table2.ID
ORDER BY Table2.ID;

Will that work, with Table1 not included in the FROM clause?

 

[mresann]

OOPS...let me rephrase that...

I used the wrong table names in my example... I had used my own table structure to set it up then manually input my own.

In your case, switch all table names in the query to Table2. If you want to include a join on Table1 then use the QBE grid to add the table.

 

[pbaldy]

I'm not the OP, so I don't know the full requirements. I simple wondered if the posted SQL would work at all, since the Min and Max portions specified Table1, but Table1 was not included in the FROM. I suspect it will pop up a parameter box asking for Table1.Num. It was probably just a typo; I just thought I would point it out.

 

[mresann]

OK, let me summarize everything here.

One table, Table1

Two fields, ID and Num

The query:

SELECT [ID] & " - " & Min([Table1].[Num]) & " | " & Max([Table1].[Num]) AS Statement
FROM Table1
GROUP BY Table1.ID
ORDER BY Table1.ID;

In a query with only one table, you don't have to preface the field names with "Table1." However, it's good practice as it disambiguates* field names if you ever modify the query later with joins from other tables.

Your query will have one field showing called "Statement". You can rename it to any other name (other than ID or Num) that doesn't conflict with VBA or SQL internals.

*Thanks Pat for the vocabulary lesson!