John: Wasn't sure Dave was up to a non-equijoin.
Dave: If you choose to use this method, which is likely to be quicker, you
will only be able to edit the query in SQL view. The difference between an
equijoin and a non-equijoin is that the non-equijoin uses a < or > symbol in
the query, and instead of a one-to-one type relationship, returns a query
with multiple records. As an example, lets assume you have a single table
with the number 0 to 9. If you joined that table to itself using an
equijoin, and included both tables in the select clause it might look like:
SELECT T1.MyNum, T2.MyNum
FROM myTable as T1 LEFT JOIN myTable as T2
ON T1.MyNum = T2.MyNum
which would return results like:
T1.MyNum T2.MyNum
1 1
2 2
3 3
....
Now, if you replaced that with a non-equijoin it might look like:
SELECT T1.MyNum, T2.MyNum
FROM myTable as T1 LEFT JOIN myTable as T2
ON T1.MyNum >= T2.MyNum
and the results would look like:
T1.MyNum T2.MyNum
1 1
2 1
2 2
3 1
3 2
3 3
This is actually a great technique when you want to rank the number of items
in a table based on the number of values that are < the value (golf scores as
an example).
SELECT T1.Score, Count(T2.ID) as Rank
FROM myTable as T1 LEFT JOIN myTable as T2
ON T1.Score >= T2.Score
GROUP BY T1.Score
You can create this type of query as as an equijioin in the query design
view, then click over to the SQL view to change the join symbol.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
John Spencer said:
This query MIGHT be faster.
SELECT T.Item, T.TransactionID
, T.TransDate
, Max(T2.TransDate) as PriorDate
, DateDiff("D",Max(T2.TransDate),T.TransDate) as Elapsed
FROM YourTable as T LEFT JOIN YourTable as T2
ON T.Item = T2.Item
AND T.TransDate > T2.TransDate
GROUP BY T.Item, T.TransactionID, T.TransDate
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I started another thread because I think processing this query as well as
others is a weak area for me. The thread is
http://www.microsoft.com/office/com...ries&mid=80c803ea-df77-4eba-9671-ccd4b0c5f5d5
I am the only one that uses the data. After I query, I report the results.
Thanks the query you sent is what I was looking for.
Dale Fye said:
You might try ensuring that both the Item and TransDate fields of your table
are indexed. Indexing is a tough subject. If you over index, then you slow
down the process of adding/inserting records, but if you under index, you can
significantly slow down the query process. A lot depends on how much use the
database gets. If it is only a couple of users, and you do more querying or
searching than you do inserting records, then you should definately create
indexes for most of the fields you use in your WHERE clauses.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
When there are a lot of records, 91000, are there tricks to reduce processing
time?
Dave
:
Recommend you consider changing your field name if the date fields name is
actually [Date]. Date is a reserved word in Access, so its use as a field
name could cause you difficulties.
The following query should get you the information you need for the previous
Date.
Select T.Item, T.TransactionID, T.TransDate,
(SELECT Max(TransDate) as PrevDate FROM yourTable
WHERE Item = T.Item
AND TransDate < T.TransDate) as PrevDate
FROM yourTable as T
Once this query is running, you can write another query that uses it,
something like:
SELECT Item, TransactionID, TransDate, [TransDate] - [PrevDate] as Calculation
FROM yourQuery
ORDER BY Item ASC, TranactionID DESC
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have read posts about previous records and still have not been able to
figure this out.
I have a series of records
Item TransactionID Date
1 30 1/1/2008
2 31 1/1/2008
2 33 2/1/2008
1 34 1/10/2008
1 35 1/15/2008
I want to be able to look back for a previous transaction ID and calculate
the difference in dates between the Item, it's transaction sequencial number
and the 2 dates of those transaction numbers.
So the results would be:
Item TransactionID Date Calculation
1 35 1/15/2008 5 Days
1 34 1/10/2008 9 days
1 30 1/1/2008
2 33 2/1/2008 31 Days
2 31 1/1/2008
Can someone help get me started?
Thanks
