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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikael Lindqvist - 08 Nov 2007 22:17 GMT

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

Reply to this Message

Douglas J. Steele - 08 Nov 2007 22:37 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Good evening everyone,
>
[quoted text clipped - 18 lines]
> Mikael
> Sweden

Reply to this Message

Mikael Lindqvist - 09 Nov 2007 06:49 GMT

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

> Good evening everyone,
>
[quoted text clipped - 16 lines]
> Mikael
> Sweden

Reply to this Message

John Spencer - 09 Nov 2007 12:36 GMT

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

Signature

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

> Well, that is certainly one way to do it. But since my price will vary
> almost
[quoted text clipped - 33 lines]
>> Mikael
>> Sweden

Reply to this Message

Douglas J. Steele - 09 Nov 2007 22:51 GMT

I told you: use the DateValue function.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Well, that is certainly one way to do it. But since my price will vary
> almost
[quoted text clipped - 33 lines]
>> Mikael
>> Sweden

Reply to this Message