create one table from six queries

F

F0zziebear

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
 
F

F0zziebear

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
 
M

mscertified

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...
 
F

F0zziebear

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...
 
F

F0zziebear

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...
 
M

mscertified

(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...
 
F

F0zziebear

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...
 
F

F0zziebear

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...
 

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

make a query from several others or.... 0
Make one final list from four sources 1
Construct table from selected rows 4
Update access table to sql table 1
Using queries from multiple tables to create a report 2
Multiple make table queries in one? 3
Merging queries by column in access 1
Queries losing fieldnames when importing from external mdb 2

Top