Calculation in Query

[Guest]

I have a query where I need to bring data in from 3 different tables.

EmployeeData (*EmployeeID, FName, LName, Status, Rate)
EmployeeProjectData (*EmployeeID, *ProjectID, *CycleDate, Total Hours)
ProjectData (*ProjectID, ProjectName, ProjectType)
(* indicates primary key)

I want my end results of the query to be:

Employee ID - ProjectID - 1/15/2007 - 1/31/2007 - 2/15/2007 - etc(to end of
year)

So, there could be multiple instances of the same EmployeeID and ProjectID
in the [EmployeeProjectData] table, but they would have a different cycle
date. How do I create a "query" under each of the dates so that these
multiple instances of the same EmployeeID and ProjectID are essentially
"squished" into one line on a query that shows the total hours for each cycle?

Thanks for your help!!!

 

[kingston via AccessMonster.com]

Do you have a table with a list of cycle start dates and cycle end dates? If
so, create a totals query based on that and EmployeeProjectData:

Group By [EmployeeID], [ProjectID], [CycleStart]
Sum on [Total Hours]
Where [CycleDate] Between [CycleStart] And [CycleEnd]

Somehow, you need to tell Access what your ranges are.

I have a query where I need to bring data in from 3 different tables.

EmployeeData (*EmployeeID, FName, LName, Status, Rate)
EmployeeProjectData (*EmployeeID, *ProjectID, *CycleDate, Total Hours)
ProjectData (*ProjectID, ProjectName, ProjectType)
(* indicates primary key)

I want my end results of the query to be:

Employee ID - ProjectID - 1/15/2007 - 1/31/2007 - 2/15/2007 - etc(to end of
year)

So, there could be multiple instances of the same EmployeeID and ProjectID
in the [EmployeeProjectData] table, but they would have a different cycle
date. How do I create a "query" under each of the dates so that these
multiple instances of the same EmployeeID and ProjectID are essentially
"squished" into one line on a query that shows the total hours for each cycle?

Thanks for your help!!!

 

[Guest]

The CycleDate will always be the 15th of each month or the last day of each
month. It won't be a range. So, there are 24 separate columns that I would
like to have data for for every instance of EmployeeID and ProjectID that are
the same.
How do I put the SQL code that you wrote below in for the calculation on
each Cycle Date? Can you put SQL code in a calculation within a query, or do
I need to build a query outside of my main one and then put the specific
CycleDate in for the criteria?

Thanks again for your help!!

Do you have a table with a list of cycle start dates and cycle end dates? If
so, create a totals query based on that and EmployeeProjectData:

Group By [EmployeeID], [ProjectID], [CycleStart]
Sum on [Total Hours]
Where [CycleDate] Between [CycleStart] And [CycleEnd]

Somehow, you need to tell Access what your ranges are.

I have a query where I need to bring data in from 3 different tables.

EmployeeData (*EmployeeID, FName, LName, Status, Rate)
EmployeeProjectData (*EmployeeID, *ProjectID, *CycleDate, Total Hours)
ProjectData (*ProjectID, ProjectName, ProjectType)
(* indicates primary key)

I want my end results of the query to be:

Employee ID - ProjectID - 1/15/2007 - 1/31/2007 - 2/15/2007 - etc(to end of
year)

So, there could be multiple instances of the same EmployeeID and ProjectID
in the [EmployeeProjectData] table, but they would have a different cycle
date. How do I create a "query" under each of the dates so that these
multiple instances of the same EmployeeID and ProjectID are essentially
"squished" into one line on a query that shows the total hours for each cycle?

Thanks for your help!!!

 

[Guest]

I think this is what you want. If not correct then post back.

TRANSFORM Sum(EmployeeProjectData.[Total Hours]) AS [SumOfTotal Hours]
SELECT EmployeeProjectData.EmployeeID, EmployeeProjectData.ProjectID
FROM EmployeeProjectData
GROUP BY EmployeeProjectData.EmployeeID, EmployeeProjectData.ProjectID
PIVOT Format([CycleDate],"Short Date");

