473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

MS Access 97 - Decrementing total in a query

First, thanks for having a look at this post.

I am trying to create a query that shows decrementing account balances. It reads the following fields from my tbl/Activity table:
Customer ID, Week #, Activity Date, Account Balance, Amount Charged

I need to export the results of this query to a MS Excel spreadsheet so that I have the following:
Customer ID, Week #, Activity Date, Account Balance, Amount Charged
001, 40, 10/3/2007, 5000, 300
001, 40, 10/4/2007, 4700, 600
001, 40, 10/5/2007, 4100, 100
001, 41, 10/7/2007, 4000, 500
...

Is this possible using a query. I have done queries where I can do math on the same record but this needs to carry along the balance from previous record to the next. I can get this done in a report but then I can't find a way to create the one line per record excel file I want in the end.

Help, suggestions, sample code; any / all are appreciated.
Oct 11 '07 #1
1 1403
nico5038
3,080 Expert 2GB
First, thanks for having a look at this post.

I am trying to create a query that shows decrementing account balances. It reads the following fields from my tbl/Activity table:
Customer ID, Week #, Activity Date, Account Balance, Amount Charged

I need to export the results of this query to a MS Excel spreadsheet so that I have the following:
Customer ID, Week #, Activity Date, Account Balance, Amount Charged
001, 40, 10/3/2007, 5000, 300
001, 40, 10/4/2007, 4700, 600
001, 40, 10/5/2007, 4100, 100
001, 41, 10/7/2007, 4000, 500
...

Is this possible using a query. I have done queries where I can do math on the same record but this needs to carry along the balance from previous record to the next. I can get this done in a report but then I can't find a way to create the one line per record excel file I want in the end.

Help, suggestions, sample code; any / all are appreciated.

You can do so by using the DSUM() and DMin() function to total the Charged and subtract that from the DMin(Balance) as you need to have a starting balance somewhere....
It's however slowing down the query and I wouldn't do this in Access, but using a formula in Excel after the export.

Nic;o)
Oct 12 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
4
by: Sondra Wilson | last post by:
I have created a sorted query. The total(records) I get in the query is correct. My problem lies with getting that total into my report. I use count to insert the total number of records in my...
2
by: Kailey11 | last post by:
This probably is a basic question for most of you, but I am setting up an very simple database - person, date traveled, location and milage - using ACCESS 2000 and I want to add up the milage in...
0
by: jphelan | last post by:
I have a subform that works fine until you import it into a new database when it crashes if you try to open it in either disign or form view. The form, "Attendees_Subform" in my application was...
1
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own...
25
by: Shannon Jacobs | last post by:
The OL tag still allows for a START value, but that is now deprecated. I've found sound references that suggest the proper technique now is to control it with a style for the OL in quetion, but I...
13
by: Ulv | last post by:
I have a table (TblItems) with fields Lenght, Width and Height as decimalfields. I also have the fields Desc, a text field. In a form I have created this line of code after updating: Desc =...
4
by: pokerboy801 | last post by:
OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three...
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.