SQL View Exression

G

Guest

Hi...

With the help of Mr. Foster I have created a expression as follows.

SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum([Cy2k2ReportT501Hrs]) FROM [Year2005 Pilot Info]
WHERE FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

What I am trying to achieve is to add the hours in "Cy2k2ReportT501Hrs"
field until I reach 25 hours and give me cooresponding date located in the
"FlightDate" field. The result it is giving me is a date but it is not 25
hours, it is less.

Any help someone can offer would be greatly appreciated.

Thank you,
Lee
 
G

Gary Walter

Lee said:
With the help of Mr. Foster I have created a expression as follows.

SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum([Cy2k2ReportT501Hrs]) FROM [Year2005 Pilot Info]
WHERE FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

What I am trying to achieve is to add the hours in "Cy2k2ReportT501Hrs"
field until I reach 25 hours and give me cooresponding date located in the
"FlightDate" field. The result it is giving me is a date but it is not 25
hours, it is less.
Click to expand...
Hi Lee,

First (meaning no offense), but MG is one of the
premier contributors on this newsgroup, and my
guess is that if you had continued this discussion
in the previous thread, Mr. Foster would have
likely *immediately* responded with more assistance.

The only thing I can think of is that you may want
your group to include a PilotID (or PilotName)...
something like?

SELECT A.FlightDate, A.PilotName
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate, A.PilotName
HAVING
((((SELECT Sum(t.[Cy2k2ReportT501Hrs])
FROM [Year2005 Pilot Info] As t
WHERE
t.FlightDate <= A.FlightDate
AND
t.PilotName = A.PilotName)
Mod 25)=0))
ORDER BY A.FlightDate DESC;

If your [Year2005 PilotInfo] table (query?)
contained more than one pilot, then your
query above would probably hit 25 hours
total on a date when a *pilot* did not have
a full 25 hours.

Does that make sense in your situation?

Another thought was how are you storing
the hours? I made a small test table called
"tblLee" to test various number types.
FlightDate FloatHours LongHours CurHours
5/1/2005 4.5 5 4.5
5/2/2005 5.1 5 5.1
5/3/2005 4.2 4 4.2
5/4/2005 6.2 6 6.2
5/5/2005 2 2 2
5/6/2005 3 3 3
5/7/2005 1.1 1 1.1


and ran following query

SELECT A.FlightDate
FROM tblLee AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum(t.[FloatHours]) FROM tblLee As t
WHERE t.FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

and got what one would expect.

FlightDate
5/6/2005


It may be that I just got lucky on summing the
limited float hours. Arithmetic on floats can be
a big gotcha. I usually create a wrapper function
around CDec() so I can use this function in
a query (maybe that has changed in later versions
of Access) to change the floats to Decimal and
expect any arithmetic to work.


I also ran this check query (you don't say
what type of field the Hours are stored in):

SELECT
t.FlightDate,
t.FloatHours,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) AS SumPrevFloat,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) Mod 25 AS MOD25Float,
t.LongHours,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) AS SumPrevLong,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) Mod 25 AS MOD25Long,
t.CurHours,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) AS SumPrevCur,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) Mod 25 AS MOD25Cur
FROM tblLee AS t;

and got

FlightDate FloatHours SumPrevFloat MOD25Float LongHours SumPrevLong
MOD25Long CurHours SumPrevCur MOD25Cur
5/1/2005 4.5 4.5 4 5 5 5 4.5 $4.50 4
5/2/2005 5.1 9.6 10 5 10 10 5.1 $9.60 10
5/3/2005 4.2 13.8 14 4 14 14 4.2 $13.80 14
5/4/2005 6.2 20 20 6 20 20 6.2 $20.00 20
5/5/2005 2 22 22 2 22 22 2 $22.00 22
5/6/2005 3 25 0 3 25 0 3 $25.00 0
5/7/2005 1.1 26.1 1 1 26 1 1.1 $26.10 1


All seemed well.