The CycleDate will always be the 15th of each month or the last day of each
month. It won't be a range. So, there are 24 separate columns that I would
like to have data for for every instance of EmployeeID and ProjectID that are
the same.
How do I put the SQL code that you wrote below in for the calculation on
each Cycle Date? Can you put SQL code in a calculation within a query, or do
I need to build a query outside of my main one and then put the specific
CycleDate in for the criteria?

Thanks again for your help!!

Do you have a table with a list of cycle start dates and cycle end dates? If
so, create a totals query based on that and EmployeeProjectData:

Group By [EmployeeID], [ProjectID], [CycleStart]
Sum on [Total Hours]
Where [CycleDate] Between [CycleStart] And [CycleEnd]

Somehow, you need to tell Access what your ranges are.

I have a query where I need to bring data in from 3 different tables.

EmployeeData (*EmployeeID, FName, LName, Status, Rate)
EmployeeProjectData (*EmployeeID, *ProjectID, *CycleDate, Total Hours)
ProjectData (*ProjectID, ProjectName, ProjectType)
(* indicates primary key)

I want my end results of the query to be:

Employee ID - ProjectID - 1/15/2007 - 1/31/2007 - 2/15/2007 - etc(to end of
year)

So, there could be multiple instances of the same EmployeeID and ProjectID
in the [EmployeeProjectData] table, but they would have a different cycle
date. How do I create a "query" under each of the dates so that these
multiple instances of the same EmployeeID and ProjectID are essentially
"squished" into one line on a query that shows the total hours for each cycle?

Thanks for your help!!!

 

[kingston via AccessMonster.com]

I think I understand the data structure now, and I think you should use a
crosstab query. The row headers will be the EmployeeID and ProjectID. The
column headers will be the CycleDates. The data will be the sum of Total
Hours.

The CycleDate will always be the 15th of each month or the last day of each
month. It won't be a range. So, there are 24 separate columns that I would
like to have data for for every instance of EmployeeID and ProjectID that are
the same.
How do I put the SQL code that you wrote below in for the calculation on
each Cycle Date? Can you put SQL code in a calculation within a query, or do
I need to build a query outside of my main one and then put the specific
CycleDate in for the criteria?

Thanks again for your help!!

Do you have a table with a list of cycle start dates and cycle end dates? If
so, create a totals query based on that and EmployeeProjectData:

[quoted text clipped - 24 lines]

 

[iliace]

If the cycle is fixed length with respect to the calendar month (e.g.
CycleStart always on the first of the month) you can use a formula
instead:

[...]
WHERE [CycleDate] Between
DateSerial(Year([CycleDate]),Month([CycleDate]),1) And
DateSerial(Year([CycleDate]),Month([CycleDate]),Choose(Month([CycleDate],31,28,31,30,31,30,31,31,30,31,30,31))

You can insert an IIF there to check for leap year too.

You would need a table of start/end dates if you have something like a
strictly bi-weekly cycle.

Hopefully that provides an idea of how you can make queries dynamic.

Do you have a table with a list of cycle start dates and cycle end dates? If
so, create a totals query based on that and EmployeeProjectData:

Group By [EmployeeID], [ProjectID], [CycleStart]
Sum on [Total Hours]
Where [CycleDate] Between [CycleStart] And [CycleEnd]

Somehow, you need to tell Access what your ranges are.

I have a query where I need to bring data in from 3 different tables.

EmployeeData (*EmployeeID, FName, LName, Status, Rate)
EmployeeProjectData (*EmployeeID, *ProjectID, *CycleDate, Total Hours)
ProjectData (*ProjectID, ProjectName, ProjectType)
(* indicates primary key)

I want my end results of the query to be:

Employee ID - ProjectID - 1/15/2007 - 1/31/2007 - 2/15/2007 - etc(to end of
year)

So, there could be multiple instances of the same EmployeeID and ProjectID
in the [EmployeeProjectData] table, but they would have a different cycle
date. How do I create a "query" under each of the dates so that these
multiple instances of the same EmployeeID and ProjectID are essentially
"squished" into one line on a query that shows the total hours for each cycle?

Thanks for your help!!!