Date Join Issue

[Peter Thompson]

Hello,

I am having issues getting a join to work. The issues I am having appears to be due to the transaction date.

There are two tables the data table (Oracle_Transaction_Data) has a standard date time format with both the data and the time showing, while the Date table has just the Date. In order to get the tables to join I have used int on the date time field in the subquery, which should then enable it to be joined to the date table, however, I get the message "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

SELECT C.Store_Name, 
b.Transaction_Date,
sum(B.Net_Sales) AS Net_Sales, 
Sum(B.Quantity) AS Quantity, 
Count(B.Transaction_Number) AS Transactions, 
Sum(B.Net_Sales)/count(B.Transaction_Number) AS ATV, 
D.ISO_WEEK_NUMBER

FROM (SELECT a.shop AS Shop, int(A.Date_Time) AS Transaction_Date, a.transaction AS Transaction_Number, sum(A.Net_Value) AS Net_Sales, sum(A.quantity) AS Quantity 
FROM Oracle_Transaction_Data AS A
GROUP BY int(A.Date_Time), A.shop, A.transaction)  AS B, 

Stores AS C, 
Date_Table AS D

WHERE B.shop=c.store_number 
And B.shop=15 
And B.Transaction_date =d.date

GROUP BY c.store_name, b.transaction_date, b.transaction_number, d.ISO_WEEK_NUMBER;

Does anyone have any ideas.

Thanks,

Peter

 

[ranman256]

dont use INT (too small), use CLNG ,to convert to a big #.

or convert the dates to the same format , format([date],"mm/dd/yyyy")

 

[Peter Thompson]

Thanks for the quick response.

I have done what you suggested below. However, no it brings back no data, but I know that some data should be bought back.
It appears to me that it must be the subquery that is causing the issue as when I try and run the in isolation it gives me the message "Invalid Use of Null", which I don't understand as I cant see any Nulls in the data.

Have you any futher ideas? Is there a way of telling which record access thinks contains the null.

SELECT C.Store_Name,
b.Transaction_Date,
sum(B.Net_Sales) AS Net_Sales,
Sum(B.Quantity) AS Quantity,
Count(B.Transaction_Number) AS Transactions,
Sum(B.Net_Sales)/count(B.Transaction_Number) AS ATV,
D.ISO_WEEK_NUMBER

FROM (SELECT a.shop AS Shop, clng(A.Date_Time) AS Transaction_Date, a.transaction AS Transaction_Number, sum(A.Net_Value) AS Net_Sales, sum(A.quantity) AS Quantity
FROM Oracle_Transaction_Data AS A
GROUP BY clng(A.Date_Time), A.shop, A.transaction) AS B,

Stores AS C,
Date_Table AS D

WHERE B.shop=c.store_number
And B.shop=15
And clng(B.Transaction_date) =clng(d.date)

GROUP BY c.store_name, b.transaction_date, b.transaction_number, d.ISO_WEEK_NUMBER;

 

[Micron]

See if you can coerce your date_time value to a serial number. Easiest way is to paste a value into Excel and format it as a general number. If you are dealing with date AND time values, this number contains a decimal point. As I write, it is 42240.9435. Longs cannot contain decimals, so that approach will/may not work. You might have to covert it to a Single (Csing). However, I suspect one of your fields is text data type, because you should be able to compare "dates only" against date/time fields without all this bother. I have done this many times before. The only issue I ever had was using the > or >= operators against a date/time field (you have to add time to the ending date using DateAdd function, otherwise it is cut off at 00:00:00 hour).

If the subquery runs ok as part of the whole, I think the other issue is that it cannot stand alone. You could also try starting from the beginning and build this in parts until it fails, but I suggest you try my other suggestion first.

<tbody>
</tbody>

 

[james_lankford]

why would these two join

B.Transaction_date =d.date

B.Transaction_date is an Int
d.date is a date

does the database automatically convert d.date to an int ?

are both you tables in Oracle ?
or is one in oracle and the other in access
or are they both in access


sql - How to correctly handle dates in queries constraints - Stack Overflow

How to extract only date value from date field in Oracle? - Stack Overflow

instead of int(A.Date_Time)
try trunc(A.Date_Time)
link to TRUNCATE

 

[Micron]

instead of int(A.Date_Time)
try trunc(A.Date_Time)
link to TRUNCATE

Are you suggesting the use of Truncate within an Access sql? Truncate is for use within Oracle and not Access, correct?

 

[james_lankford]

well one of the tables is named
Oracle_Transaction_Data

so I don't know what he's doing

maybe it's a linked table, maybe it's imported

but if it's linked I believe he can do a passthrough query and use oracle specific functions