finding the "most frequent " entry in one field for each instance in another field

[D Denholm]

I am a Access newbie... Hopefully somebody can help me figure this
out.

I have a database that looks like:

Asset Economic Minimum
----- ----------------
10555 320
10555 320
10555 320
10555 180
10555 500
30267 5
30267 5
30267 78
30267 100

I would like to be able to create a query that will give me a list of
the unique entries in the "Asset" column along with the "most
frequent" entry from the "Economic Minimum" column for each unique
"Asset" entry. The result of the query for this example would look
like:

Asset Economic Minimum
----- ----------------
10555 320
30267 5

If one could have additional columns that would give the count and/or
the frequency of the "most frequent" entry that would be even better.
The result of this query would look like:

Asset Economic Minimum Count Frequency
----- ---------------- ----- ---------
10555 320 3 .60
30267 5 2 .50
I suspect this is easy but I have never used Access before and the
Access book I have (Que: Using Access 2000) has not been much help...
Does anyone have a suggestion for a good book for learning Access?

Thanks,

ddenholm

[Phil Stanton]

You need 4 queries for the complete solution. Your data is held in Table1

Query1

SELECT Table1.Asset, Count(Table1.EcQty) AS CountOfEcQty, Table1.EcQty
FROM Table1
GROUP BY Table1.Asset, Table1.EcQty;

Query2

SELECT Query1.Asset, Max(Query1.CountOfEcQty) AS MaxOfCountOfEcQty
FROM Query1
GROUP BY Query1.Asset;
Query3

SELECT Count(Table1.Asset) AS CountOfAsset, Table1.Asset
FROM Table1
GROUP BY Table1.Asset;
Query4
SELECT Query1.Asset, Query1.CountOfEcQty, Query1.EcQty, Query3.CountOfAsset,
[CountOfEcQty]/[CountOfAsset] AS [Percent]
FROM Query3 INNER JOIN (Query1 INNER JOIN Query2 ON (Query1.CountOfEcQty =
Query2.MaxOfCountOfEcQty) AND (Query1.Asset = Query2.Asset)) ON Query3.Asset
= Query2.Asset
ORDER BY Query1.Asset;

Seems to give the correct result and also shows any "ties" when the
frequency is the same

Phil
"D Denholm" <dd******@theworld.com> wrote in message
news:e7**************************@posting.google.c om...

I am a Access newbie... Hopefully somebody can help me figure this
out.

I have a database that looks like:

Asset Economic Minimum
----- ----------------
10555 320
10555 320
10555 320
10555 180
10555 500
30267 5
30267 5
30267 78
30267 100

I would like to be able to create a query that will give me a list of
the unique entries in the "Asset" column along with the "most
frequent" entry from the "Economic Minimum" column for each unique
"Asset" entry. The result of the query for this example would look
like:

Asset Economic Minimum
----- ----------------
10555 320
30267 5

If one could have additional columns that would give the count and/or
the frequency of the "most frequent" entry that would be even better.
The result of this query would look like:

Asset Economic Minimum Count Frequency
----- ---------------- ----- ---------
10555 320 3 .60
30267 5 2 .50
I suspect this is easy but I have never used Access before and the
Access book I have (Que: Using Access 2000) has not been much help...
Does anyone have a suggestion for a good book for learning Access?

Thanks,

ddenholm

[D Denholm]

Thanks... I won't pretend I understand all the SQL but it does just
what I wanted. I had tried using the Access DesignView and the Wizard
but could not figure out how to make either do what I wanted.

I will see if I can figure out the details of your SQL. If you can
recommend a good intro text to SQL I would appreciate it.

Thanks again.

--Doug
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...

You need 4 queries for the complete solution. Your data is held in Table1

Query1

SELECT Table1.Asset, Count(Table1.EcQty) AS CountOfEcQty, Table1.EcQty
FROM Table1
GROUP BY Table1.Asset, Table1.EcQty;

Query2

SELECT Query1.Asset, Max(Query1.CountOfEcQty) AS MaxOfCountOfEcQty
FROM Query1
GROUP BY Query1.Asset;
Query3

SELECT Count(Table1.Asset) AS CountOfAsset, Table1.Asset
FROM Table1
GROUP BY Table1.Asset;
Query4
SELECT Query1.Asset, Query1.CountOfEcQty, Query1.EcQty, Query3.CountOfAsset,
[CountOfEcQty]/[CountOfAsset] AS [Percent]
FROM Query3 INNER JOIN (Query1 INNER JOIN Query2 ON (Query1.CountOfEcQty =
Query2.MaxOfCountOfEcQty) AND (Query1.Asset = Query2.Asset)) ON Query3.Asset
= Query2.Asset
ORDER BY Query1.Asset;

