Another Access Query problem - help appreciated

[narpet]

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):

ID#     PreID#     PostID#     Dollars
1         2           1           20
1         2           1           30
1         3           1           10
2         5           3           15
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

[Rabbit]

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):

ID#     PreID#     PostID#     Dollars
1         2           1           20
1         2           1           30
1         3           1           10
2         5           3           15
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.

[narpet]

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.

[JConsulting]

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?

SELECT DISTINCT iif(Count([PreID#])>3,Count([PreID#]),0) AS Count, [ID#]
FROM Test
WHERE (((test.[ID#])=[PostID#]))
group by [ID#]
 

J

[Rabbit]

ID#     PreID#     PostID#     Dollars
1         2           8           20
1         2           1           30
1         3           1           10
2         5           3           15
1         4           1           25

So you're saying in this example, the Count would still be 3 correct?

[narpet]

Yes, this will work for me...

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

How about something like so?

SELECT DISTINCT iif(Count([PreID#])>3,Count([PreID#]),0) AS Count, [ID#]
FROM Test
WHERE (((test.[ID#])=[PostID#]))
group by [ID#]
 

J

[narpet]

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

Thanks,
Rodney

ID#     PreID#     PostID#     Dollars
1         2           8           20
1         2           1           30
1         3           1           10
2         5           3           15
1         4           1           25

So you're saying in this example, the Count would still be 3 correct?

[Rabbit]

Yes, this will work for me...

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

All set then?