Transaction Record

[Joe]

Hello,

I would like for my most current transaction on a table to be coded as 0 for
faster queries but not sure how to go by it.

Example
Transaction ID Transaction Date Tnumber
1 7/23/2008 2
2 7/24/2008 1
3 7/25/2008 0

When I reference the Transaction table I can just query Tnumber = 0 and it
would give me the most recent transaction.

Any help would be nice.

Thanks

 

[John W. Vinson/MVP]

Hello,

I would like for my most current transaction on a table to be coded as 0 for
faster queries but not sure how to go by it.

Example
Transaction ID Transaction Date Tnumber
1 7/23/2008 2
2 7/24/2008 1
3 7/25/2008 0

When I reference the Transaction table I can just query Tnumber = 0 and it
would give me the most recent transaction.

Any help would be nice.

Thanks

Well, that violates normal form inasmuch as the TNumber is dependent
on other data in the table. If you were to add (say) an overlooked
transaction with a transaction date prior to the latest, you would
need to reassign all later TNumbers.

Do you have a *DEMONSTRATED*, unsolvable, performance problem using a
subquery to get the latest date? Let's say you have even a million or
so transactions, and a nonunique Index on TransactionDate; a query
like

SELECT <whatever you want to see>
FROM Transactions
WHERE TransactionDate = (SELECT Max(T.[TransactionDate]) FROM
Transactions AS T);

should be pretty quick.

 

[Joe]

Hello John...

I have no problem writing the query. The problem that I was having was
other users not to familiar with database running their own queries. i
wanted to simplify the query for them.

Thanks

Hello,

I would like for my most current transaction on a table to be coded as 0 for
faster queries but not sure how to go by it.

Example
Transaction ID Transaction Date Tnumber
1 7/23/2008 2
2 7/24/2008 1
3 7/25/2008 0

When I reference the Transaction table I can just query Tnumber = 0 and it
would give me the most recent transaction.

Any help would be nice.

Thanks

Well, that violates normal form inasmuch as the TNumber is dependent
on other data in the table. If you were to add (say) an overlooked
transaction with a transaction date prior to the latest, you would
need to reassign all later TNumbers.

Do you have a *DEMONSTRATED*, unsolvable, performance problem using a
subquery to get the latest date? Let's say you have even a million or
so transactions, and a nonunique Index on TransactionDate; a query
like

SELECT <whatever you want to see>
FROM Transactions
WHERE TransactionDate = (SELECT Max(T.[TransactionDate]) FROM
Transactions AS T);

should be pretty quick.