sub query count

[Souris]

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID = tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID = tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am unable to
figure out.

Your information is great apreciated,

 

[Dale Fye]

Forget about your query for now, describe your table structure (both tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you expect
the outcome of the query to give you based on the sample data you provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a typo?

Dale

 

[Souris]

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,

Forget about your query for now, describe your table structure (both tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you expect
the outcome of the query to give you based on the sample data you provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID = tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID = tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am unable to
figure out.

Your information is great apreciated,

 

[Dale Fye]

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,

 

[Souris]

Thanks millions,
It works,
The meeting date is actual meeting date, activity date is the date user
created both are in the Details table.

Thanks millions again,

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,

 

[Souris]

Is it possible to link Details table 2 for otther activity?
I tried to to inner join but it does not work like following

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) + SUM(IIF(F.ActivtyDate BETWEEN [Start date?]
AND [End date?]
AND F.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D
INNER JOIN tbl_Employees D ON D.Employee_ID = E.EmployeeID
INNER JOIN tbl_Detais2 F ON D.Employee_ID = F.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

Thanks again,

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,

 

[Dale Fye]

Are you telling me you have a second details table? If so, what is different
between it and the first?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Is it possible to link Details table 2 for otther activity?
I tried to to inner join but it does not work like following

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) + SUM(IIF(F.ActivtyDate BETWEEN [Start date?]
AND [End date?]
AND F.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D
INNER JOIN tbl_Employees D ON D.Employee_ID = E.EmployeeID
INNER JOIN tbl_Detais2 F ON D.Employee_ID = F.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

Thanks again,

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,



:

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,

 

[Souris]

There 2 types of activities for employee, so there are in 2 tables.
The key fields are the same but the orthers are complete different.

Thanks again,

Are you telling me you have a second details table? If so, what is different
between it and the first?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Is it possible to link Details table 2 for otther activity?
I tried to to inner join but it does not work like following

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) + SUM(IIF(F.ActivtyDate BETWEEN [Start date?]
AND [End date?]
AND F.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D
INNER JOIN tbl_Employees D ON D.Employee_ID = E.EmployeeID
INNER JOIN tbl_Detais2 F ON D.Employee_ID = F.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

Thanks again,

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,



:

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,

 

[Souris]

It is OK to have 2 queries to do the union query after.
If it is possible then I just prefer to have only one query to do the work.

Thanks again,

Are you telling me you have a second details table? If so, what is different
between it and the first?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Is it possible to link Details table 2 for otther activity?
I tried to to inner join but it does not work like following

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) + SUM(IIF(F.ActivtyDate BETWEEN [Start date?]
AND [End date?]
AND F.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D
INNER JOIN tbl_Employees D ON D.Employee_ID = E.EmployeeID
INNER JOIN tbl_Detais2 F ON D.Employee_ID = F.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

Thanks again,

Still not entirely sure where you get "MeetingDate" in your original query.

If what you want to do is count for each day/employee combination, the
number of MeetingType = 9 meetings they attended, you could use:

SELECT D.ActivityDate, E.[Last Name], E.[First Name],
SUM(IIF(D.ActivtyDate BETWEEN [Start date?] AND [End date?]
AND D.MeetingType = 9, 1, 0)) as Type9Meetings
FROM tblDetails D INNER JOIN tbl_Employees D
ON D.Employee_ID = E.EmployeeID
GROUP BY D.ActivityDate, E.[Last Name], E.[First Name]

HTH
Dale

Thanks for the message,
Yes, it is a typo on the table name
Actually, I call it ActivityDetails which means is a child of Employee
table
start_day and End_day is user enter information

employee table

Employee_ID First Name Last Name
1 FirstName1 LastName1
2 FisrtName2 LastName2
3 FisrtName3 LastName3

tblDetails

Emplyee_ID ActivityDate MeetingType
1 04/03/2008 1
1 04/03/2008 2
2 04/01/2008 9
3 04/05/2008 9

Basically, there are 2 tables in the query.
One employee can have different meetings (more than one) on the same date.

Thanks again for helping,



:

Forget about your query for now, describe your table structure (both
tables,
pertinent fields only) and tell us what you are trying to do, in words.

Give us a sample of a couple of records from each table, and what you
expect
the outcome of the query to give you based on the sample data you
provide.

It looks like you are trying to determine how many meetings of type = 9,
each of your employees attended on a particular activity date.
I don't understand the relationship between activity date (what table is
it
in) and MeetingDate. Are [Start_Date] and [End_Date] parameters or are
they
fields in one of your tables.

In your second to last line, you mentioned table tblEDDetails, is that a
typo?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

SELECT ActivityDate, tblEmployee.first_name,
(Select count(*) from tblDetails
INNER JOIN tblEmployee on tblDetails.Employee_ID =
tblEmployee.Employee_ID
where
tblDetails.type = 9 and tblDetails.meetingdate between [Start_date] and
[end_date] ) as MyValue
from tblDetails
INNER JOIN tblEmployee on tblEDDetails.Employee_ID =
tblEmployee.Employee_ID
WHERE tblDetails.MeetingDate between [Start_date] and [End_date]

I have above query to get count per every employee from the subquery.
The above sub query gives me the count for all employees.

I beleive that there are some where link is not correct, but I am
unable to
figure out.

Your information is great apreciated,