Is there any difference in what you are
working with, versus the simple example
above, i.e., are "FlightDate" values actually
type Date versus formatted strings, etc.?

good luck,

gary
 
G

Guest

Hi Thank you for your help....I am new to this forum and learning as I go
along.
I ran your simple query as follows:
SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum(t.[p1-pic-day]) FROM [Year2005 Pilot Info] As t
WHERE t.FlightDate <= A.FlightDate) Mod 20)=0))
ORDER BY A.FlightDate DESC;

The field of p1-pic-day has entries of 0, 2.0, 0.6, 1.1, 0.2 and etc. I ran
this query and it goes almost to the last entry which is a zero which miss
the 20 hour total by a long shot. If you like i can email you the entire
field of numbers which is apporx 36 entries.

Let me tell you what I do to arrive at this information. I run a query for
a particular pilot which will give me information for the past 180 days.
There are several fields in this query that has hours posted for whatever
they did in that particular category. From one of those fields i need to add
the column from the most recent entry date until i accumlate 20 hours from
then i would like the corresponding date associated with that value.

Again thank you for your help on this.

Lee



Gary Walter said:
Lee said:
With the help of Mr. Foster I have created a expression as follows.

SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum([Cy2k2ReportT501Hrs]) FROM [Year2005 Pilot Info]
WHERE FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

What I am trying to achieve is to add the hours in "Cy2k2ReportT501Hrs"
field until I reach 25 hours and give me cooresponding date located in the
"FlightDate" field. The result it is giving me is a date but it is not 25
hours, it is less.
Click to expand...
Hi Lee,

First (meaning no offense), but MG is one of the
premier contributors on this newsgroup, and my
guess is that if you had continued this discussion
in the previous thread, Mr. Foster would have
likely *immediately* responded with more assistance.

The only thing I can think of is that you may want
your group to include a PilotID (or PilotName)...
something like?

SELECT A.FlightDate, A.PilotName
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate, A.PilotName
HAVING
((((SELECT Sum(t.[Cy2k2ReportT501Hrs])
FROM [Year2005 Pilot Info] As t
WHERE
t.FlightDate <= A.FlightDate
AND
t.PilotName = A.PilotName)
Mod 25)=0))
ORDER BY A.FlightDate DESC;

If your [Year2005 PilotInfo] table (query?)
contained more than one pilot, then your
query above would probably hit 25 hours
total on a date when a *pilot* did not have
a full 25 hours.

Does that make sense in your situation?

Another thought was how are you storing
the hours? I made a small test table called
"tblLee" to test various number types.
FlightDate FloatHours LongHours CurHours
5/1/2005 4.5 5 4.5
5/2/2005 5.1 5 5.1
5/3/2005 4.2 4 4.2
5/4/2005 6.2 6 6.2
5/5/2005 2 2 2
5/6/2005 3 3 3
5/7/2005 1.1 1 1.1


and ran following query

SELECT A.FlightDate
FROM tblLee AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum(t.[FloatHours]) FROM tblLee As t
WHERE t.FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

and got what one would expect.

FlightDate
5/6/2005


It may be that I just got lucky on summing the
limited float hours. Arithmetic on floats can be
a big gotcha. I usually create a wrapper function
around CDec() so I can use this function in
a query (maybe that has changed in later versions
of Access) to change the floats to Decimal and
expect any arithmetic to work.


I also ran this check query (you don't say
what type of field the Hours are stored in):

SELECT
t.FlightDate,
t.FloatHours,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) AS SumPrevFloat,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) Mod 25 AS MOD25Float,
t.LongHours,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) AS SumPrevLong,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) Mod 25 AS MOD25Long,
t.CurHours,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) AS SumPrevCur,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) Mod 25 AS MOD25Cur
FROM tblLee AS t;

and got

