Creating a new row base on a query (1 Viewer)

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
Hello,

I'm new to ms access and I was wondering how do you insert a new row that has the the sum of a query?

The table look's like this:

Object Account | Subledger | JE Explanation | Actual Ammount | Remarks
Tom*************abc*******Orange********100.00*******Apple
Tom*************abc*******Orange********300.00*******Apple
Dianne***********xyz*******Banana********250.00****** Apple
Jerry*************jkl*******Grapes********100.00******* Apple
Tom*************abc*******Orange********100.00*******Apple

and the output I want is:

Object Account | Subledger | JE Explanation | Actual Ammount | Remarks
Tom*************abc*******Orange********100.00*******Apple
Tom*************abc*******Orange********300.00*******Apple
Tom*************abc*******Orange********100.00*******Apple
Tom**********abc********Apple********100.00*******Apple


Basically I need to add a new row base on a query of duplicates in object account, subledger and JE explanation.

The new amount of the new row is the sum of the duplicates and the remarks of the duplicates is the JE explanation of the new row.

I don't know if this is possible or not, and I'am running out of ideas for this one.

Any help on this? :)

PS: Pardon for the sample table I can't post pictures yet :(
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:01
Joined
Jul 9, 2003
Messages
16,244
Maybe look at doing it with a report or possibly in Excel
 

JHB

Have been here a while
Local time
Today, 18:01
Joined
Jun 17, 2012
Messages
7,732
Is the result you want and what you show correct, (I've expected it should be 500 for "Actual Ammount")?
 

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
Is the result you want and what you show correct, (I've expected it should be 500 for "Actual Ammount")?

oops sorry the expected result should be

Tom**********abc********Apple********500.00******* Apple

thanks for pointing it out.
 

JHB

Have been here a while
Local time
Today, 18:01
Joined
Jun 17, 2012
Messages
7,732
Use a sum query.
 

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
Can you tell me how can I achieve that? I don't have any idea on how to achieve it with a sub query of duplicates of subledger, account and explanation
 

JHB

Have been here a while
Local time
Today, 18:01
Joined
Jun 17, 2012
Messages
7,732
Yes I can show you if you send some sample data in a MS-Access database, zip it because you haven't post 10 post yet.
 

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
I'm just using a test table for it and I attached it here just in case :)
thanks in advance :D
 

Attachments

  • sampDatabase.accdb
    372 KB · Views: 90

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
Update on this I made a SQL version on this now I'm having trouble on converting it from SQL to MS Access, Any help on this?


INSERT INTO SampleData2
SELECT Object_Account, Descriptions, GL_Date, Document_Type, Document_Number,
Company, Subledger, Subledger_Type, ' ', Sum(Actual_Amount * -1) AS TotalAmount, (JE_Explantion) As Explanation FROM SampleData
WHERE CAST(Object_Account as varchar(20))+Subledger+JE_Explantion IN
(
SELECT CAST(Object_Account as varchar(20))+Subledger+JE_Explantion
FROM SampleData
GROUP BY Object_Account, Subledger, JE_Explantion
HAVING COUNT(Object_Account) > 1
)
group by Object_Account, Descriptions, GL_Date, Document_Type, Document_Number,
Company, Subledger, Subledger_Type, Remarks, JE_Explantion

Here are the actual fields for this data, any ideas how to convert this?
 

JHB

Have been here a while
Local time
Today, 18:01
Joined
Jun 17, 2012
Messages
7,732
I've made two solutions in the attached database, run the queries "SolutionOne" and "SolutionTwo", which one do you want to use and what should happen next?
 

Attachments

  • sampDatabase.accdb
    424 KB · Views: 82

penandpaper

Registered User.
Local time
Today, 10:01
Joined
Aug 7, 2017
Messages
20
I've made two solutions in the attached database, run the queries "SolutionOne" and "SolutionTwo", which one do you want to use and what should happen next?


Thanks a lot its done now :)
 

JHB

Have been here a while
Local time
Today, 18:01
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good luck.
 

Users who are viewing this thread

Top Bottom