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

Another Access Query problem - help appreciated

P: 69
Okay... here's another query that I need to write that I just can't seem to figure out.

first the table layout (table name TEST):

Expand|Select|Wrap|Line Numbers
  1. ID#     PreID#     PostID#     Dollars
  2. 1         2           1           20
  3. 1         2           1           30
  4. 1         3           1           10
  5. 2         5           3           15
  6. 1         4           1           25
Okay... here's what I need to do. I need to select the records where:

1. the ID# is equal to the PostID# AND
2. the count of distinct values in the PreID# is greater than 2

So with my example, the records with 1 in the ID# field have equal values (1) in the PostID# field, and the distinct count of the PreID# field is 3 (there are two 2s, one 3, and one 4 for a distinct count of 3). I don't care about the Dollars field, I just put it in there to show that duplicate ID#s are okay.

I hope this makes sense...

Is this possible?

Thanks so much in advance,
Rod
May 8 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,364
Okay... here's another query that I need to write that I just can't seem to figure out.

first the table layout (table name TEST):

Expand|Select|Wrap|Line Numbers
  1. ID#     PreID#     PostID#     Dollars
  2. 1         2           1           20
  3. 1         2           1           30
  4. 1         3           1           10
  5. 2         5           3           15
  6. 1         4           1           25
Okay... here's what I need to do. I need to select the records where:

1. the ID# is equal to the PostID# AND
2. the count of distinct values in the PreID# is greater than 2

So with my example, the records with 1 in the ID# field have equal values (1) in the PostID# field, and the distinct count of the PreID# field is 3 (there are two 2s, one 3, and one 4 for a distinct count of 3). I don't care about the Dollars field, I just put it in there to show that duplicate ID#s are okay.

I hope this makes sense...

Is this possible?

Thanks so much in advance,
Rod
Actually the distinct count is 4. 2,3,5,4. If you're talking about distinct count by grouping, then you'll have to specify what grouping. By ID# and PostID#? By ID# only? By PostID# only? Any one of these can be the case from the example you gave. But it may not be the case for all examples so you'll have to be clearer on this point.

But, in either case you'd use an aggregate query grouping by the ID fields you need and then using a count and setting up the proper criteria.
May 8 '07 #2

P: 69
Actually the distinct count is 4. 2,3,5,4. If you're talking about distinct count by grouping, then you'll have to specify what grouping. By ID# and PostID#? By ID# only? By PostID# only? Any one of these can be the case from the example you gave. But it may not be the case for all examples so you'll have to be clearer on this point.

But, in either case you'd use an aggregate query grouping by the ID fields you need and then using a count and setting up the proper criteria.
Based on my criteria... the distinct count is 3... the number 5 is not in my criteria as stated becaust the ID# (2) is not equal to the PostID# (3), which omits that PreID# (5). This leaves us with PreID#s 2, 3, and 4.

Any ideas on how to specifically write this query? Everything I try tells me that I can't use an aggregrate function in the where clause.
May 8 '07 #3

JConsulting
Expert 100+
P: 603
Based on my criteria... the distinct count is 3... the number 5 is not in my criteria as stated becaust the ID# (2) is not equal to the PostID# (3), which omits that PreID# (5). This leaves us with PreID#s 2, 3, and 4.

Any ideas on how to specifically write this query? Everything I try tells me that I can't use an aggregrate function in the where clause.
How about something like so?

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT iif(Count([PreID#])>3,Count([PreID#]),0) AS Count, [ID#]
  2. FROM Test
  3. WHERE (((test.[ID#])=[PostID#]))
  4. group by [ID#]
  5.  
J
May 8 '07 #4

Rabbit
Expert Mod 10K+
P: 12,364
Expand|Select|Wrap|Line Numbers
  1. ID#     PreID#     PostID#     Dollars
  2. 1         2           8           20
  3. 1         2           1           30
  4. 1         3           1           10
  5. 2         5           3           15
  6. 1         4           1           25
So you're saying in this example, the Count would still be 3 correct?
May 8 '07 #5

P: 69
Yes, this will work for me...

Thanks so much for your help, I really appreciate it.
Rod


How about something like so?

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT iif(Count([PreID#])>3,Count([PreID#]),0) AS Count, [ID#]
  2. FROM Test
  3. WHERE (((test.[ID#])=[PostID#]))
  4. group by [ID#]
  5.  
J
May 8 '07 #6

P: 69
Yes, the count would still be 3 in that example.

Thanks,
Rodney

Expand|Select|Wrap|Line Numbers
  1. ID#     PreID#     PostID#     Dollars
  2. 1         2           8           20
  3. 1         2           1           30
  4. 1         3           1           10
  5. 2         5           3           15
  6. 1         4           1           25
So you're saying in this example, the Count would still be 3 correct?
May 8 '07 #7

Rabbit
Expert Mod 10K+
P: 12,364
Yes, this will work for me...

Thanks so much for your help, I really appreciate it.
Rod
All set then?
May 8 '07 #8

Post your reply

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