I've been reading over all the examples I can find and I am not making a lot of headway. In one table we have charges and payments. They can be on any of a number of accounts. Like the "checkbook" request I found, there can be several on the same day and it is likely that though "payment #5" on "account 2" might be dated for today, I might find an expense I have to charge on an account that is for a previous date.

In what I attempted, if I only have one account I could use the date condition and at least get by with it, or use the condition area for the account number and then everything would be the total for the account (where I really rather a true running balance for historical reasons), and if I dare combine the two conditions (account number AND date) I got complete nonsense.In my query the account field came before the date, so I thought I could throw in first an account condition and then using logical AND the date condition, but un-unh.

Does anyone know of an example that resembles one table for more than one checking-like account that a form can be pulled from which presumably based on a query provides the payments for one account sorted by date with a running balance, or do I have to attack this from another angle like somehow generating a dataset in memory for just one account number and then sorting it (which seems to me becomes more of a report than a table and would create more involvement if I had to edit items)?

Hi Bruce

From your description it sounds like you are trying do all of these actions in one table?

If you are then I would suggest that your tabe is nor normalised

Can you Compact & Repair and then upload your Dbase?

Regards

Mike

OK, here is the db, but don't expect anything pretty. If you go to the "favorites" area and open that form, you will get an idea of what I am molding at this point. The present focus is the tab labeled "Payments", which is the last one, and houses a subform named "quPaymentsTable Subform"* in the forms area. In the form I was tinkering with putting the running total in an unbound text box, which is the unlabeled one pretty much in the center column between "Payment/Credit" and "Amount". Referring to the table or query you will note that all values are input as positive, but a check-box differentiates between a debit and a credit. When I design the input screens I intend to have separate for payments received vs. charges and the checkbox will be marked by the code. If you would please focus on the table "tblPayments" and the query "quPaymentsTable", we will be addressing the issue as I see it, then if I am somewhere totally lost in the woods I welcome redirection.

Referring to the query you will see two columns that have fields using the IIF function (pmtDebit and pmtCredit) - which are strictly for display, so when viewed in the form I can make it look more "ledger-like", then intending the next column to be the running balance that would be based on the difference between the debits and credits at any given point for the account that is being displayed. As you will see, the last column houses the expression - Debit_Balance: DSum([Pmt_Debit],"quPaymentsTable",("[pa_li_Account] = " & [pa_li_Account]) And ("[Pmt_dt_Date]<=#" & [Pmt_dt_Date] & "#")). If I get rid of the date portion, I at least get the correct break-off for the account numbers when I open the query, but try and run it with just the date expression (without the account break) and the numbers make no sense. Run with both make less sense.

And if you can tell me why my combo box lookups in the first and third tabs work (change all the relative information in the tabbed subform) but the second one does not, great, but it isn;t what is giving me today's headache!

*Yes, I know I need to be consistent and forms should start with "frm", but I dove in after being away from this for well over a year and I have some glaring startup mistakes in my form. Strange how Access itself gives such an awful prefix for a form from it's form wizard, making the prefix appearing to me to indicate a query...


Edited by: bem on Mon Dec 17 19:53:50 EST 2007.

Edited by: bem on Mon Dec 17 19:57:47 EST 2007.

I can't help but be concerned over what I have read of the inefficiency of the DSUM() function vs. the given examples of using a SELECT query. The SELECT query is presently flying way over my head, but it appears I should study, learn and use that instead.

Is the criticism of DSUM() considered valid by all?

Is the SELECT query something that with enough study I should be reasonably able to grasp and use for an increasing running total to be displayed in a form given two conditions being 1) just the records for one account number to be 2) broken by date and items within the date?

If so, I suspect I should jump ships immediately rather than spend everyone's time on making DSUM() work in this example.

Sincere Thanks,
-old Bruce

Thank-you for all the thought and work you folks have performed! I wanted everyone to know that I finally found a link to an explanation of how to handle an SQL subquery that I (even) could easily understand and it is phenomenal (I will reference it later, but it IS in these forums!!!)! Not only does the explanation break it down so I easily could understand the structure, but after modifying it to fit my purpose and running it (the only "trick" was to establish the correct kind of query, which I honestly can't recall how to do properly but I will note soon when I do...), then once done, opening that new one up in design view and adding all the fields I wanted shown, then creating the same basic continuous form off of that new query!

[censored], I hate my fading senile memory... (personal, not the computers...).

Ah ha. The enlightening pages are:
http://www.accessdb.info/index.php?option=...or+your+vote%21
and
http://www.ehow.com/how_13580_create-sql-query.html

The first is the generic code for the subquery that is very tweakable, the second is how to create the select query to enter the code, which then is transformed into more of a GUI based arrangement that you can tweak, adding in other fields, and use as the basis for a form.