Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Create if statement query for grouping and summing in Access

I need help with creating a query for grouping and summing based on conditions

I want to group by the following
 GL_CHART_OF_ACCOUNTS.ACCT_NO (group)
 GL_CHART_OF_ACCOUNTS.OPEN_BAL (sum)

if gl_chart_of_accounts.bank_acct = 1 then i don't want to group those accounts

If  GL_CHART_OF_ACCOUNTS.DR_CR_DESIG = D then show GL_CHART_OF_ACCOUNTS.OPEN_BAL as negative
If  GL_CHART_OF_ACCOUNTS.DR_CR_DESIG = D then show GL_CHART_OF_ACCOUNTS.OPEN_BAL as positive

Need to export data to a PostgreSQL database
0
Gerhardpet
Asked:
Gerhardpet
  • 8
  • 6
  • 5
2 Solutions
 
hnasrCommented:
Upload a sample database with few records and an image of the required output.

One idea is to create a query with a column say  bank_acct_for_grouping. This column is filled with ACCT_NO but filled with 1 if bank_acct=1. Group By bank_acct_for_grouping, and all accounts of value 1 will be listed together. Your feedback may clarify any misunderstanding.
0
 
IrogSintaCommented:
I'm assuming you meant that if DR_CR_DESIG = C then OPEN_BAL is positive.
I also don't understand what you mean by not grouping bank_acct = 1.  Did you mean to exclude those accounts?  If so, try this:

Select ACCT_NO, Sum(OPEN_BAL * IIF(DR_CR_DESIG = 'D', 1, -1)) As SumOfOpenBal From  GL_CHART_OF_ACCOUNTS Where BANK_ACCT <> 1 Group By ACCT_NO

Open in new window


If however, you meant that any records with BANK_ACCT=1 should be listed individually and not grouped together, then you need another field that identifies each transactions as unique for each account (or unique for the whole table such as a primary key).  For example of you have a field called SEQ, you can try this:

 
Select ACCT_NO, Sum(OPEN_BAL * IIF(DR_CR_DESIG = 'D', 1, -1)) As SumOfOpenBal From  GL_CHART_OF_ACCOUNTS Group By ACCT_NO, IIF(BANK_ACCT = 1, SEQ, 0)

Open in new window


Ron
0
 
GerhardpetAuthor Commented:
Yes my mistake it is DR_CR_DESIG = C. Thats what I get for copying and pasting :-)

I have attached the database with the G/L accounts

I want to group by ACCT_NO, Not group when BANK_ACCT = 1 and sum OPEN_BAL

and

If  GL_CHART_OF_ACCOUNTS.DR_CR_DESIG = D then show OPEN_BAL as negative
If  GL_CHART_OF_ACCOUNTS.DR_CR_DESIG = C then show OPEN_BAL as positive

I have another field that is unique but that also applies to BANK_ACCT = 0 and when BANK_ACCT = 0 I want ACCT_NO to be grouped. The field is CHART_CURRENCY and for base currency the field is = "" (blank)
gl-accounts.accdb
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
IrogSintaCommented:
Look at this and let me know what you think.  Also I should point out that your GL Account 11120 has both 0 and 1 Bank_Acct types.  I'm not sure if you meant for that to be.
gl-accounts.accdb
0
 
hnasrCommented:
SQL: Sorry did not check previous comment database.
Idea is to get output for  BANK_ACCT=0 grouped and summed, and UNION ALL for BANK_ACCT=1 um-grouped.

SELECT GL_ACCOUNTS.ACCT_NO, Sum(OPEN_BAL*IIf(DR_CR_DESIG="C",1,-1)) AS BAL
FROM GL_ACCOUNTS
WHERE (((GL_ACCOUNTS.[BANK_ACCT])=0))
GROUP BY GL_ACCOUNTS.ACCT_NO;
UNION ALL
SELECT GL_ACCOUNTS.ACCT_NO, OPEN_BAL*IIf(DR_CR_DESIG="C",1,-1) AS BAL
FROM GL_ACCOUNTS
ORDER BY BAL

