By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,960 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,960 IT Pros & Developers. It's quick & easy.

Group By query that groups FK and counts the PK

P: 1
I am working with a two field table of 144,020 rows.

The PERSONID field is the FK and the ADDRESSID is the PK.

FK is a number type, PK is autonumber.

PERSONID represents employees, ADDRESSID represents addresses.

The objective is to mark all single addresses as primary and all double addresses (for the same employee) as 'primary and secondary'.

Here is what I am doing that is not working, perhaps someone can tell me the problem and hopefully a solution that meets the objective.

I am doing a 'group by' query that groups by PERSONID and counts the PK of ADDRESSID.

I set the criteria for the count to 1, (as I need to return only those PERSONID with one address.

After running this. I run an Update query that sets the PRIORITY (new added field) to PRIMARY for all the single records by using a Subquery (I set the criteria for the PERSONID to:

IN(SELECT PERSONID FROM qrySglAddress)

What am I missing in order to have all single addresses marked as primary and all double addresses marked as 'primary and secondary'.

thanks for any help on this.

richforsandy
Oct 1 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Hi man,

It seems that you need to do soth like ranking of your adresses! Here in the Access forum recently has a lot of usefull information about ranking, done by SQL and by VB!

Type

ranking
rank
rang
Count

in the search engine in the scripts and Y'll have all needed results :)

If pb here we are! :)

Best regards

:)


I am working with a two field table of 144,020 rows.

The PERSONID field is the FK and the ADDRESSID is the PK.

FK is a number type, PK is autonumber.

PERSONID represents employees, ADDRESSID represents addresses.

The objective is to mark all single addresses as primary and all double addresses (for the same employee) as 'primary and secondary'.

Here is what I am doing that is not working, perhaps someone can tell me the problem and hopefully a solution that meets the objective.

I am doing a 'group by' query that groups by PERSONID and counts the PK of ADDRESSID.

I set the criteria for the count to 1, (as I need to return only those PERSONID with one address.

After running this. I run an Update query that sets the PRIORITY (new added field) to PRIMARY for all the single records by using a Subquery (I set the criteria for the PERSONID to:

IN(SELECT PERSONID FROM qrySglAddress)

What am I missing in order to have all single addresses marked as primary and all double addresses marked as 'primary and secondary'.

thanks for any help on this.

richforsandy
Oct 1 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.