Re: access 2007 accountancy application

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You are probably familiar with creating queries in design view, but when
subqueries are used within a query its not possible to do it all in design
view. What I posted was the SQL of the query. SQL is the language used for
writing queries. When you create query in design view, if you then switch to
SQL view you'll see it as SQL.

So what you need to do is open the query designer and, without adding any
tables, switch to SQL view. Then just copy the SQL:

SELECT TransID, T1.Date, Description, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.Date <= T1.Date
AND ( T2.TransID <= T1.TransID
OR T2.Date <> T1.Date)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.Date <= T1.Date
AND ( T2.TransID <= T1.TransID
OR T2.Date <> T1.Date)) AS Balance
FROM Transactions AS T1
ORDER BY T1.Date DESC, TransID DESC;

and paste it into the SQL view window. If all your column (field) names are
as you gave them in your original post the only thing you might have to
change is the table name; I called it Transactions, so for the three times it
says Transactions in the SQL change it to your real table name. If any
column names differ then also change them where necessary. If you want the
results returned in ascending rather than descending date order just change
the last line as I described by taking out the two instances of DESC.

Remember that if a table or column name includes any spaces or other special
characters you must wrap it in square brackets, e.g. [My Table]. If in doubt
wrap the table or field name in the brackets; it won't matter if they aren't
actually necessary.

BTW I don't think your English needs much improvement; it is very good
already.

Ken Sheridan
Stafford, England

kofmf wrote:
Thanks Ken for help
The explanations were clear, but formula was less clear
Tables, queries, reports, forms are easy when I read a book, but they are
very difficult when I must use in practice.
I MUST improve my knowledge of Access and English too.
Thanks again for the help

You can compute the sum of credits to date and the sum of debits to date in
subqueries and subtract the latter from the former to give the balance. As
[quoted text clipped - 67 lines]

.

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Future of ADPs
    ... To get there from the main window of SQL EM, click on Tools, then SQL Query ... Regardless of how I feel about it as a design tool, ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Designing Query - Help Please ASAP
    ... The changes made to the SQL of the query result from switching into design ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ...
    (microsoft.public.access.queries)
  • Re: Expression Left in query giving syntax error
    ... trying to change focus from the field of the design grid. ... Next I tried coding the whole SQL expression including the Left function ... directly in the SQL window. ... The query is made of only one single table - so no ambiguities possible. ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)