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

i have 3 queries. i need one result

P: n/a
greetings. first, i apologize for asking an old question. it appears
that i'm looking for a solution that was already given but it don't
seem to fit my scenario. so thanks for putting up with me.

here's what i have.

a table called "RESULTS" - this table has 4 columns i need to work
with. CustID, Matrix, Test, Parameter.

i have three select queries that perform a count on how many times a
certain "Parameter" is listed.

the first query produces results for all customers (no filtering of
CustID). ** For this message post I'll use the Parameter of "ALUMINUM"
as an example.

so, my first query produces all CustID with Paramter:ALUMINUM appearing
155 times.

my second query (through query design view) filters CustID using; <>
"InHouse". my result for the second query is ALUMINUM appearing 153
times.

my third query (through query design view) filters CustID using;
"InHouse". my resut for the third query is ALUMINUM appearing 2 times.
So my math and three queries are correct.

What I have been trying to do is get ONE query/table that will show a
header with the following; MATRIX, TEST, PARAMETER, Count of Query1,
Count of Query2, Count of Query3.

so that my data row will have; (data for Matrix), (data for Test),
ALUMINUM (aluminum is the data for parameter), 155, 153, 2

the three counts is what is killing me. i can't get the three queries
into one so that i can carry one report instead of three.

This is a long post. I apologize. I'm almost done. Query2 & Query3
which are a subset of Query1 have less data. By this I mean that
ALUMINUM appears on all three but another item like IRON will appear in
Query1 (of course) and Query2 but not in Query3 - or Query3 will show
'0' as a count when all three queries appear side by side (naturally
since there is no "IRON" results for CustID "INHOUSE" generated in
Query3).

I hope i wrote enough detail. I had accomplished this last year but
have lost my original query and the backups on disk. I appreciate the
posts.

I've tried Unions, Crosstabs, Table Creation and then another 4th
query. I've been on this so long, i'm running in circles.
Thanks again.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Create a fourth query that includes your three queries. Join Query2 to
Query1 on CustID. Click on the join line and change the join type to the one
that says something like Include all of Query1 and matches from Query2. Join
Query3 to Query1 on CustID. Click on the join line and change the join type
to the one that says something like Include all of Query1 and matches from
Query3.

Create a fifth query that includes the fourth query. Change the fifth query
to Crosstab.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<ro******@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
greetings. first, i apologize for asking an old question. it appears
that i'm looking for a solution that was already given but it don't
seem to fit my scenario. so thanks for putting up with me.

here's what i have.

a table called "RESULTS" - this table has 4 columns i need to work
with. CustID, Matrix, Test, Parameter.

i have three select queries that perform a count on how many times a
certain "Parameter" is listed.

the first query produces results for all customers (no filtering of
CustID). ** For this message post I'll use the Parameter of "ALUMINUM"
as an example.

so, my first query produces all CustID with Paramter:ALUMINUM appearing
155 times.

my second query (through query design view) filters CustID using; <>
"InHouse". my result for the second query is ALUMINUM appearing 153
times.

my third query (through query design view) filters CustID using;
"InHouse". my resut for the third query is ALUMINUM appearing 2 times.
So my math and three queries are correct.

What I have been trying to do is get ONE query/table that will show a
header with the following; MATRIX, TEST, PARAMETER, Count of Query1,
Count of Query2, Count of Query3.

so that my data row will have; (data for Matrix), (data for Test),
ALUMINUM (aluminum is the data for parameter), 155, 153, 2

the three counts is what is killing me. i can't get the three queries
into one so that i can carry one report instead of three.

This is a long post. I apologize. I'm almost done. Query2 & Query3
which are a subset of Query1 have less data. By this I mean that
ALUMINUM appears on all three but another item like IRON will appear in
Query1 (of course) and Query2 but not in Query3 - or Query3 will show
'0' as a count when all three queries appear side by side (naturally
since there is no "IRON" results for CustID "INHOUSE" generated in
Query3).

I hope i wrote enough detail. I had accomplished this last year but
have lost my original query and the backups on disk. I appreciate the
posts.

I've tried Unions, Crosstabs, Table Creation and then another 4th
query. I've been on this so long, i'm running in circles.
Thanks again.

Nov 13 '05 #2

P: n/a
PC Datasheet wrote:
Create a fourth query that includes your three queries. Join Query2 to
Query1 on CustID. Click on the join line and change the join type to the one
that says something like Include all of Query1 and matches from Query2. Join
Query3 to Query1 on CustID. Click on the join line and change the join type
to the one that says something like Include all of Query1 and matches from
Query3.

Create a fifth query that includes the fourth query. Change the fifth query
to Crosstab.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<ro******@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
greetings. first, i apologize for asking an old question. it appears
that i'm looking for a solution that was already given but it don't
seem to fit my scenario. so thanks for putting up with me.

here's what i have.

a table called "RESULTS" - this table has 4 columns i need to work
with. CustID, Matrix, Test, Parameter.

i have three select queries that perform a count on how many times a
certain "Parameter" is listed.

the first query produces results for all customers (no filtering of
CustID). ** For this message post I'll use the Parameter of "ALUMINUM"
as an example.

so, my first query produces all CustID with Paramter:ALUMINUM appearing
155 times.

my second query (through query design view) filters CustID using; <>
"InHouse". my result for the second query is ALUMINUM appearing 153
times.

my third query (through query design view) filters CustID using;
"InHouse". my resut for the third query is ALUMINUM appearing 2 times.
So my math and three queries are correct.

What I have been trying to do is get ONE query/table that will show a
header with the following; MATRIX, TEST, PARAMETER, Count of Query1,
Count of Query2, Count of Query3.

so that my data row will have; (data for Matrix), (data for Test),
ALUMINUM (aluminum is the data for parameter), 155, 153, 2

the three counts is what is killing me. i can't get the three queries
into one so that i can carry one report instead of three.

This is a long post. I apologize. I'm almost done. Query2 & Query3
which are a subset of Query1 have less data. By this I mean that
ALUMINUM appears on all three but another item like IRON will appear in
Query1 (of course) and Query2 but not in Query3 - or Query3 will show
'0' as a count when all three queries appear side by side (naturally
since there is no "IRON" results for CustID "INHOUSE" generated in
Query3).

I hope i wrote enough detail. I had accomplished this last year but
have lost my original query and the backups on disk. I appreciate the
posts.

I've tried Unions, Crosstabs, Table Creation and then another 4th
query. I've been on this so long, i'm running in circles.
Thanks again.


thanks!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.