CODEWITHSUNDEEP
sqlms-accessms-access-2003
Chaostryder
Chaostryder

Reputation: 223

append query in SQL not working right

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

Upvotes: 2

Views: 375

Answers (1)

Taryn
Taryn

Reputation: 247700

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

Upvotes: 2

Related Questions