Running Total from 2 columns in different tables (1 Viewer)

michibahn · Sep 22, 2011

Hi guys, I'm really new to db programming so please bear with me.

I need to add a new column (named BALANCE) in my query to sum/subtract my existing 2 columns namely QTY IN and QTY OUT.

Attached is a snapshot on what I have in excel using the formula =SUM(A1,-B1) in the 1st row then on the 2nd row =SUM(C1,A2,-B2) to calculate the BALANCE column "in EXCEL"

I need it to do these calculactions in MS ACCESS. Please help me. Attached is my DB qryRunTotal

TIA!

Alansidman · Sep 22, 2011

Here is a solution that works.

http://www.fabalou.com/Access/Queries/RunningTotalQuery.asp

Alan

michibahn · Sep 23, 2011

Alansidman said:
Here is a solution that works.

http://www.fabalou.com/Access/Queries/RunningTotalQuery.asp

Alan

Hi Alan,

Thanks for your reply. I really have no idea on how to apply this in my qryRunTotal query. Will it do the add/subtract function in my 2 columns to create the running total?

Would you be able to look at my DB?

Thanks a lot!

Alansidman · Sep 23, 2011

I will look at it as time allows.

Alan

michibahn · Sep 23, 2011

Alansidman said:
I will look at it as time allows.

Alan

Hi Alan,

I'm woking on the qryRunTotal query to make a new BALANCE Column from the QTY in and QTY OUT columns.

I've also posted the snapshot of the results I want to achieve in my first post as well.

Thanks a lot for your time.

Alansidman · Sep 23, 2011

First off, if you want to have a running sum, you will need to put the Inventory receipts and Inventory shipments in the same table. You will also need to set that table with a unique record number (usually this is an autonumber that is used only for Access to identify records). Once you have this in place, then you can run a query using the SQL statement format shown in the link I presented.

Your current structure will not allow this as there are no unique identifiers for both tables that will allow for sequential running sums.

If after restructuring your db, you need additional help with this, then post back with your db attached.

Alan

Alansidman · Sep 23, 2011

Here is a db template that I developed for a check book. It has a query transactions that employs the running sum solution I gave you. Look at how the table and query and the report are set up to get a running sum by date.
When you download it you will need to put both files in the same directory to open the db.

I hope this helps you to understand the solution better.

michibahn · Sep 23, 2011

Alansidman said:
First off, if you want to have a running sum, you will need to put the Inventory receipts and Inventory shipments in the same table. You will also need to set that table with a unique record number (usually this is an autonumber that is used only for Access to identify records). Once you have this in place, then you can run a query using the SQL statement format shown in the link I presented.

Your current structure will not allow this as there are no unique identifiers for both tables that will allow for sequential running sums.

If after restructuring your db, you need additional help with this, then post back with your db attached.

Alan

Hi Alan,

Thanks for your inputs. I will restructure my DB based on your recommendation. So basically, in my qryRunTotal query, it would call only 1 table with unique identifiers as an additional column? would this be a sort of ID number (AutoNumber)?

TIA!

Alansidman · Sep 23, 2011

Correct. In my checkbook example, you can see this when you open the query in design view and then look at it in SQL statement view.

Alan

michibahn · Sep 23, 2011

Alansidman said:
Correct. In my checkbook example, you can see this when you open the query in design view and then look at it in SQL statement view.

Alan

Thank you Alan. I will try to work on it.

Appreciate your help.

michibahn · Sep 23, 2011

Hi Alan,

I tried opeing your checkbook DB but it is like in Form type only. I can't see any query in the DB.

thanks!

Alansidman · Sep 23, 2011

Click on the F11 key. For Production, there is no reason for users to see tables, etc.

michibahn · Sep 23, 2011

Alansidman said:
Click on the F11 key. For Production, there is no reason for users to see tables, etc.

oh ok.. i saw it already thanks!

michibahn · Sep 26, 2011

Hi Alan,

My problem now is i can't create a UniqueKey in my tables. My source data is a TXT file and I'm just running a TransferText macro to create these tables.

Hope you could check my DB if you have time.

TIA!

Alansidman · Sep 26, 2011

I looked at the file you have in the original thread. It appears to be unchanged for Ins and Outs. Still have a couple of tables. Have you restructured to one table? Why can't you go into design view for the table, insert a row, call it RecID or similar and make it an autonumber. Also if you are importing the information using the wizard, it gives you an opportunity to create a unique record ID. Why are you not doing this.

Alan

michibahn · Sep 26, 2011

Alansidman said:
I looked at the file you have in the original thread. It appears to be unchanged for Ins and Outs. Still have a couple of tables. Have you restructured to one table? Why can't you go into design view for the table, insert a row, call it RecID or similar and make it an autonumber. Also if you are importing the information using the wizard, it gives you an opportunity to create a unique record ID. Why are you not doing this.

Alan

Hi Alan,

Sorry I made a mistake in my post. I was able to insert an new COLUMN named ID on both NGK-In and NGK-OUT tables. However, on the FINAL table, the new column ID is removed when I run the NGK FINAL INVENTORY query to create this table.

Kindly see my update DB in this post.

TIA!

Alansidman · Sep 26, 2011

Open your new table that you made in design view. Insert a new field "RecordID" and make it an autonumber and primary key. Save it. Use this table for your running sum query.

BTW: I don't see any quantities out in your final table or in the make table query.

Alan

Alansidman · Sep 26, 2011

Had a few minutes. Attached is your database. Things I did
I did an update query to multiply all quantities out in the Out table to negative numbers. Did the same for the pallets.

I then created a Union All query to join the two tables (in and out)

I then used the Union All query to make a new select query that I was then able to sort on the date and made it a make table query. I created a new table (new table) with the combined ins and outs. Next in the New Table. I inserted a field Record ID and made it an autonumber.

Now you should be ready to go forward using these same concepts and ideas.

Alan

michibahn · Sep 29, 2011

Hi Alan,

Thanks for spending your minutes to do this. Question for you..

1. If I run Query5 to make the NewTable, the RecordID will disappear since it will overwrite it every time you run Query5.

2. Can we show the DATE OUT table and the PALLET OUT table separately in the NewTable? I guess you computed everything in 1 line to get the Sum.

Really appreciate all your efforts.

Thanks!

Alansidman said:
Had a few minutes. Attached is your database. Things I did
I did an update query to multiply all quantities out in the Out table to negative numbers. Did the same for the pallets.

I then created a Union All query to join the two tables (in and out)

I then used the Union All query to make a new select query that I was then able to sort on the date and made it a make table query. I created a new table (new table) with the combined ins and outs. Next in the New Table. I inserted a field Record ID and made it an autonumber.

Now you should be ready to go forward using these same concepts and ideas.

Alan

Alansidman · Sep 29, 2011

Once you have the new table, combined, use that table as your recordset to which you link a form to input new data. I never intended that you should run this procedure over and over to get your end result. It was a means to normalize your data so you could go forward. Lose your old tables and use the new one. Otherwise you will be in for a long chaotic challenge to maintain your database. Get it right and use it.

Alan

Running Total from 2 columns in different tables (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Attachments

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

AWF VIP

Attachments

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread