How to join date/time field when "days" are same but "time" differ

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good evening everyone,

I have one TRANSACTION table that contains:
1."date/time" field (YYMMDD hh:mm:ss)
2."quantity field"

And another PRICE table that contans:
1."date/time" field (YYMMDD hh:mm:ss)
2."price field"

Now, I want to join the "date/time" fields so I can perform a simple
"quantity * price" calculation on each transaction-row.

But the time differes (hh:mm:ss) and I just can't find a way to make Access
accept a relation between the days (it outputs nothing). How can I make it
ignore difference in the "time-section" and only look at the YYMMDD section?

Kindly,
Mikael
Sweden
 
Use the DateValue function.

I have to question why your Price table has a single Date/Time value in it,
though. Shouldn't you perhaps have EffectiveFrom and EffectiveTo fields, and
you'd then match the transaction's date using TransactionTime BETWEEN
Price.EffectiveFrom AND Price.EffectiveTo?
 
Well, that is certainly one way to do it. But since my price will vary almost
with every day of the month I believe relation link between "transaction
table" and "price table" is the most convinient approach (I don't want to
"hard-code" between intervals).

So my question again, is it possible to match DATE/TIME even if the "time
part" varies (hours, minutes, seconds). I want the relation to ONLY look at
the YYMMDD part!

Kindly,
Mikael
 
Use the DateValue function. It strips off the time. It does require that
you give it a non-null valid date or date string.

SELECT Transaction.Date
, Transaction.Quantity
, Price.Price
, Transaction.Quantity * Price.Price as ExtendedPrice
FROM Transaction INNER JOIN Price
ON DateValue(Transaction.Date) = DateValue(Price.Date)

Another way to do this would be to build 3 queries
SELECT DateValue([Date]) as DateOnly, Quantity
FROM Transaction

SELECT DateValue([Date]) as DatePrice, Price.Price
FROM Price

Now join those two queries on DateOnly and DatePrice

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top