query update error

[MarcoPerez21]

I keep getting a update error below is my SQL statements:

INSERT INTO [tbl_des1_daily sales] ( [Day], [Store Praline Sales], [Store Merch Sales], [Store Postage Sales] )
SELECT tbl_SalesHistory2_ASC.TicketDate, tbl_SalesHistory2_ASC.SumOfExtendedPrice, tbl_SalesHistory2_Merch.SumOfExtendedPrice, tbl_SalesHistory2_Post.SumOfExtendedPrice
FROM (tbl_SalesHistory2_ASC LEFT JOIN tbl_SalesHistory2_Merch ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Merch.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Merch.Location)) LEFT JOIN tbl_SalesHistory2_Post ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Post.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Post.Location)
WHERE (((tbl_SalesHistory2_ASC.Location)="01"));

can anyone tell me why I keep getting an error message?

thanks
Marco

 

[amorous]

INSERT INTO [tbl_des1_daily sales] ( [Day], [Store Praline Sales], [Store Merch Sales], [Store Postage Sales] ) VALUES

-VJ

 

[MarcoPerez21]

can you give me more information on the VALUES, I am not sure what values I am supposed to insert here, are you referring to the information that I have totalled?


Marco

 

[amorous]

I was just referring to the Key word VALUES

A regular insert statment looks like this:

INSERT INTO TableName(field1,field2) VALUES(field1value,filed2value)

So try your query as ...

INSERT INTO [tbl_des1_daily sales] ( [Day], [Store Praline Sales], [Store Merch Sales], [Store Postage Sales] ) VALUES
SELECT tbl_SalesHistory2_ASC.TicketDate, tbl_SalesHistory2_ASC.SumOfExtendedPrice, tbl_SalesHistory2_Merch.SumOfExtendedPrice, tbl_SalesHistory2_Post.SumOfExtendedPrice
FROM (tbl_SalesHistory2_ASC LEFT JOIN tbl_SalesHistory2_Merch ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Merch.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Merch.Location)) LEFT JOIN tbl_SalesHistory2_Post ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Post.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Post.Location)
WHERE (((tbl_SalesHistory2_ASC.Location)="01"));


-VJ

 

[MarcoPerez21]

I get a syntax error in the insert statement, below is exactly what i have typed:

INSERT INTO [tbl_des1_daily sales] ( [Day], [Store Praline Sales], [Store Merch Sales], [Store Postage Sales] Values
SELECT tbl_SalesHistory2_ASC.TicketDate, tbl_SalesHistory2_ASC.SumOfExtendedPrice, tbl_SalesHistory2_Merch.SumOfExtendedPrice, tbl_SalesHistory2_Post.SumOfExtendedPrice
FROM (tbl_SalesHistory2_ASC LEFT JOIN tbl_SalesHistory2_Merch ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Merch.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Merch.Location)) LEFT JOIN tbl_SalesHistory2_Post ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Post.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Post.Location)
WHERE (((tbl_SalesHistory2_ASC.Location)="01"));

thanks,
marco

 

[amorous]

I am sorry.. you dont need the the key word VALUES for the query structure you have.

So what error did u get when you tried your query with out the keyword VALUES.

-VJ

 

[lespaul]

Do you get the results you are expecting when you just run the select statement?

What is the error message?

Leslie

 

[MarcoPerez21]

yes I get the results that I want from the select statement, I get this:

MS Access set 0 field(S) to Null due to type conversion failure, and it didn't add 408 records to the table due to key violations. 0 record(s) due to lock violations, 0 records due to rule violations.

 

[lespaul]

What is the key field in tbl_des1_daily sales?

Are any of the records from your select already in tbl_des1_daily sales?

Leslie

 

[MarcoPerez21]

Leslie,
my key field is "DAY" & the DATA type is DATE/Time, no the data from the select qry are not in my tbl_des1_daily sales.

thanks,
Marco

 

[lespaul]

are you still using the exact query you posted above? It's missing a paren if so (red) and you may also need one around the select (blue):

INSERT INTO [tbl_des1_daily sales] ( [Day], [Store Praline Sales], [Store Merch Sales], [Store Postage Sales][COLOR=red])[/color]  Values
[COLOR=blue]([/color]SELECT tbl_SalesHistory2_ASC.TicketDate, tbl_SalesHistory2_ASC.SumOfExtendedPrice, tbl_SalesHistory2_Merch.SumOfExtendedPrice, tbl_SalesHistory2_Post.SumOfExtendedPrice
FROM (tbl_SalesHistory2_ASC LEFT JOIN tbl_SalesHistory2_Merch ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Merch.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Merch.Location)) LEFT JOIN tbl_SalesHistory2_Post ON (tbl_SalesHistory2_ASC.TicketDate = tbl_SalesHistory2_Post.TicketDate) AND (tbl_SalesHistory2_ASC.Location = tbl_SalesHistory2_Post.Location)
WHERE (((tbl_SalesHistory2_ASC.Location)="01"))[COLOR=blue])[/color];

Leslie

 

[MarcoPerez21]

no I m not using that I caught that mistake earlier, made the necessary changes to it, and still not luck.

thanks,
MArco

 

[lespaul]

don't know what to tell you, everything looks ok. Did you add parens around your select statement?

leslie


Leslie