output query based on 3 queries?

  • Thread starter Thread starter Shivalee Gupta via AccessMonster.com
  • Start date Start date
S

Shivalee Gupta via AccessMonster.com

can anyone help me?
or we can think like this:
we need 3 different queries initially, i.e
1-show the icode which has an object stcode, which has a field tcd and
which has values f110.
2-show the icode which has object f_regu. which has field fbtch, which has
values 2,3,11.
3-show the icode which has object f_regu. which also has field bukrs, but
has no value.
now i need to combine all these 3 queries and see the common code coming
out of the 3. that would be my required answer!.
basically i need the icode which meets all the above requirements, as a
group, not individually.
 
Shivalee

If what you want to end up with is a list of icodes (from each of the three
queries), you could use a UNION query. Take a look in Access HELP for
details, but it generally looks like:

SELECT icode FROM Table1
(...JOIN ..)
WHERE (...)
UNION
SELECT icode FROM Table1
(...2nd JOINs ...)
WHERE (second where conditions)
UNION
SELECT icode FROM Table1
(...3rd JOINs...)
WHERE (third where conditions)

You can get the SQL statements for each of the queries by building them in
the query designer first, then changing your view to SQL statement and
copying them out. You'd need to create a new, fourth query, set it's view
to SQL, and use only the SQL statements (you can't use the designer window
to do UNION queries).
 
Shivalee,
Another option might be something like:
Select icode from table where object = stcode and TCD = f110 and icode =
(select icode from table where object = f_regu and fbtch in (2,3,11) and
icode = (select icode from table where object = f_regu and bukrs = "")))

This might be faster if you are dealing with large tables and unions.
CF
 
thank you jeff. your code is working. i put in:
select agr_name from agr_1251
where object="s_tcode" and field="tcd" and low="f110"
union
select agr_name from agr_1251
where object="f_regu_buk" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="bukrs" and low=""
UNION select agr_name from agr_1251
where object="f_regu_koa" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="koart" and low="";

but wart, yours is not. i put in:

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.AGR_NAME)=(select agr_name from agr_1251
where object="f_regu_buk" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="bukrs" and low=""
and agr_name=
(select agr_name from agr_1251
where object="f_regu_koa" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="koart" and low=""))) AND ((agr_1251.OBJECT)="s_tcode") AND (
(agr_1251.FIELD)="tcd") AND ((agr_1251.LOW)="f110"));

can you tell me what i am doing wrong in wart's code?
my question now: how can get distinct values in this output?

thanks jeff, once again,
regards,
shivalee
 
Shivalee

Are you saying that you get more than one instance of a particular icode
value? Check Access HELP on using UNION queries, but I thought that UNION
provided distinct values, unless you used the keyword "ALL", which might
have duplicates... (or maybe I am mis-remembering...)

Good luck

Jeff Boyce
<Access MVP>
 
hey jeff.
i have tried union also.

select agr_name from agr_1251
where object="s_tcode" and field="tcd" and low="f110"
UNION
select agr_name from agr_1251
where object="f_regu_buk" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="bukrs" and low=""
UNION select agr_name from agr_1251
where object="f_regu_koa" and field="fbtch" and low="2" and low="3" and
low="11" and low="21" and low="25"
and field="koart" and low="";

but it is also not giving me required results.
please help!
shivalee
 
Shivalee

I believe this is related to your other post, and that the (second) SQL
statement is in error. How could the "low" field be "2" and "3" and "11"
and "21" and "25", all at the same time?
 
let us refer to the other site only. otherwise it is difficult to cpmrehend.
shivalee
 
Back
Top