Writing Query with criteria

[acdevteam]

Hello Everyone,

We are a dev team very new to Access, and so far we have gotten
excellent support from this group. We have a question about writing a
query. Here is the situation:

We have a query that pulls data from a detail table and displays the
total for 1 column.

DetailTable:
ProjectID (not unique)
EffectiveDate
TotalSalary

Query:
ProjectID (can have multiple records for the same ID)
EffectiveDate
SumofTotalSalary

That works. Simple enough!

Now, what we want is a query (may be we need another query or modify
this one), that:

A = Calculate days from effective date (Date minus EffectiveDate)
B = Multiply A with TotalSalary for each record
Show single record for each Project ID with the total of all Bs for
this ProjectID

Here is the query we have for the first part:
SELECT DISTINCTROW tblPersonnel.ProjectID, tblPersonnel.EffDate,
Sum(tblPersonnel.TotalSalary) AS [Sum Of TotalSalary]
FROM tblPersonnel
GROUP BY tblPersonnel.ProjectID, tblPersonnel.EffDate;
Here is the result we get:

"ProjectID","EffDate","Sum Of TotalSalary"
"J2006",8/1/2005 0:00:00,$115320.00
"P001",9/9/2006 0:00:00,$95020.00
"P007",1/1/2007 0:00:00,$82620.00
"P102",1/1/2007 0:00:00,$69300.00
"P110",1/1/2008 0:00:00,$55800.00

Thanks in advance for looking into this. You all are awsome.

Puneet.

[Bob Quintal]

ac*******@gmail.com wrote in
news:11**********************@i42g2000cwa.googlegr oups.com:

Hello Everyone,

Now, what we want is a query (may be we need another query or
modify this one), that:

A = Calculate days from effective date (Date minus
EffectiveDate) B = Multiply A with TotalSalary for each record
Show single record for each Project ID with the total of all
Bs for this ProjectID

Here is the query we have for the first part:
SELECT DISTINCTROW tblPersonnel.ProjectID,
tblPersonnel.EffDate, Sum(tblPersonnel.TotalSalary) AS [Sum Of
TotalSalary] FROM tblPersonnel
GROUP BY tblPersonnel.ProjectID, tblPersonnel.EffDate;
Here is the result we get:

"ProjectID","EffDate","Sum Of TotalSalary"
"J2006",8/1/2005 0:00:00,$115320.00
"P001",9/9/2006 0:00:00,$95020.00
"P007",1/1/2007 0:00:00,$82620.00
"P102",1/1/2007 0:00:00,$69300.00
"P110",1/1/2008 0:00:00,$55800.00

Thanks in advance for looking into this. You all are awsome.

For the first step, you use the datediff() function.
datediff("d", effdate, date()). the "d" tells the function that
you want the difference in days. You could get months, weeks or
even seconds.

Note that since some of your dates are future, you'll want to
figure out some logic to exclude those dates else you'll have
negative days, and negative amounts..

An issue is the period of Salary. if it's by year, you will need
to divide by 365.

Then you insert all these expressions in the sum() function
above.