Seems to give the correct result and also shows any "ties" when the
frequency is the same

Phil
"D Denholm" <dd******@theworld.com> wrote in message
news:e7**************************@posting.google.c om...

I am a Access newbie... Hopefully somebody can help me figure this
out.

I have a database that looks like:

Asset Economic Minimum
----- ----------------
10555 320
10555 320
10555 320
10555 180
10555 500
30267 5
30267 5
30267 78
30267 100

I would like to be able to create a query that will give me a list of
the unique entries in the "Asset" column along with the "most
frequent" entry from the "Economic Minimum" column for each unique
"Asset" entry. The result of the query for this example would look
like:

Asset Economic Minimum
----- ----------------
10555 320
30267 5

If one could have additional columns that would give the count and/or
the frequency of the "most frequent" entry that would be even better.
The result of this query would look like:

Asset Economic Minimum Count Frequency
----- ---------------- ----- ---------
10555 320 3 .60
30267 5 2 .50
I suspect this is easy but I have never used Access before and the
Access book I have (Que: Using Access 2000) has not been much help...
Does anyone have a suggestion for a good book for learning Access?

Thanks,

ddenholm

[Phil Stanton]

If I could figure it out myself it would be marvellous.

I presume you have looked at the queries in design mode rather than SQL
which makes them somewhat clearer. You will note that the first 3 queries
are has the Total symbol on the toolbar highlighted.

Phil
"D Denholm" <dd******@theworld.com> wrote in message
news:e7**************************@posting.google.c om...

Thanks... I won't pretend I understand all the SQL but it does just
what I wanted. I had tried using the Access DesignView and the Wizard
but could not figure out how to make either do what I wanted.

I will see if I can figure out the details of your SQL. If you can
recommend a good intro text to SQL I would appreciate it.

Thanks again.

--Doug
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message

news:<3f***********************@mercury.nildram.ne t>...

You need 4 queries for the complete solution. Your data is held in Table1
Query1

SELECT Table1.Asset, Count(Table1.EcQty) AS CountOfEcQty, Table1.EcQty
FROM Table1
GROUP BY Table1.Asset, Table1.EcQty;

Query2

SELECT Query1.Asset, Max(Query1.CountOfEcQty) AS MaxOfCountOfEcQty
FROM Query1
GROUP BY Query1.Asset;
Query3

SELECT Count(Table1.Asset) AS CountOfAsset, Table1.Asset
FROM Table1
GROUP BY Table1.Asset;
Query4
SELECT Query1.Asset, Query1.CountOfEcQty, Query1.EcQty, Query3.CountOfAsset, [CountOfEcQty]/[CountOfAsset] AS [Percent]
FROM Query3 INNER JOIN (Query1 INNER JOIN Query2 ON (Query1.CountOfEcQty = Query2.MaxOfCountOfEcQty) AND (Query1.Asset = Query2.Asset)) ON Query3.Asset = Query2.Asset
ORDER BY Query1.Asset;

Seems to give the correct result and also shows any "ties" when the
frequency is the same

Phil
"D Denholm" <dd******@theworld.com> wrote in message
news:e7**************************@posting.google.c om...

I am a Access newbie... Hopefully somebody can help me figure this
out.

I have a database that looks like:

Asset Economic Minimum
----- ----------------
10555 320
10555 320
10555 320
10555 180
10555 500
30267 5
30267 5
30267 78
30267 100

I would like to be able to create a query that will give me a list of
the unique entries in the "Asset" column along with the "most
frequent" entry from the "Economic Minimum" column for each unique
"Asset" entry. The result of the query for this example would look
like:

Asset Economic Minimum
----- ----------------
10555 320
30267 5

If one could have additional columns that would give the count and/or
the frequency of the "most frequent" entry that would be even better.
The result of this query would look like:

Asset Economic Minimum Count Frequency
----- ---------------- ----- ---------
10555 320 3 .60
30267 5 2 .50
I suspect this is easy but I have never used Access before and the
Access book I have (Que: Using Access 2000) has not been much help...
Does anyone have a suggestion for a good book for learning Access?

Thanks,

ddenholm