create one table from six queries

G

Guest

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
 
G

Guest

Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
 
G

Guest

Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


F0zziebear said:
Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
mscertified said:
Please post the SQL for the six queries

-Dorian
Click to expand...
Click to expand...
 
G

Guest

Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

mscertified said:
Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


F0zziebear said:
Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
mscertified said:
Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dorian,

Had a chance to run the scrip and it works. However, a couple of problems.

When I came to import the data into excel the following message comes up:
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 10.
comes up, Do you know why this is happening? And how I can resolve this?

Secondly I ideally want to create a table/data to import that looks like this

Column1 Column2 Column3
CSRV1365+CSRV2365 CSRV190+CSRV290 CSRV131+CSRV231

Hope you can help as I don't really understand SQL yet

Many thanks

F0zz

F0zziebear said:
Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

mscertified said:
Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


F0zziebear said:
Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
:

Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

(1) No idea, sounds like an Excel issue not Access
(2) Combine each pair of queries into one as follows:
UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS" OR HEALTHCHECKS.Reviewer2)="CS"));
Click to expand...
Click to expand...
So you will end up with 3 queries being UNIONed instead of 6.
-Dorian

F0zziebear said:
Dorian,

Had a chance to run the scrip and it works. However, a couple of problems.

When I came to import the data into excel the following message comes up:
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 10.
comes up, Do you know why this is happening? And how I can resolve this?

Secondly I ideally want to create a table/data to import that looks like this

Column1 Column2 Column3
CSRV1365+CSRV2365 CSRV190+CSRV290 CSRV131+CSRV231

Hope you can help as I don't really understand SQL yet

Many thanks

F0zz

F0zziebear said:
Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

mscertified said:
Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


:

Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
:

Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dorian,

Syntax error
It's saying there is an extra ) in the expression. I've tried various
combinations but it isn't liking your new script. Can you see where the error
is?

F0zz

mscertified said:
(1) No idea, sounds like an Excel issue not Access
(2) Combine each pair of queries into one as follows:
UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS" OR HEALTHCHECKS.Reviewer2)="CS"));
Click to expand...
Click to expand...
So you will end up with 3 queries being UNIONed instead of 6.
-Dorian

F0zziebear said:
Dorian,

Had a chance to run the scrip and it works. However, a couple of problems.

When I came to import the data into excel the following message comes up:
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 10.
comes up, Do you know why this is happening? And how I can resolve this?

Secondly I ideally want to create a table/data to import that looks like this

Column1 Column2 Column3
CSRV1365+CSRV2365 CSRV190+CSRV290 CSRV131+CSRV231

Hope you can help as I don't really understand SQL yet

Many thanks

F0zz

F0zziebear said:
Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

:

Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


:

Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
:

Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dorian,

i have worked out that there was a brack missing in front of the 2nd
healthcheck expression. Is it possible to design how the information creates
a table as the 3 queries would relate to one person and I want to create a
table for about 17 people

Yours

F0zz

F0zziebear said:
Dorian,

Syntax error
It's saying there is an extra ) in the expression. I've tried various
combinations but it isn't liking your new script. Can you see where the error
is?

F0zz

mscertified said:
(1) No idea, sounds like an Excel issue not Access
(2) Combine each pair of queries into one as follows:
UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS" OR HEALTHCHECKS.Reviewer2)="CS"));
Click to expand...
So you will end up with 3 queries being UNIONed instead of 6.
-Dorian

F0zziebear said:
Dorian,

Had a chance to run the scrip and it works. However, a couple of problems.

When I came to import the data into excel the following message comes up:
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 10.
comes up, Do you know why this is happening? And how I can resolve this?

Secondly I ideally want to create a table/data to import that looks like this

Column1 Column2 Column3
CSRV1365+CSRV2365 CSRV190+CSRV290 CSRV131+CSRV231

Hope you can help as I don't really understand SQL yet

Many thanks

F0zz

:

Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

:

Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


:

Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
:

Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry about the syntax error, I'm not sure how that happened. I can see
another problem with what I gave you. The 'Count' clause is counting a
specific reviewer and I did not change it in all cases. If you want both
counts added you will have to change each 'Count' clause to:

Count(HEALTHCHECKS.Reviewer1) + Count(HEALTHCHECKS.Reviewer2) AS
CountOfReviewer