Open in new window

0
 
GerhardpetAuthor Commented:
@IrogSinta
Yours worked but I realize now that by NOT grouping the Bank accounts it will not work just yet.  By not grouping the banks accounts I have three G/L accounts 11120 and in the new ERP system I can't have duplicates for account numbers.

What would work if I had a way to increment the bank accounts by whatever number I choose. Say I would increment them by 10 then I would have
11120
11130
11140

etc...etc...

Can someone help me with that?
0
 
hnasrCommented:
Any feedback to my comment: http:#a40374789?
0
 
IrogSintaCommented:
Sorry I don't quite understand what you're getting at.  If you can't have duplicate account numbers, why don't you just update them in the table so that they aren't the same?
0
 
GerhardpetAuthor Commented:
@hnasr Yes that is my second option if I can't get it to work with the query from @IrogSinta

@IrogSinta The database I gave you is just some sample data in Access. In the old ERP system is using Pervasive as the database engine and I can't change the G/L account number. The account number is the primary key and part of the index. There are at least 3 other tables where the primary key is used.

So that is why I need to increment the account number in Access.

The attached database has the following bank accounts

11120
11120
11120
11180

So it should look something like this
11120
11130
11140
11180

Perhaps this is not even possible.
0
 
hnasrCommented:
What if "The attached database has the following bank accounts"

11120
11120
11130
11180

How do you want it to look like?

This update can be done using a recordset. But you need to specify the rule for such processing.
0
 
GerhardpetAuthor Commented:
What is need is so that none of the back accounts are the same.

So it could look like this
11120
11121
11130
11180

Right now I'm trying the union all query. That may work better but still don't know how
0
 
hnasrCommented:
" That may work better but still don't know how "
The union, in this issue,  joins two queries. The first query is a group by query where BANK_ACCT =0; the second is a normal select query where BANK_ACCT=1.

You are invited to comment back with any doubts.

The issue of updating the duplicate accounts can be done using VBA. But as you showed it can be tricky for selecting which increment to add to the second duplicate account.
0
 
IrogSintaCommented:
The only way I could think of is to create a cross reference table using the Account Names and assigning them to the new GL Account numbers.  Look at this database and see if this works for you:
gl-accounts.accdb
0
 
hnasrCommented:
Revisited the UNION ALL, gave more records than what exists in table.

Other way, is to have an ACCT_NO_EXTRA field in table GL_ACCOUNTS.
Run code to fill this new field by adding an increment. In this example I added increment of 1.
Form1, Command0_Click event to fill ACCT_NO_EXTRA field
Option Compare Database

Private Sub Command0_Click()
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim i As Integer
    Dim j As Integer
    Set rs1 = CurrentDb.OpenRecordset("Select ACCT_NO From GL_ACCOUNTS GROUP BY ACCT_NO ORDER BY ACCT_NO")
     
    Do While Not (rs1.EOF)
        Set rs2 = CurrentDb.OpenRecordset("Select * from GL_ACCOUNTS WHERE ACCT_NO='" & rs1("ACCT_NO") & "'")
        rs2.MoveLast
        rs2.MoveFirst
        'If rs2.RecordCount > 1 Then
            j = 0
            'rs2.MoveNext
            Do While Not (rs2.EOF)
                j = j + 1
                rs2.Edit
                    rs2("ACCT_NO_EXTRA") = j
                rs2.Update
                rs2.MoveNext
            Loop
        'Else
            'rs2.Edit
                'rs2("ACCT_NO_EXTRA") = "0"
            'rs2.Update
        'End If
        rs2.Close
        rs1.MoveNext
    Loop
    rs1.Close 
End Sub

Open in new window

Query1 groups by ACCT_NO, ACCT_NO_EXTRA.
SELECT GL_ACCOUNTS.ACCT_NO, GL_ACCOUNTS.ACCT_NO_EXTRA, Sum(OPEN_BAL*IIf(DR_CR_DESIG="C",1,-1)) AS BAL
FROM GL_ACCOUNTS
GROUP BY GL_ACCOUNTS.ACCT_NO, ACCT_NO_EXTRA
ORDER BY ACCT_NO, ACCT_NO_EXTRA;

