create one table from six queries

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

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

(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

"F0zziebear" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

(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

"F0zziebear" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

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" wrote:

(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

"F0zziebear" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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

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" wrote:

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" wrote:

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" wrote:

(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

"F0zziebear" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Please post the SQL for the six queries

-Dorian

"F0zziebear" wrote:

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