append query in SQL not working right

Question

INSERT INTO Table2 ( Customer, Order_Date, Stamp_Date, Travelled_Distance, Units, Country, Comments )

SELECT

'CustomerFamily' As Customer, 
T1.Order_Date AS Order_Date, 
T1.Stamp_Date AS Stamp_Date, 
T1.Travelled_Distance-T2.Travelled_Distance AS Travelled_Distance, 
T1.Units AS Units, 
'Canada' AS Country, 
'' AS Comments

FROM

Table1 AS T1, 
Table1 AS T2

WHERE

T1.Customer='Jake' And 
T2.Customer='Mike' And 
T2.Order_Date=T1.Order_Date

ORDER BY

T1.Order_Date;

This is my append query that has a calculation in it ( for the days that jake and mike travel on same day it subtracts mikes travel distance from jakes)

the PROBLEM is that it does all the calculation fine and puts it into Table 2 but there is two rows which are the same for every single calculation.

why is it repeating twice? I can't spot the error

Is there any date in Table1 where Mike or Jake has travelled more than once in the same date? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 21, 2011 at 22:46
Is there some specific reason why you're using implicit joins instead of explicit? — David-W-Fenton
– David-W-Fenton, Commented Jul 22, 2011 at 23:41
jsut because when i tried them a few times before it kept giving me a JOIN format nto supported..or function nto supported or somethign not supported i dont quite remember — Chaostryder
– Chaostryder, Commented Jul 22, 2011 at 23:47
If there is any date in Table1 where someone has travelled more than once in the same date, then you should not use DISTINCT but GROUP BY and SUM(Travelled_Distance) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 23, 2011 at 7:05

Taryn · Accepted Answer · 2011-07-21 22:07:49Z

2

Try

SELECT DISTINCT 'CustomerFamily' As Customer, 
T1.Order_Date AS Order_Date, 
T1.Stamp_Date AS Stamp_Date, 
T1.Travelled_Distance-T2.Travelled_Distance AS Travelled_Distance, 
T1.Units AS Units, 
'Canada' AS Country, 
'' AS Comments

answered Jul 21, 2011 at 22:07

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

append query in SQL not working right

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related