Open in new window

gl-accounts-2.accdb
0
 
GerhardpetAuthor Commented:
Ok the cross reference table will be the easiest. Now what I need is create the table and want to do a lookup on my G/L accounts table. I'm using the wizard to create the look from the G/L accounts table for the ACCT_NO but what I want to do is lookup on ACCT_NO and have the Name of the account populate the second row as well. This is so that I will never make a mistake when I do I look up for my cross reference table

The reason is that I link from G/L table to the cross reference table on ACCT_NO and then to my other table for the new system by the NAME to get to the NEW_ACCT_NO

Make sense?
0
 
IrogSintaCommented:
Sorry, I'm quite confused with your description.
0
 
hnasrCommented:
Try this:
OPEN Form1 and Click Command0

It works by creating  a temp table to hold GL_ACCOUNTS, if you have no access to modify, and add a new column ACCT_NO_EXTRA

Code fills ACCT_NO_EXTRA with increments to uniquely identify each duplicate record in ACCT_NO.

balances query joins the temp table with a balances data set.

Finally balances query is displayed.
Here is the code:
ption Compare Database

Private Sub Command0_Click()
    Dim sql As String
    'create tempTable
    DoCmd.SetWarnings False
    sql = "SELECT GL_ACCOUNTS.* INTO tempAccounts FROM GL_ACCOUNTS;"
    DoCmd.RunSQL sql
    'add ACCT_NO_EXREA column
    sql = "ALTER TABLE tempAccounts ADD COLUMN ACCT_NO_EXTRA TEXT(3);"
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True
    'Fill ACCT_NO_EXTRA in tempAccounts
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim i As Integer
    Dim j As Integer
    Set rs1 = CurrentDb.OpenRecordset("Select ACCT_NO From tempACCOUNTS GROUP BY ACCT_NO ORDER BY ACCT_NO")
     
    Do While Not (rs1.EOF)
        Set rs2 = CurrentDb.OpenRecordset("Select * from tempACCOUNTS WHERE ACCT_NO='" & rs1("ACCT_NO") & "'")
        rs2.MoveLast
        rs2.MoveFirst
        'If rs2.RecordCount > 1 Then
            j = 0
            'rs2.MoveNext
            Do While Not (rs2.EOF)
                j = j + 1
                rs2.Edit
                    rs2("ACCT_NO_EXTRA") = j
                rs2.Update
                rs2.MoveNext
            Loop
        'Else
            'rs2.Edit
                'rs2("ACCT_NO_EXTRA") = "0"
            'rs2.Update
        'End If
        rs2.Close
        rs1.MoveNext
    Loop
    rs1.Close
    DoCmd.OpenQuery "balances"
End Sub

Open in new window


Balances query:
SELECT Query1.ACCT_NO, tempAccounts.NAME, Query1.BAL
FROM (SELECT t.ACCT_NO, t.ACCT_NO_EXTRA, Sum(OPEN_BAL*IIf(DR_CR_DESIG="C",1,-1)) AS BAL FROM tempAccounts AS t GROUP BY ACCT_NO, ACCT_NO_EXTRA ORDER BY ACCT_NO, ACCT_NO_EXTRA)  AS Query1 INNER JOIN tempAccounts ON (Query1.ACCT_NO = tempAccounts.ACCT_NO) AND (Query1.ACCT_NO_EXTRA = tempAccounts.ACCT_NO_EXTRA)
ORDER BY Query1.ACCT_NO, Query1.ACCT_NO_EXTRA;

Open in new window

gl-accounts-3.accdb
0
 
GerhardpetAuthor Commented:
I want to avoid using forms and making this too complicated. The Access database is only for converting data from a Pervasive to a PostgreSQL database.

So I'm using the cross reference table and UNION ALL for the bank accounts and non bank accounts grouping the non bank accounts.

Thanks for all of the help.
0
 
hnasrCommented:
Welcome!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now