Joining multiple queries into a single query

B

bg_ie

Hi,

I have the following queries -

SELECT Count([tblTestScriptStatus].[StatusType]) AS Count1
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="IMPLEMENTED"));

SELECT Count([tblTestScriptStatus].[StatusType]) AS Count2
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="OK"));

SELECT Count([tblTestScriptStatus].[StatusType]) AS Count3
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="PENDING"));

which are idenetical except for IMPLEMENTED, OK, and PENDING. What I'd
like to do is to combine these queries into one query giving the
following output table -

OK PENDING IMPLEMENTED
3 8 4

but I'm not sure how to go about it,

It would be even cooler if I didn't have to specify the status type
explicitly but could instead count the number of occurences for each
unique statusType in tblTestCase. For example, if I were to add the
status type "NOT OK".

Can you help?

Thanks for your help,

Barry.
 
G

Guest

Try this ---
TRANSFORM Count(tblTestScriptStatus.ID) AS CountOfID
SELECT tblTestScriptStatus.TestScriptStatusID
FROM tblTestScriptStatus INNER JOIN tblTestCase ON
tblTestScriptStatus.TestScriptStatusID = tblTestCase.TestScriptStatusID
GROUP BY tblTestScriptStatus.TestScriptStatusID
PIVOT tblTestScriptStatus.StatusType;
 
B

bg_ie

Try this ---
TRANSFORM Count(tblTestScriptStatus.ID) AS CountOfID
SELECT tblTestScriptStatus.TestScriptStatusID
FROM tblTestScriptStatus INNER JOIN tblTestCase ON
tblTestScriptStatus.TestScriptStatusID = tblTestCase.TestScriptStatusID
GROUP BY tblTestScriptStatus.TestScriptStatusID
PIVOT tblTestScriptStatus.StatusType;

--
KARL DEWEY
Build a little - Test a little



[email protected] said:
Hi,
Click to expand...
I have the following queries -
Click to expand...
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count1
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="IMPLEMENTED"));
Click to expand...
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count2
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="OK"));
Click to expand...
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count3
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="PENDING"));
Click to expand...
which are idenetical except for IMPLEMENTED, OK, and PENDING. What I'd
like to do is to combine these queries into one query giving the
following output table -
Click to expand...
OK PENDING IMPLEMENTED
3 8 4
Click to expand...
but I'm not sure how to go about it,
Click to expand...
It would be even cooler if I didn't have to specify the status type
explicitly but could instead count the number of occurences for each
unique statusType in tblTestCase. For example, if I were to add the
status type "NOT OK".
Click to expand...
Can you help?
Click to expand...
Thanks for your help,
Click to expand...
Barry.- Dölj citerad text -
Click to expand...

- Visa citerad text -
Click to expand...

Cool, thanks very much for your help.

I was wondering though if it is possible to get -

OK PENDING IMPLEMENTED
3 8 4

rather than -

ID OK PENDING IMPLEMENTED
1 3
2 8
3 4

which I get from your table.

Thanks,

Barry.
 
B

bg_ie

Try this ---
TRANSFORM Count(tblTestScriptStatus.ID) AS CountOfID
SELECT tblTestScriptStatus.TestScriptStatusID
FROM tblTestScriptStatus INNER JOIN tblTestCase ON
tblTestScriptStatus.TestScriptStatusID = tblTestCase.TestScriptStatusID
GROUP BY tblTestScriptStatus.TestScriptStatusID
PIVOT tblTestScriptStatus.StatusType;
Click to expand...
[email protected] said:
Hi,
I have the following queries -
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count1
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="IMPLEMENTED"));
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count2
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="OK"));
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count3
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="PENDING"));
which are idenetical except for IMPLEMENTED, OK, and PENDING. What I'd
like to do is to combine these queries into one query giving the
following output table -
OK PENDING IMPLEMENTED
3 8 4
but I'm not sure how to go about it,
It would be even cooler if I didn't have to specify the status type
explicitly but could instead count the number of occurences for each
unique statusType in tblTestCase. For example, if I were to add the
status type "NOT OK".
Can you help?
Thanks for your help,
Barry.- Dölj citerad text -
Click to expand...
Click to expand...
- Visa citerad text -
Click to expand...