FlightDate FloatHours SumPrevFloat MOD25Float LongHours SumPrevLong
MOD25Long CurHours SumPrevCur MOD25Cur
5/1/2005 4.5 4.5 4 5 5 5 4.5 $4.50 4
5/2/2005 5.1 9.6 10 5 10 10 5.1 $9.60 10
5/3/2005 4.2 13.8 14 4 14 14 4.2 $13.80 14
5/4/2005 6.2 20 20 6 20 20 6.2 $20.00 20
5/5/2005 2 22 22 2 22 22 2 $22.00 22
5/6/2005 3 25 0 3 25 0 3 $25.00 0
5/7/2005 1.1 26.1 1 1 26 1 1.1 $26.10 1


All seemed well.

Is there any difference in what you are
working with, versus the simple example
above, i.e., are "FlightDate" values actually
type Date versus formatted strings, etc.?

good luck,

gary
Click to expand...
 
G

Gary Walter

Lee said:
With the help of Mr. Foster I have created a expression as follows.

SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum([Cy2k2ReportT501Hrs]) FROM [Year2005 Pilot Info]
WHERE FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

What I am trying to achieve is to add the hours in "Cy2k2ReportT501Hrs"
field until I reach 25 hours and give me cooresponding date located in the
"FlightDate" field. The result it is giving me is a date but it is not 25
hours, it is less.
Click to expand...
Hi Lee,

One other possibility is if your
FlightDate contains a "Time" portion.

Because the time portion can not always
be represented accurately on a computer,
the *equality* of the Where clause might
possibly fail.

simple test:

create a prequery "Q" where you convert
FlightDate to only the Date portion of the date/time.

SELECT
DateSerial(Year(FlightDate),Month(FlightDate),Day(FlightDate)) AS
FlightDateOnly,
Cy2k2ReportT501Hrs]
FROM [Year2005 Pilot Info];


then test your query using "Q"
instead of "[Year2005 Pilot Info]"
and "FlightDateOnly"
instead of "FlightDate"

SELECT A.FlightDateOnly
FROM Q AS A
GROUP BY Q.FlightDateOnly
HAVING
((((SELECT Sum(t.[Cy2k2ReportT501Hrs])
FROM Q As t
WHERE t.FlightDateOnly <= A.FlightDateOnly) Mod 25)=0))
 
G

Gary Walter

Hi Lee,

You are welcome to zip it into an attachment
and send (remove please no spam from my address),
but I am about to go into work...
so might be awhile before I can come back to
this.

I still am not clear on what you want, so maybe
looking at the data will help?

If you can, it would be more advantageous for you to
simply post some sample data here and try to explain one
more time (sorry) what you expect to get in your query
from that specific data.

Hang in there,

gary

"Lee"wrote:
Hi Thank you for your help....I am new to this forum and learning as I go
along.
I ran your simple query as follows:
SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum(t.[p1-pic-day]) FROM [Year2005 Pilot Info] As t
WHERE t.FlightDate <= A.FlightDate) Mod 20)=0))
ORDER BY A.FlightDate DESC;

The field of p1-pic-day has entries of 0, 2.0, 0.6, 1.1, 0.2 and etc. I
ran
this query and it goes almost to the last entry which is a zero which miss
the 20 hour total by a long shot. If you like i can email you the entire
field of numbers which is apporx 36 entries.

Let me tell you what I do to arrive at this information. I run a query
for
a particular pilot which will give me information for the past 180 days.
There are several fields in this query that has hours posted for whatever
they did in that particular category. From one of those fields i need to
add
the column from the most recent entry date until i accumlate 20 hours from
then i would like the corresponding date associated with that value.

Again thank you for your help on this.

Lee



Gary Walter said:
Lee said:
With the help of Mr. Foster I have created a expression as follows.

SELECT A.FlightDate
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum([Cy2k2ReportT501Hrs]) FROM [Year2005 Pilot Info]
WHERE FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

What I am trying to achieve is to add the hours in "Cy2k2ReportT501Hrs"
field until I reach 25 hours and give me cooresponding date located in
the
"FlightDate" field. The result it is giving me is a date but it is not
25
hours, it is less.
Click to expand...
Hi Lee,

