Update Query (1 Viewer)

jctaylor

New member
Local time
Yesterday, 17:18
Joined
Jun 9, 2016
Messages
3
I have a table with prices that I want to update with the latest prices. I used the query design for append and I got this sql statement.

INSERT INTO Sprices ( stksym, mdate, Volume, High, Low, [close] )
SELECT Tprices.stksym, Tprices.mdate, Tprices.Volume, Tprices.High, Tprices.Low, Tprices.close
FROM Tprices INNER JOIN Sprices ON Tprices.Stksym = Sprices.stksym;

When I run it, I get thousands of duplicates. When I look at the dataset mode when I am designing it, there are 184 records. Any suggestions about why I am getting all these excess records.
 

isladogs

MVP / VIP
Local time
Today, 00:18
Joined
Jan 14, 2017
Messages
18,186
Try inserting unique values like this:

Code:
INSERT INTO Sprices ( stksym, mdate, Volume, High, Low, [close] )
SELECT Tprices.stksym, Tprices.mdate, Tprices.Volume, Tprices.High, Tprices.Low, Tprices.[close]
FROM Tprices INNER JOIN Sprices ON Tprices.Stksym = Sprices.stksym;

Before you run the append, check the number of records in the Select Distinct part

I noticed you put [] around the Close field in the first line.
I imagine its a reserved word in access so add it in the 3rd line also as I have
 

MarkK

bit cruncher
Local time
Yesterday, 17:18
Joined
Mar 17, 2004
Messages
8,178
welcome...

I wouldn't have two tables with exactly the same fields. How does data get into TPrices? Why not put that data directly into SPrices when it is generated, and if that data is updated over time, then add a DateTime field, so you can know what data is current, and what data is historical.

Typically it is evidence of a design flaw that you have two tables with exactly the same structure, and also a design flaw that you must move data around inside your database.

hth
Mark
 

jctaylor

New member
Local time
Yesterday, 17:18
Joined
Jun 9, 2016
Messages
3
Try inserting unique values like this:

Code:
INSERT INTO Sprices ( stksym, mdate, Volume, High, Low, [close] )
SELECT Tprices.stksym, Tprices.mdate, Tprices.Volume, Tprices.High, Tprices.Low, Tprices.[close]
FROM Tprices INNER JOIN Sprices ON Tprices.Stksym = Sprices.stksym;

Before you run the append, check the number of records in the Select Distinct part

I noticed you put [] around the Close field in the first line.
I imagine its a reserved word in access so add it in the 3rd line also as I have

I tried that and it didn't work either.


I am importing a csv file into a temp table and I have tried appending that but I still get all the extra records.
 

plog

Banishment Pending
Local time
Yesterday, 19:18
Joined
May 11, 2011
Messages
11,613
Whats the INNER JOIN doing for you? Why's that there?

Also, run this query:

Code:
SELECT stksym, COUNT(stksym) AS Records
FROM Sprices
GROUP BY stksym
HAVING COUNT(stksym)>1;

If you get any results there's your answer--you have duplicates in Sprices.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:18
Joined
Feb 19, 2013
Messages
16,553
my suggest would be because of this

FROM Tprices INNER JOIN Sprices ON Tprices.Stksym = Sprices.stksym

since you are inserting records, I'm assuming Sprices already has multiple records with the same stksym, so creating your multiples.

Two solutions:

1. Change this

SELECT Tprices.stksym, Tprices.mdate, ......

to

SELECT DISTINCT Tprices.stksym, Tprices.mdate,

or 2. Change this

FROM Tprices INNER JOIN Sprices ON Tprices.Stksym = Sprices.stksym

to

FROM Tprices
 

jctaylor

New member
Local time
Yesterday, 17:18
Joined
Jun 9, 2016
Messages
3
Taking out the inner join worked. Thanks a lot.
 

Users who are viewing this thread

Top Bottom