joining two queries

[ron]

I have two queries with three fields each. Two of the
three, [Customer] and [ServiceCode] are exactly alike and
pulling from the same tables. The only difference is that
Query1 has a calculated field [Exempt]and Query2 has a
corresponding field [Taxed]. The resulting records for
the two queries are mutually exclusive.

I would like to use the two queries to create a third
query that returns all the records from each, with the
[Exempt] and [Taxed] displaying thus:

[Customer] [ServiceCode] [Exempt] [TaxAmount]
Smith 4525 100.00
Jakes 2525 47.50
Jones 2521 82.58
Castle 2561 150.00

Possible? Easily accomplished. I tried messing around with
joins but only proved to myself that I don't know what I'm
doing.

Thanks,
ron

 

[Nikos Yannacopoulos]

Ron,

Make a query and add your original table and the two
calculation queries. Join the main table to each query on
the two common fields, double-click on the joins and
select option"select all records from

and only
those from [QueryX] where...".
Then select as output fields the two common fields from
the main table and the calculated fields from the queries.

HTH,
Nikos

 

[ron]

Thanks for the guidance. Unfortunately, I think I
oversimplied my situation and your solution only partially
fits. My two original queries each pull from three tables,
so your instruction "add your original table" needs
instead to be "add your original tables." I tried this,
following your instruction with the joins, but I'm getting
an "ambiguous outer join" error. Let me know if more
specific information about the table relationships would
be useful.

thanks...
rOn

-----Original Message-----
Ron,

Make a query and add your original table and the two
calculation queries. Join the main table to each query on
the two common fields, double-click on the joins and
select option"select all records from

and only
those from [QueryX] where...".
Then select as output fields the two common fields from
the main table and the calculated fields from the queries.

HTH,
Nikos

-----Original Message-----
I have two queries with three fields each. Two of the
three, [Customer] and [ServiceCode] are exactly alike and
pulling from the same tables. The only difference is that
Query1 has a calculated field [Exempt]and Query2 has a
corresponding field [Taxed]. The resulting records for
the two queries are mutually exclusive.

I would like to use the two queries to create a third
query that returns all the records from each, with the
[Exempt] and [Taxed] displaying thus:

[Customer] [ServiceCode] [Exempt] [TaxAmount]
Smith 4525 100.00
Jakes 2525 47.50
Jones 2521 82.58
Castle 2561 150.00

Possible? Easily accomplished. I tried messing around with
joins but only proved to myself that I don't know what I'm
doing.

Thanks,
ron
.

.

 

[Nikos Yannacopoulos]

Ron,

If I get it right, you tried to use the three joined
tables as a "basis" instead of the original one I was
referring to, and add the two calculation queries, all
within one query? That's often tricky to get to work as
far as joins go. Your chances are a hell of a lot better
if you combine the three original tables in one query as
a "basis" and use this instead of what I called
the "original table" in the subsequent query that binds
the two calculated ones.
If this doesn't work either (which I doubt), I would be
glad to have a look at your database to see if I can be of
any more help, provided there's no sensitive data in
there... in any case, if you decide to e-mail it to me,
pls unload the bulk of the data leaving just a sample, if
a big file.

Best,
Nikos

-----Original Message-----
Thanks for the guidance. Unfortunately, I think I
oversimplied my situation and your solution only partially
fits. My two original queries each pull from three tables,
so your instruction "add your original table" needs
instead to be "add your original tables." I tried this,
following your instruction with the joins, but I'm getting
an "ambiguous outer join" error. Let me know if more
specific information about the table relationships would
be useful.

thanks...
rOn

-----Original Message-----
Ron,

Make a query and add your original table and the two
calculation queries. Join the main table to each query on
the two common fields, double-click on the joins and
select option"select all records from

and only
those from [QueryX] where...".
Then select as output fields the two common fields from
the main table and the calculated fields from the queries.

HTH,
Nikos

-----Original Message-----
I have two queries with three fields each. Two of the
three, [Customer] and [ServiceCode] are exactly alike and
pulling from the same tables. The only difference is that
Query1 has a calculated field [Exempt]and Query2 has a
corresponding field [Taxed]. The resulting records for
the two queries are mutually exclusive.

I would like to use the two queries to create a third
query that returns all the records from each, with the
[Exempt] and [Taxed] displaying thus:

[Customer] [ServiceCode] [Exempt] [TaxAmount]
Smith 4525 100.00
Jakes 2525 47.50
Jones 2521 82.58
Castle 2561 150.00

Possible? Easily accomplished. I tried messing around with
joins but only proved to myself that I don't know what I'm
doing.

Thanks,
ron
.

.

.