First (meaning no offense), but MG is one of the
premier contributors on this newsgroup, and my
guess is that if you had continued this discussion
in the previous thread, Mr. Foster would have
likely *immediately* responded with more assistance.

The only thing I can think of is that you may want
your group to include a PilotID (or PilotName)...
something like?

SELECT A.FlightDate, A.PilotName
FROM [Year2005 Pilot Info] AS A
GROUP BY A.FlightDate, A.PilotName
HAVING
((((SELECT Sum(t.[Cy2k2ReportT501Hrs])
FROM [Year2005 Pilot Info] As t
WHERE
t.FlightDate <= A.FlightDate
AND
t.PilotName = A.PilotName)
Mod 25)=0))
ORDER BY A.FlightDate DESC;

If your [Year2005 PilotInfo] table (query?)
contained more than one pilot, then your
query above would probably hit 25 hours
total on a date when a *pilot* did not have
a full 25 hours.

Does that make sense in your situation?

Another thought was how are you storing
the hours? I made a small test table called
"tblLee" to test various number types.
FlightDate FloatHours LongHours CurHours
5/1/2005 4.5 5 4.5
5/2/2005 5.1 5 5.1
5/3/2005 4.2 4 4.2
5/4/2005 6.2 6 6.2
5/5/2005 2 2 2
5/6/2005 3 3 3
5/7/2005 1.1 1 1.1


and ran following query

SELECT A.FlightDate
FROM tblLee AS A
GROUP BY A.FlightDate
HAVING ((((SELECT Sum(t.[FloatHours]) FROM tblLee As t
WHERE t.FlightDate <= A.FlightDate) Mod 25)=0))
ORDER BY A.FlightDate DESC;

and got what one would expect.

FlightDate
5/6/2005


It may be that I just got lucky on summing the
limited float hours. Arithmetic on floats can be
a big gotcha. I usually create a wrapper function
around CDec() so I can use this function in
a query (maybe that has changed in later versions
of Access) to change the floats to Decimal and
expect any arithmetic to work.


I also ran this check query (you don't say
what type of field the Hours are stored in):

SELECT
t.FlightDate,
t.FloatHours,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) AS SumPrevFloat,
(SELECT Sum(t1.FloatHours) FROM tblLee As t1 WHERE
t1.FlightDate<=t.FlightDate) Mod 25 AS MOD25Float,
t.LongHours,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) AS SumPrevLong,
(SELECT Sum(t2.LongHours) FROM tblLee As t2 WHERE
t2.FlightDate<=t.FlightDate) Mod 25 AS MOD25Long,
t.CurHours,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) AS SumPrevCur,
(SELECT Sum(t3.CurHours) FROM tblLee As t3 WHERE
t3.FlightDate<=t.FlightDate) Mod 25 AS MOD25Cur
FROM tblLee AS t;

and got

FlightDate FloatHours SumPrevFloat MOD25Float LongHours SumPrevLong
MOD25Long CurHours SumPrevCur MOD25Cur
5/1/2005 4.5 4.5 4 5 5 5 4.5 $4.50 4
5/2/2005 5.1 9.6 10 5 10 10 5.1 $9.60 10
5/3/2005 4.2 13.8 14 4 14 14 4.2 $13.80 14
5/4/2005 6.2 20 20 6 20 20 6.2 $20.00 20
5/5/2005 2 22 22 2 22 22 2 $22.00 22
5/6/2005 3 25 0 3 25 0 3 $25.00 0
5/7/2005 1.1 26.1 1 1 26 1 1.1 $26.10 1


All seemed well.

Is there any difference in what you are
working with, versus the simple example
above, i.e., are "FlightDate" values actually
type Date versus formatted strings, etc.?

good luck,

gary
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

How to find previous, current and future values 1
How to get the previous, current and next values for a field 2
How to get previous and current and next values on one line 2
Finding both the Prior and the Next Record 1
Access Date and aggregation Query 2
Challenging Date Query help needed 2
Question With A Expression 1
Most Recent Date Query 2

Top