Evaluate Next Record In Query?

[Guest]

Hello,

I have a table with pay periods as a field. I can sort these records by pay
period. Is there any way to evaluate the next record, in order to create a
field in a query which tells me whether the gap between pay periods is 1 week
or 2 weeks (through evaluating the next record)?

 

[Michel Walsh]

Hi,



If the table has fields like:

f1, dateStamp


then



SELECT a.f1, a.dateStamp As thisDateStamp,
b.dateStamp As PreviousDStampForF1,
a.dateStamp-b.dateStamp as Gap

FROM (myTable As a LEFT JOIN myTable As b ON a.f1=b.f1 AND
a.dateStamp>b.dateStamp)
LEFT JOIN myTable As c ON a.f1=c.f1 AND a.dateStamp >
c.dateStamp

GROUP BY a.f1, a.dateStamp, b.dateStamp

HAVING b.dateStamp = MAX(c.dateStamp)




should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your reply. I'm thinking we're close, but still get one small
error...

I built a query to "produce" the datestamp (year + payweek) and am using
that in the query, resulting in:

SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, A.DateStamp -B.DateStamp As GAP
FROM (qryDateStamp As A LEFT JOIN qryDateStamp AS B ON A.mgrname = B.mgrname
and A.DateStamp > B.DateStamp)
LEFT JOIN qryDateStamp AS C ON A.mgrname = C.mgrname and A.DateStamp >
C.DateStamp
GROUP BY A.mgrname, A.DateStamp, B.DateStamp Having B.DateStamp =
MAX(C.DateStamp)

I get the error: You tried to execute a query that does not include the
specified expression 'B.DateStamp = MAX(C.DateStamp) as part of an aggregate
function.

Not being an SQL guru, and not being able to view this in design mode (due
to Access not liking the ">" sign in the join), I'm slightly stuck.

Thanks.

 

[Guest]

One more bit of info Michel,

I now get a different error messageata Type Mismatch In Criteria
Expression.

The query is:

SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, DateDiff("w",A.DateStamp, B.DateStamp) AS GAP
FROM (qryDateStamp AS A LEFT JOIN qryDateStamp AS B ON (A.mgrname =
B.mgrname) AND (A.DateStamp > B.DateStamp)) LEFT JOIN qryDateStamp AS C ON
(A.mgrname = C.mgrname) AND (A.DateStamp > C.DateStamp)
GROUP BY A.mgrname, A.DateStamp, B.DateStamp
HAVING (((First(B.DateStamp))=Max([C].[DateStamp])));

The only difference between this, and the one I emailed on earlier, is the
field DateStamp is "created" in qryDateStamp as a SHORT DATE field, through
the syntax: CDate(Left([paydate],2) & "-" & Mid([paydate],3,3) & "-" &
Right([paydate],2)). Paydate is a field with the format ddmmmyy (i.e.
03Mar06).



If I run the query as noted below, I get no error message, but you can see
the JOIN statement has a.datestamp = b and c.datestamp vs. a.datestamp > b
and c.datestamp...

SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, DateDiff("w",A.DateStamp, B.DateStamp) AS GAP
FROM (qryDateStamp AS A LEFT JOIN qryDateStamp AS B ON (A.mgrname =
B.mgrname) AND (A.DateStamp = B.DateStamp)) LEFT JOIN qryDateStamp AS C ON
(A.mgrname = C.mgrname) AND (A.DateStamp = C.DateStamp)
GROUP BY A.mgrname, A.DateStamp, B.DateStamp
HAVING (((First(B.DateStamp))=Max([C].[DateStamp])));

Any ideas why I get this error message?

 

[Michel Walsh]

Hi,



If you have a text field compared to a numerical (or date_time) field, that
can be a problem, indeed. It is generally easier and faster to WORK on
native numerical data and to CONVERT / FORMAT only at the presentation
stage.



You can keep the grid edition capability, if you are ready to loose the LEFT
JOIN (which we don't really need, at least, not for the bulk of the
debugging): transfer the content of the ON clauses into a WHERE clause, and
replace LEFT JOIN with a coma:


SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, DateDiff("w",A.DateStamp, B.DateStamp) AS GAP
FROM qryDateStamp AS A , qryDateStamp AS B , qryDateStamp AS C

WHERE (A.mgrname = B.mgrname) AND (A.DateStamp > B.DateStamp) AND
(A.mgrname = C.mgrname) AND (A.DateStamp > C.DateStamp)

GROUP BY A.mgrname, A.DateStamp, B.DateStamp
HAVING ((((B.DateStamp))=Max([C].[DateStamp])));


should be 'workable' in the query designer. It makes the WHERE clause more
messy than the individual ON clauses. You can also make a mix of all this,
putting back the = parts as join, and leaving just the inequalities in the
WHERE clause.

Sure, some records will be missing, such as those where there is not records
in B such that

A.DateStamp > B.DateStamp


but we were loosing those records, anyhow, even using the LEFT JOIN, with
our actual HAVING clause.

Note that since B.DateStamp is in the GROUP BY, we don't need to aggregate
it, with FIRST or otherwise, in the HAVING clause.



Hoping it may help,
Vanderghast, Access MVP

One more bit of info Michel,

I now get a different error messageata Type Mismatch In Criteria
Expression.

The query is:

SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, DateDiff("w",A.DateStamp, B.DateStamp) AS GAP
FROM (qryDateStamp AS A LEFT JOIN qryDateStamp AS B ON (A.mgrname =
B.mgrname) AND (A.DateStamp > B.DateStamp)) LEFT JOIN qryDateStamp AS C ON
(A.mgrname = C.mgrname) AND (A.DateStamp > C.DateStamp)
GROUP BY A.mgrname, A.DateStamp, B.DateStamp
HAVING (((First(B.DateStamp))=Max([C].[DateStamp])));

The only difference between this, and the one I emailed on earlier, is the
field DateStamp is "created" in qryDateStamp as a SHORT DATE field,
through
the syntax: CDate(Left([paydate],2) & "-" & Mid([paydate],3,3) & "-" &
Right([paydate],2)). Paydate is a field with the format ddmmmyy (i.e.
03Mar06).



If I run the query as noted below, I get no error message, but you can see
the JOIN statement has a.datestamp = b and c.datestamp vs. a.datestamp > b
and c.datestamp...

SELECT A.mgrname, A.DateStamp AS ThisDateStamp, B.DateStamp AS
ThatDateStamp, DateDiff("w",A.DateStamp, B.DateStamp) AS GAP
FROM (qryDateStamp AS A LEFT JOIN qryDateStamp AS B ON (A.mgrname =
B.mgrname) AND (A.DateStamp = B.DateStamp)) LEFT JOIN qryDateStamp AS C ON
(A.mgrname = C.mgrname) AND (A.DateStamp = C.DateStamp)
GROUP BY A.mgrname, A.DateStamp, B.DateStamp
HAVING (((First(B.DateStamp))=Max([C].[DateStamp])));

Any ideas why I get this error message?
--
David

Hi,



If the table has fields like:

f1, dateStamp


then



SELECT a.f1, a.dateStamp As thisDateStamp,
b.dateStamp As PreviousDStampForF1,
a.dateStamp-b.dateStamp as Gap

FROM (myTable As a LEFT JOIN myTable As b ON a.f1=b.f1 AND
a.dateStamp>b.dateStamp)
LEFT JOIN myTable As c ON a.f1=c.f1 AND a.dateStamp >
c.dateStamp

GROUP BY a.f1, a.dateStamp, b.dateStamp

HAVING b.dateStamp = MAX(c.dateStamp)




should do.


Hoping it may help,
Vanderghast, Access MVP