Change Query for Group Headings on a Form 1

[Gazonice]

Hello,

I have succesfully used the code below to create basic group headers on a Form from a single Table (Access 2003). It works well.

SELECT
    m1.state,
    m1.names,
    (
        SELECT Count(names)
        FROM MyTable AS m2
        WHERE
            m2.state=m1.state
            AND m2.names<=m1.names
    ) AS rank
FROM MyTable AS m1
ORDER BY m1.state, m1.names;

But, I have a slight problem in that the field (names) is coming from a second table in my database and I cannot work out how to collect 'state' from MyTable (primary key = 'ID') and 'names' from MyTable2 (foreign key = 'link') and still apply the 'rank'.

Any help would be much appreciated - thank you.

Garry

 

[PHV]

Something like this ?

SELECT A.state, B.names
,(SELECT Count(*) FROM MyTable2 WHERE link=A.ID AND names<=B.names) AS rank
FROM MyTable A INNER JOIN MyTable2 B ON A.ID=B.link
ORDER BY SELECT A.state, B.names

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gazonice]

Hello,

Thank you for your reply.

Unfortunately the last line of your statement generates an error with 'SELECT'.
On removing 'SELECT', the Query runs but all the States are showing with a rank of 1.

Despite my own best efforts I still cannot get this to work.

Kindest Regards,

Garry

 

[PHV]

Sorry for the typo:

SELECT A.state, B.names
,(SELECT Count(*) FROM MyTable2 WHERE link=A.ID AND names<=B.names) AS rank
FROM MyTable A INNER JOIN MyTable2 B ON A.ID=B.link
ORDER BY A.state, B.names

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gazonice]

Hello,

Thank you for your reply - which is very much appreciated.

It must be very close but not quite there.. the rank column shows as '1' for all the results.

The names are all unique.

Kind Regards,

Garry.

 

[PHV]

What about this ?

SELECT A.state, B.names
,(SELECT Count(*) FROM FROM MyTable C INNER JOIN MyTable2 D ON C.ID=D.link WHERE C.state=A.state AND D.names<=B.names) AS rank
FROM MyTable A INNER JOIN MyTable2 B ON A.ID=B.link
ORDER BY A.state, B.names

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gazonice]

Thank you PHV for your replies, this seems to be working perfectly.

I have spent many hours trying to figure this one, you have saved me many more !

Star on it's way !

Kindest regards,

Garry

 

[PHV]

Sorry for the typo (FROM FROM)