The whole way this is set up assumes there will be 2 and only 2 reviewers.
Of course, that is a terrible design. It might be better to do the counting
in Excel after you have exported the data.
When you say 17 people, do you mean 17 reviewers?
If so, you dont want a column for each reviewer, you want one column for the
count and one column for the reviewer name. So you get each reviewers count
in the same column.

-Dorian

F0zziebear said:
Dorian,

i have worked out that there was a brack missing in front of the 2nd
healthcheck expression. Is it possible to design how the information creates
a table as the 3 queries would relate to one person and I want to create a
table for about 17 people

Yours

F0zz

F0zziebear said:
Dorian,

Syntax error
It's saying there is an extra ) in the expression. I've tried various
combinations but it isn't liking your new script. Can you see where the error
is?

F0zz

mscertified said:
(1) No idea, sounds like an Excel issue not Access
(2) Combine each pair of queries into one as follows:
UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS" OR HEALTHCHECKS.Reviewer2)="CS"));

So you will end up with 3 queries being UNIONed instead of 6.
-Dorian

:

Dorian,

Had a chance to run the scrip and it works. However, a couple of problems.

When I came to import the data into excel the following message comes up:
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 10.
comes up, Do you know why this is happening? And how I can resolve this?

Secondly I ideally want to create a table/data to import that looks like this

Column1 Column2 Column3
CSRV1365+CSRV2365 CSRV190+CSRV290 CSRV131+CSRV231

Hope you can help as I don't really understand SQL yet

Many thanks

F0zz

:

Dorian,

I will give it a go on Monday and let you know how I get on. Thanks for the
help in the meantime

F0zz

:

Your 6 queries are creating 6 tables (named in the INTO clauses). If you
want them all to go into the SAME table you will need a way to distinguish
which data is which (I added a new column to the front of each query) also
made the second column the same name in each query, you can then use UNION
clause to combine all the queries into one. Something like:

SELECT "CSRV131" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV231" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV190" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV290" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

UNION
SELECT "CSRV1365" As CSType, Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

UNION
SELECT "CSRV2365" As CSType, Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

You can then create another make table query reading in the above query:
SELECT * FROM AboveQuery INTO [MyTable]

Hope this is something like what you needed.

-Dorian


:

Query1
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query2
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 31]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"31") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query3
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query4
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV2 90]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"90") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Query5
SELECT Count(HEALTHCHECKS.Reviewer1) AS CountOfReviewer1 INTO [CS RV1 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer1)="CS"));

Query6
SELECT Count(HEALTHCHECKS.Reviewer2) AS CountOfReviewer2 INTO [CS RV2 365]
FROM HEALTHCHECKS INNER JOIN PROJECTS ON HEALTHCHECKS.[Project name] =
PROJECTS.[Project name]
HAVING (((HEALTHCHECKS.[Actual Date]) Between Date() And Date()-"365") AND
((HEALTHCHECKS.Reviewer2)="CS"));

Please note that queries 1,3,5 come from the same column in the table, but
use different date ranges, and the same applies for queries 2,4,6

I then also want to add queries 1&2,3&4,5&6 to create 3 columns which I will
then import into an excel sheet which I use for reporting.

I hope this is enough information, otherwise please ask for more. I am self
taught and therefore have limited knowledge so please try and explain any
IT/techny SQL type language to guide me through.

Many thanks in advance

F0zz
:

Please post the SQL for the six queries

-Dorian

:

I have an excel report which draws data from access. The excel table looks
like this:
Each project is reviewed by two people

Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months
AA x y x+y

This is repeated for 3 months and 1 month (so the querry uses a date between
to work that out)
x is a create-tabel query and so is y. I want to create one table for these
queries (in total 6 per reviewer) and import that table into excel. I would
also need to add the reviewer 1 and 2 in the table so as to import it in one
go.

Can you help on how to create a table from these six queries, or is there
another way of doing this?

Let me know if you need further clarification
Click to expand...
Click to expand...
Click to expand...
 

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

Update access table to sql table 1
Impossible Query.. 9
How to export table from outlook into ms access table? 0
Make one final list from four sources 1
Multiple make table queries in one? 3
Merging queries by column in access 1
getting Tags ID used in queries 3
Queries losing fieldnames when importing from external mdb 2

Top