something like
sum(tblPersonnel.TotalSalary * iif(effdate date(), 0, datediff
( "d", effdate, date()) / 365) As [Sum Of Total Salary]

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[acdevteam]

Hi Bob,

That worked like a charm. Thanks.

However, I hvae a little complication to add, and I will explain by
example:
tblPersonnel:
ProjectID, ResourceID, BaseSalary, EffDate, PercentEffort, EffSalary,
Fringe, TotalSalary
P001, R990, 50000, 1/1/2006, 100, 50000, 10000, 60000
P001, R991, 60000, 1/1/2006, 100, 70000, 13000, 83000
P001, R990, 50000, 7/1/2006, 50, 25000, 10000, 35000

As you see, what is happening in this table via forms is --
In the beginning, R990 and R991 are at 100% effort on this project. So
their BaseSalary and EffSalary are same. Add some Fringe to it, and you
get the TotalSalary.

Now, effective 7/1/2006, R990 is changed from 100% to 50% effort.
Hence, the BaseSalary for this resource is down to 25000 and the
TotalSalary is 35000.

(I am creating a separate record every time the % Effort for the
resource is changed)

What I need to calculate is the total burn till date on the project for
all resources over the life of the project. So, if today is 8/3/2006,
it should come up to:
(60000/365) * DateDiff("d", 1/1/2006, 6/30/2006) +
(83000/365) * DateDiff("d", 1/1/2006, 8/3/2006) +
(35000/365) * DateDiff("d", 7/1/2006, 8/3/2006)

I just cant seem to figure out how to write this in the query. so for
example, in the first row of my sample calc above, how do I know if the
resource has another record from 7/1/2006, so only do 1/1 to 6/30....

Thanks again,
Puneet.
Bob Quintal wrote:

ac*******@gmail.com wrote in
news:11**********************@i42g2000cwa.googlegr oups.com:

Hello Everyone,

Now, what we want is a query (may be we need another query or
modify this one), that:

A = Calculate days from effective date (Date minus
EffectiveDate) B = Multiply A with TotalSalary for each record
Show single record for each Project ID with the total of all
Bs for this ProjectID

Here is the query we have for the first part:
SELECT DISTINCTROW tblPersonnel.ProjectID,
tblPersonnel.EffDate, Sum(tblPersonnel.TotalSalary) AS [Sum Of
TotalSalary] FROM tblPersonnel
GROUP BY tblPersonnel.ProjectID, tblPersonnel.EffDate;
Here is the result we get:

"ProjectID","EffDate","Sum Of TotalSalary"
"J2006",8/1/2005 0:00:00,$115320.00
"P001",9/9/2006 0:00:00,$95020.00
"P007",1/1/2007 0:00:00,$82620.00
"P102",1/1/2007 0:00:00,$69300.00
"P110",1/1/2008 0:00:00,$55800.00

Thanks in advance for looking into this. You all are awsome.

For the first step, you use the datediff() function.
datediff("d", effdate, date()). the "d" tells the function that
you want the difference in days. You could get months, weeks or
even seconds.

Note that since some of your dates are future, you'll want to
figure out some logic to exclude those dates else you'll have
negative days, and negative amounts..

An issue is the period of Salary. if it's by year, you will need
to divide by 365.

Then you insert all these expressions in the sum() function
above.

something like
sum(tblPersonnel.TotalSalary * iif(effdate date(), 0, datediff
( "d", effdate, date()) / 365) As [Sum Of Total Salary]

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

ac*******@gmail.com wrote in
news:11**********************@p79g2000cwp.googlegr oups.com:

Hi Bob,

That worked like a charm. Thanks.

However, I hvae a little complication to add, and I will
explain by example:
tblPersonnel:
ProjectID, ResourceID, BaseSalary, EffDate, PercentEffort,
EffSalary, Fringe, TotalSalary
P001, R990, 50000, 1/1/2006, 100, 50000, 10000, 60000
P001, R991, 60000, 1/1/2006, 100, 70000, 13000, 83000
P001, R990, 50000, 7/1/2006, 50, 25000, 10000, 35000

As you see, what is happening in this table via forms is --
In the beginning, R990 and R991 are at 100% effort on this
project. So their BaseSalary and EffSalary are same. Add some
Fringe to it, and you get the TotalSalary.

Now, effective 7/1/2006, R990 is changed from 100% to 50%
effort. Hence, the BaseSalary for this resource is down to
25000 and the TotalSalary is 35000.

(I am creating a separate record every time the % Effort for
the resource is changed)

What I need to calculate is the total burn till date on the
project for all resources over the life of the project. So, if
today is 8/3/2006, it should come up to:
(60000/365) * DateDiff("d", 1/1/2006, 6/30/2006) +
(83000/365) * DateDiff("d", 1/1/2006, 8/3/2006) +
(35000/365) * DateDiff("d", 7/1/2006, 8/3/2006)

I just cant seem to figure out how to write this in the query.
so for example, in the first row of my sample calc above, how
do I know if the resource has another record from 7/1/2006, so
only do 1/1 to 6/30....

Thanks again,
Puneet.

So what you need is a second date in the table. Instead of
EffDate, you need dteEffFrom and dteEffThru

Then you do another Iif to determine if dteEffThru is less than
today's date, and use that instead of today's date.
Then you sum the results.

-
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com