Minimum values in a query

[mihabaki]

Hi

I have a table (tbl1) with three fields/columns (Primary key, NameID, Number). E.g.:

NameID ... Number
Mike ........ 2
Mike ........ 3
Mike ........ 19
Ronald ...... 1
Ronald ...... 2
Greg ........ 7
...

I would like to make a query that lists each NameID only ONCE, with corresponding MINIMUN Number. So the result from the upper example would be:

NameID ... Number
Mike ......... 2
Ronald ....... 1
Greg ........ 7


How do I do it???


Thank you in advance

m.

 

[Len Boorman]

straight forward select query then click the Sigma sign from the toolbar to make it aggregation and select Minimum for ID

L

 

[mihabaki]

Thank you. It works.

M.

 

[mihabaki]

this doesn't really work the way I expected it to... the problem is that I have more than two fields (columns):


ID........NameID ... Number
1 .........Mike ........ 2
2..........Mike ........ 3
3..........Mike ........ 19
4..........Ronald ...... 1
5..........Ronald ...... 2
6..........Greg ........ 7
...

...and would like the result to be:

ID........NameID ... Number
1 ........Mike ......... 2
4.........Ronald ....... 1
6.........Greg ........ 7

AND ALSO I would like to be able to USE the result (records with corresponding minimum values) in other qeuries/tables...

How should I do this then???

 

[Len Boorman]

The problem lies with your fields

You have Mike with an ID of 1 and 2

What is Mike's ID ?.

When you run the query it looks at the complete record so you get the apparent double result, One solution would be to leave the ID out of the query.

I suspect you have not Normalised you database and that primary keys are not correctly defined.

len

 

[mihabaki]

I believe I haven't made myself clear enough: the field "ID" is the primary key, the field "Name" is not indexed and the same value can be entered more than one time - the same goes for the "number" field. I also have about ten other fields in this table, but it wouldn't serve any purpose to describe them here.

For each "name" group of names there can only be ONE record with minimum number (e.g. there is no way that a record with the name Mike can have to exactly the same values in the "number" field).

So again, here is my example table:

ID........Name ... Number
1 .........Mike ........ 2
2..........Mike ........ 3
3..........Mike ........ 19
4..........Ronald ...... 1
5..........Ronald ...... 2
6..........Greg ........ 7
...

...and I would like the result of a query to be:

ID........Name ... Number
1 ........Mike ......... 2
4.........Ronald ....... 1
6.........Greg ........ 7

AND ALSO I want to use the result of these query in other queries, therefore I doubt that aggregation is the wright way to do this?!?!?

 

[Len Boorman]

I believe that the primary key you have chosen is the problem.

Copy the table and delete the primary key that you have.

Make Name and Number a dual field primary key and run the query over this new table

The selection of Primary keys is paramount in the desifn of a database.

Len

 

[neileg]

You could used a nested query to get what you want.

Create a new query. Add in the query that returns the NameID and the minimum number. Add in your original table. Join the table and the query and join them on both NameID and Number. In the grid take the NameID and Number ID from the query and the ID field from the table. Note that you may still get more than one record if the minimum number is non unique for ach name, if Mike has two records where the number is 2, both will be returned.

 

[mihabaki]

Sorry I forgot to say that this works!!!

Thank you neileg

M.