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

Select/query Question for Access 2003

100+
P: 119
hi,

Does anyone know how you would select, from a table of data, only the distinct values for one field that always appear with each value in a second field?

For example...Suppose the data is the following

Field1 Field2
2 a
3 a
4 a
1 b
2 b
3 b
4 b
1 c
2 c
4 c

Because only 2 and 4 always occur for every value in the second field, the query would return:

2 a
4 a
2 b
4 b
2 c
4 c

Any help much appreciated.
Jun 7 '07 #1
Share this Question
Share on Google+
3 Replies

P: 16
. .
Jun 7 '07 #2

100+
P: 119
Does anyone have any ideas? I would imagine there is a very simple solution...
Jun 11 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Does anyone have any ideas? I would imagine there is a very simple solution...

I would be glad to see "a very simple solution".

Here is my ugly one.

Table: t1
.f1 (Number)
.f2 (Text)

Query: qryF2s
SELECT DISTINCT t1.f2 FROM t1;

Query: qryDRF1s
SELECT DISTINCTROW t1.f1 AS DRf1 FROM t1;

Query: qryDRF1Count_vs_F2Count
SELECT
t1.f1 AS f1,
t1.f2 AS f2,
(SELECT Count(*) FROM qryDRF1s WHERE DRf1=f1) AS DRF1Count,
(SELECT Count(*) FROM qryF2s) AS F2Count
FROM t1;

Query: qryResult (displays result)
SELECT
qryDRF1Count_vs_F2Count.f1,
qryDRF1Count_vs_F2Count.f2
FROM
qryDRF1Count_vs_F2Count
WHERE qryDRF1Count_vs_F2Count.DRF1Count=qryDRF1Count_vs_ F2Count.F2Count;

Really nice question.
Jun 11 '07 #4

Post your reply

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