Help - SUM Query of MS Access (1 Viewer)

penandpaper

Registered User.
Local time
Today, 04:14
Joined
Aug 7, 2017
Messages
20
Hello is the SUM query in ms access different in SQL because I've got a SQL query and it is working as intended but when I convert it into ms access it works differently?

Here is the syntax:

Code:
INSERT INTO tblKnockOff ([Object Account], Description, [GL Date], [Document Type], [Document Number], Company, Subledger, [Subledger Type],  [JE Explanation], [Actual Amount], Remarks, [Batch Number], [Batch Date], [Trans Originator], [Post Status], Comments)
SELECT [Object Account], Description, [GL Date], [Document Type], [Document Number], 
Company, Subledger, [Subledger Type], ' ', [B]Sum([Actual Amount])[/B] AS TotalAmount, ([JE Explanation]) As Explanation, [Batch Number], [Batch Date], [Trans Originator], [Post Status], Comments FROM tblUnbilled
WHERE Cstr([Object Account])+Subledger+[JE Explanation] IN 
(
SELECT Cstr([Object Account])+Subledger+[JE Explanation]
FROM tblUnbilled 
GROUP BY [Object Account], Subledger, [JE Explanation] 
HAVING COUNT([Object Account]) > 1
)
GROUP BY [Object Account], Description, [GL Date], [Document Type], [Document Number], Company, Subledger, [Subledger Type], [Batch Number], 
[Batch Date], [Trans Originator], [Post Status], Comments, Remarks, [JE Explanation]

the sql version of the syntax add's the values in the field actual amount but in the ms access it just insert the same value?

any help on this? I need to finish this as soon as possible.
 

plog

Banishment Pending
Local time
Today, 06:14
Joined
May 11, 2011
Messages
11,611
You have 3 queries there (1 INSERT and 2 SELECTS)--where is this failing? You need to break it down and find out where its going haywire--the first SELECT or the second. Have you done that?

Throw away the INSERT portion and just work with the SELECTs until you get this fixed.
 

penandpaper

Registered User.
Local time
Today, 04:14
Joined
Aug 7, 2017
Messages
20
Thanks for the response and I think I know the problem now, its because some of the data in other columns are different.
 

Users who are viewing this thread

Top Bottom