Solved

Create if statement query for grouping and summing in Access

Posted on 2014-10-10
19
273 Views
Last Modified: 2014-10-13
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
Comment
Question by:Gerhardpet
  • 8
  • 6
  • 5
19 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 40374436
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40374460
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40374758
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40374771
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
 
LVL 30

Accepted Solution

by:
hnasr earned 250 total points
ID: 40374789
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40375276
@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
 
LVL 30

Expert Comment

by:hnasr
ID: 40375294
Any feedback to my comment: http:#a40374789?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40375310
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40375600
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 30

Expert Comment

by:hnasr
ID: 40375621
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40375625
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40375653
" 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
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 40375709
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40375938
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40376261
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40376277
Sorry, I'm quite confused with your description.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40377644
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40377979
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40378374
Welcome!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now