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.

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:

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

@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

@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.

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!

" 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.

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

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

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

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.

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 …