special query

[jon via AccessMonster.com]

I have a db group by user ID numbers. In this db I have two fields.
transaction and fund. A user can have more then one transaction what i want
to do is ONLY display if the user put money into the fund AND took out money.

for example
-----------

trans fund
f a
f a
t b
t b
t c
t c


so here the user is not putting in and taking out from the same fund. so none
get displayed but if i added

f c

i would want to display all transactions dealing wit that fund.

if anyone could help i would really appreciate it, i really dont have a clue


thanks
-Jon

 

[John Vinson]

I have a db group by user ID numbers.

Jargon alert: you have a *table*. A Database ("db") is a container for
multiple tables, objects, reports, and so on. Also, a Table is not
"grouped" - a table is an unordered bucket of data.

In this db I have two fields.
transaction and fund. A user can have more then one transaction what i want
to do is ONLY display if the user put money into the fund AND took out money.

for example
-----------

trans fund
f a
f a
t b
t b
t c
t c


so here the user is not putting in and taking out from the same fund. so none
get displayed but if i added

f c

i would want to display all transactions dealing wit that fund.

Well, I don't either; there is nothing at all obvious in your example
which explains how you determine whether a transaction involves
putting in or taking out. How do you tell?

John W. Vinson[MVP]

 

[jon via AccessMonster.com]

well if there is both "f" and "t" for the same fund ID i want to show it if
there is only t's or f's for a fund ID i want to discard those records.


makes anymore sense?

 

[John Spencer (MVP)]

SELECT Fund
FROM Table
WHERE Exists
(SELECT *
FROM Table as Tmp
WHERE Tmp.Fund = Table.Fund And Trans = "F") AND
Exists
(SELECT *
FROM Table as Tmp
WHERE Tmp.Fund = Table.Fund And Trans = "T")

You could also do this with two queries.

QueryA
SELECT DISTINCT Fund, Trans
FROM Table
WHERE Trans in ("F","T")

Second Query
SELECT Fund
FROM Table
WHERE Fund In
(SELECT Fund
FROM QueryA
GROUP BY Fund
HAVING Count(Fund) = 2)