Max value form a group

[Guest]

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??

 

[Guest]

Your data has a problem in that your highest percent do not have item names.
It looks like spreadsheet data.

 

[Guest]

In a matter of fact it is...
This is a linked table from an Excel spreadsheet...
do you suggest that i should make changes to the excel file?
should the blanks contain the item names?

Your data has a problem in that your highest percent do not have item names.
It looks like spreadsheet data.

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??

 

[Guest]

Yes if you want to work the data in Access.

In a matter of fact it is...
This is a linked table from an Excel spreadsheet...
do you suggest that i should make changes to the excel file?
should the blanks contain the item names?

Your data has a problem in that your highest percent do not have item names.
It looks like spreadsheet data.

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??

 

[Guest]

***oops a mistype in my subject it's 'from' and not 'form' ***

OK. I've done that...
but still got problems with the query:

SELECT DISTINCTROW TABLE.name, TABLE.type, Max(TABLE.percent) AS [Max Of
percent]
FROM TABLE
GROUP BY TABLE.name, TABLE.type;

Yes if you want to work the data in Access.

In a matter of fact it is...
This is a linked table from an Excel spreadsheet...
do you suggest that i should make changes to the excel file?
should the blanks contain the item names?

Your data has a problem in that your highest percent do not have item names.
It looks like spreadsheet data.

:

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??

 

[Guest]

What kind of problems?

***oops a mistype in my subject it's 'from' and not 'form' ***

OK. I've done that...
but still got problems with the query:

SELECT DISTINCTROW TABLE.name, TABLE.type, Max(TABLE.percent) AS [Max Of
percent]
FROM TABLE
GROUP BY TABLE.name, TABLE.type;

Yes if you want to work the data in Access.

In a matter of fact it is...
This is a linked table from an Excel spreadsheet...
do you suggest that i should make changes to the excel file?
should the blanks contain the item names?


:

Your data has a problem in that your highest percent do not have item names.
It looks like spreadsheet data.

:

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??

 

[Guest]

If I understand you correctly, you want to find the Max percent for each
[name], and also list the associated [type]. I've only been able to do this
using 2 steps.

Query1 finds the Max percent for each [name]. SQL:

SELECT Table.name, Max(Table.percent) AS MaxOfpercent
FROM

GROUP BY Table.name;

Query2 used the results of Query1 and the original Table. Join Query1.name
with Table.name, and Join Query1.MaxOfpercent with Table.percent. SQL:

SELECT Table.name, Table.type, Query1.MaxOfpercent
FROM

INNER JOIN Query1 ON (Table.percent = Query1.MaxOfpercent) AND
(Table.name = Query1.name);

 

[Guest]

Thanks JPC it worked just fine... what i was missing was a little bit of
common logic... thanks again
Also thanks to Karl for his helpful suggestions

If I understand you correctly, you want to find the Max percent for each
[name], and also list the associated [type]. I've only been able to do this
using 2 steps.

Query1 finds the Max percent for each [name]. SQL:

SELECT Table.name, Max(Table.percent) AS MaxOfpercent
FROM

GROUP BY Table.name;

Query2 used the results of Query1 and the original Table. Join Query1.name
with Table.name, and Join Query1.MaxOfpercent with Table.percent. SQL:

SELECT Table.name, Table.type, Query1.MaxOfpercent
FROM

INNER JOIN Query1 ON (Table.percent = Query1.MaxOfpercent) AND
(Table.name = Query1.name);

Hello,
I have a table that looks like this:

[name] [percent] [type]
Record1: Item 1 23% 2
Record2: <blank> 34% 3
Record3: <blank> 55% 1
Record4: Item 2 34% 3
Record5: <blank> 22% 2
Record6: <blank> 10% 5
Record7: 40% 5

I'm trying to make a query that finds the highest percent value from each
item and returns the Item's name and type number...
Can anybody help me please??