Senin, 13 Januari 2014

Re: [MS_AccessPros] Getting "Balance" query

 



Khalid,
 
I only use Access for my own purposes, and am no expert, but I investigated your latest query as it uses Dcount, which I have never used but wondered if it would be useful to me.
 
Your query seems to be intended to display all the transactions from a particular client, and your last three fields as totals for all that client's transactions.
 
After much experimentation with quotes and brackets, I conclude that Dsum does not recognise [Enter Client ID] as a parameter to be entered. Access Help says that Dsum only recognises existing fields.
 
I tried splitting your query into two, with Query1 displaying the fields without your final three, and Query2 using Dsum applied to fields of Query1, but again I got an error saying [Enter Client ID] can not be found, even though this term did not exist in Query2.
 
All I was able to do is use Query1 to display the individual records, and make Query2 use Sum of fields in Query1 to give the answers you require. I also added all the other fields and put "Last" instead of "Group By" in the Design Grid - this gave one transaction, but it wasn't the last row in MiscAccount, it was the last one I had edited. Sorting Query1 did not change anything.
 
Here are my SQL's:
 
Query1:
SELECT MiscAccount.TransactionID, MiscAccount.TransactionDate, MiscAccount.ClientCIN, MiscAccount.ClientName, MiscAccount.Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, MiscAccount.Expenses, MiscAccount.AmountReceived
FROM MiscAccount
WHERE (MiscAccount.ClientCIN) Like [Enter Client ID];

Query2:
SELECT Query1.ClientCIN, Query1.ClientName, Last(Query1.TransactionID) AS LastOfTransactionID, Last(Query1.TransactionDate) AS LastOfTransactionDate, Last(Query1.Details) AS LastOfDetails, Last(Query1.ConsignmentNo) AS LastOfConsignmentNo, Last(Query1.Cartons) AS LastOfCartons, Last(Query1.Weight) AS LastOfWeight, Last(Query1.Expenses) AS LastOfExpenses, Last(Query1.AmountReceived) AS LastOfAmountReceived, Sum(Query1.Expenses) AS SumOfExpenses, Sum(Query1.AmountReceived) AS SumOfAmountReceived, Sum([AmountReceived]-[Expenses]) AS Balance
FROM Query1
GROUP BY Query1.ClientCIN, Query1.ClientName;

Dave W
 
----- Original Message -----
Sent: Monday, January 13, 2014 7:33 AM
Subject: Re: [MS_AccessPros] Getting "Balance" query

 

John,
I'm still floating in this query

SELECT TransactionID, TransactionDate, ClientCIN, ClientName, Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, Expenses, AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") - DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'")  AS Balance
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

I am getting values for Expenses and AmountReceived, but getting #Error for TotCash, TotExpense and Balance

Khalid


On Saturday, January 11, 2014 10:42 PM, John Viescas <JohnV@msn.com> wrote:
 
Khalid-

That clearly is NOT the datasheet view of the SQL you posted.  Your SQL asks for columns TransactionID, TransactionDate, ClientCIN, ClientName, Details, ConsignmentNo, Cartons, Weight, Expenses, AmountReceived, TotCash, TotExpense, and Balance.  Are you getting any values for Expenses and AmountReceived?  You do need to add a filter on ClientCIN to all the DSUM expressions.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jan 11, 2014, at 6:24 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,
That is OK now.

Now we are left with 2nd query (ClientsBalanceSheetMiscAccountQuery) which is upto now as follows:

SELECT MiscAccount.TransactionID, MiscAccount.TransactionDate, MiscAccount.ClientCIN, MiscAccount.ClientName, MiscAccount.Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, MiscAccount.Expenses, MiscAccount.AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID])-DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS Balance
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

Showing datasheet view as:

TransactionID Expenses AmountReceived TotCash TotExpense Balance
8 4607 Null #Error #Error #Error
9 Null 4300 #Error #Error #Error

The final thing is that we need a final query for a report, are we working on the right track for our target?

Khalid


On Monday, January 6, 2014 1:57 PM, John Viescas <JohnV@msn.com> wrote:
 
Khalid-

Just because you added that code doesn't mean you don't have null values in TransactionID.  What do you see when you open ManagersAccounts in Datasheet view and sort on TransactionID?  If you see any "blank" fields, that's your problem.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jan 6, 2014, at 8:33 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

I have tried this code like this:

Private Sub TransactionDate_GotFocus()
    ' If on a new record,
    If Me.NewRecord Then
        ' Assign the "next" value to the ID
        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
    End If
End Sub

Query is the first one which you are saying should work unless there are null values in TransactionID field.

Now with this code i dont have null value in TransactionID field, BUT still not getting value in "balance"

Khalid


On Monday, January 6, 2014 11:37 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:
 
John,
First of all i should mention, that query was other database "PCTL" and now i working on other database "PCTL Accounts".

Yes there would be a null value if it is the first entry on this form that was the reason i mentioned about the code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If on a new record,
    If Me.NewRecord Then
        ' Assign the "next" value to the ID
        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
    End If

The reason why i included this code is with reference to my previous thread in which you suggested this code on 2 Jan see below:

Jan 2 at 5:45 PM
Khalid-

First of all, there is a function called IsNothing, but it requires an object as an argument, not a field or control.

As the article implies, you first have to change your AutoNumber field to Long Integer, but I recommend you make a backup copy of the table first.

Your code needs to go in the BeforeUpdate event of the form.  It should look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If on a new record,
    If Me.NewRecord Then
        ' Assign the "next" value to the ID
        Me.TransactionID = NZ(DMax("TransactionID", "Transactions"), 0) + 1
    End If
End Sub

… where "Transactions" is the name of your table.

Hope you can understand me now.

Khalid


On Monday, January 6, 2014 2:15 AM, John Viescas <JohnV@msn.com> wrote:
 
Khalid-

The first query should work unless there are Null values in the TransactionID field in any of the rows.

And your code is wrong.  It should be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If on a new record,
    If Me.NewRecord Then
        ' Assign the "next" value to the ID
        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
    End If
    '-------------------------
    Dim msg As String
    msg = msg & "Do you want to Save this record?"
    If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbNo Then
        Cancel = True
        Me.Undo
    End If
End Sub

I have no clue why you included the code - it has nothing to do with the query.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
http://www.viescas.com/ 
(Paris, France)




On Jan 5, 2014, at 5:44 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,
Sometime in the near past i had a problem to get balance on a continuous form where i had "TransactionID" field, key, Auto Number, which was resolved by John thanks to him again.
Which was:
SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID])-DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS Balance
FROM ManagersAccounts
ORDER BY ManagersAccounts.TransactionID;

But now i have an other form where TransactionID field is key, Number, Long Integer,  and forms before update is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If on a new record,
    If Me.NewRecord Then
        ' Assign the "next" value to the ID
        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
    End If
    '-------------------------
    Dim msg As String
    msg = msg & "Do you want to Save this record?"
    If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbYes Then
        DoCmd.Save
    Else
        Me.Undo
    End If
End Sub

While using the above query i get error message:
Syntex error (missing operator) in expression 'TransactionID <='.

this error message has to be clicked two times on OK button.

I tried this modification in the query and i do not get error message but output is not correct in balance i get the same figure on each row which was on first row balance:

SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID")-DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS Balance
FROM ManagersAccounts
ORDER BY ManagersAccounts.TransactionID;

Need help how to modify it.

Khalid












__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)
Recent Activity:
.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)