Dfirst

  • Thread starter Thread starter Mighty Mouse
  • Start date Start date
M

Mighty Mouse

trying to use dfirst, having a bit of troble using the format, can I get some
help here please?
 
Are you sure it’s the DFirst method you need? Essentially it returns a value
from a random row in a table or query, as does the DLast method. If you want
to return a value from a specific row, such as a the first row from set of
rows sorted by one or more columns, then it may well not do what you expect.

If you could explain in detail what you want to do rather than how toy are
trying to do it we can probably offer more constructive help.

Ken Sheridan
Stafford, England
 
I think what the "mouse" is ultimately trying to do is to extract the last
two pieces of information for records with multiple entries. For example, it
has a table with a listing of all of the store visits, dates, and scores.
The "mouse" wants to be able to extract the two most recent visits, dates,
and scores for each unique store. Is dfirst/dsecond the correct method to do
that?
 
As Ken stated, when used against a table, DFirst will essentially return a
random row. If you use it against a query that has an appropriate ORDER BY
clause, it'll work, but you're better off using DMax.

There is no "dSecond"

Take a look at how to use a subquery (see what Allen Browne has at
http://www.allenbrowne.com/subquery-01.html )

Note that using SELECT TOP 2 (with an appropriate ORDER BY clause) is not
guaranteed to only return 2 rows: if there's a tie, all rows that match the
ORDER BY criteria will be returned.
 
If that's the case then, as Doug says, the DMax method can be used to return
the latest date and then this used as a criterion for returning some other
value from that row using the DLookup method, but to return the rows with the
two latest visits per store a query can be used, e.g.

SELECT *
From Visits As V1
WHERE VisitDate IN
(SELECT TOP 2 VisitDate
FROM Visits As V2
WHERE V2.Store = V1.Store
ORDER BY VisitDate DESC)
ORDER BY Store, VisitDate DESC;

or:

SELECT *
FROM Visits AS V1
WHERE EXISTS
(SELECT Store
FROM Visits AS V2
WHERE V2.Store = V1.Store
AND V2.VisitDate >= V1.VisitDate
GROUP BY Store
HAVING COUNT(*) <= 2)
ORDER BY Store, VisitDate DESC;

or:

SELECT *
FROM Visits AS V1
WHERE
(SELECT COUNT(*)
FROM Visits AS V2
WHERE V2.Store = V1.Store
AND V2.VisitDate >= V2.VisitDate) <= 2
ORDER BY Store, VisitDate DESC;

In each case the subquery identifies the two latest visits per store by
being correlated with the outer query on the Store column, distinguishing
between the two instances of the Visits table by the aliases V1 and V2.

BTW there is no DSecond method, only first and last (plus the various other
domain aggregation methods like DMax, DMin etc).

Ken Sheridan
Stafford, England
 
Thanks for the help. This post has moved me light years away from where I
was. I was able to use one of the queries below to return two records per
store for the most recent visits. Now I need to take it one step farther and
get one record per store with both visit dates and score included on the same
line. There are two many dates to use a cross tab query. I am wondering if
there is anyway for me to designate the most recent visit as Visit 1 and then
Visit 2. I am not sure how to do this. Does anyone have any thoughts?
 
You can do it by creating another query which joins one of the queries below
to itself, so if the query has been saved as qryLastTwoVisits the final query
would be like this:


SELECT Q1.Store,
Q2.VisitDate As Visit1,
Q2.Score As Score1,
IIF(Q1.VisitDate =
Q2.VisitDate ,
NULL,Q1.VisitDate)
AS Visit2,
IIF(Q1.VisitDate =
Q2.VisitDate ,
NULL,Q1.Score)
AS Score2
FROM qryLastTwoVisits AS Q1,
qryLastTwoVisits AS Q2
WHERE Q1.Store = Q2.Store
AND (Q1.VisitDate > Q2.VisitDate
OR (SELECT COUNT(*)
FROM qryLastTwoVisits As Q3
WHERE Q3.Store = Q1.Store) = 1);


Note that in this case the join is done in the WHERE clause on the Stores
being the same, and the either one visit date being later that the other or
there having been only one visit to the store in question.

The IIF function calls and the subquery in the WHERE clause are to take
account of a store only having had one visit, in which case the Visit2 and
Score2 columns will contain Nulls. Otherwise without the IIF function call
the dates and scores would be repeated in the pairs of columns, and without
the subquery those rows would not be returned at all.

Ken Sheridan
Stafford, England
 
Back
Top