Cool, thanks very much for your help.

I was wondering though if it is possible to get -

OK PENDING IMPLEMENTED
3 8 4

rather than -

ID OK PENDING IMPLEMENTED
1 3
2 8
3 4

which I get from your table.

Thanks,

Barry.- Dölj citerad text -

- Visa citerad text -
Click to expand...

The following -

SELECT Count(tblTestScriptStatus.StatusType) AS CountOfStatusType
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType) Is Not Null))
GROUP BY tblTestCase.TestScriptStatusID;

gives me the results in a single column as follows -

3
8
4

but I need the Status type name also and I can't figure out how to get
this -

OK 3
PENDING 8
IMPLEMENTED 4

Any suggestions?

Barry
 
B

bg_ie

On 15 Maj, 19:34, KARL DEWEY <[email protected]>
wrote:
Try this ---
TRANSFORM Count(tblTestScriptStatus.ID) AS CountOfID
SELECT tblTestScriptStatus.TestScriptStatusID
FROM tblTestScriptStatus INNER JOIN tblTestCase ON
tblTestScriptStatus.TestScriptStatusID = tblTestCase.TestScriptStatusID
GROUP BY tblTestScriptStatus.TestScriptStatusID
PIVOT tblTestScriptStatus.StatusType;
--
KARL DEWEY
Build a little - Test a little
:
Hi,
I have the following queries -
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count1
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="IMPLEMENTED"));
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count2
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="OK"));
SELECT Count([tblTestScriptStatus].[StatusType]) AS Count3
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType)="PENDING"));
which are idenetical except for IMPLEMENTED, OK, and PENDING. What I'd
like to do is to combine these queries into one query giving the
following output table -
OK PENDING IMPLEMENTED
3 8 4
but I'm not sure how to go about it,
It would be even cooler if I didn't have to specify the status type
explicitly but could instead count the number of occurences for each
unique statusType in tblTestCase. For example, if I were to add the
status type "NOT OK".
Can you help?
Thanks for your help,
Barry.- Dölj citerad text -
- Visa citerad text -
Click to expand...
Click to expand...
Cool, thanks very much for your help.
Click to expand...
I was wondering though if it is possible to get -
Click to expand...
OK PENDING IMPLEMENTED
3 8 4
Click to expand...
rather than -
Click to expand...
ID OK PENDING IMPLEMENTED
1 3
2 8
3 4
Click to expand...
which I get from your table.

Barry.- Dölj citerad text -
Click to expand...
- Visa citerad text -
Click to expand...

The following -

SELECT Count(tblTestScriptStatus.StatusType) AS CountOfStatusType
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType) Is Not Null))
GROUP BY tblTestCase.TestScriptStatusID;

gives me the results in a single column as follows -

3
8
4

but I need the Status type name also and I can't figure out how to get
this -

OK 3
PENDING 8
IMPLEMENTED 4

Any suggestions?

Barry- Dölj citerad text -

- Visa citerad text -
Click to expand...

Hurrah! I got it to work with the following -

SELECT tblTestScriptStatus.StatusType,
Count(tblTestScriptStatus.StatusType) AS CountOfStatusType
FROM tblTestScriptStatus RIGHT JOIN tblTestCase ON
tblTestScriptStatus.ID = tblTestCase.TestScriptStatusID
WHERE (((tblTestScriptStatus.StatusType) Is Not Null))
GROUP BY tblTestScriptStatus.StatusType;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query table not updating as expected 2
Combining Select Queries 7
Joining 3 Queries That Count 8
Still grappling with queries 5
Joining Queries 2
Combine 2 queries into 1 1
Cross Tab Query - Date Range fails 4
Joining two Queries to get ALL data 2

Top