Consolidating like data together (1 Viewer)

cowenpa

Member
Local time
Today, 20:03
Joined
Apr 7, 2021
Messages
38
HI there - I have a query as follows:
1621341789374.png

which returns
1621341824279.png

Where the P&L Group is the same I'd like the total to be added together and just show 1 entry so for example C Technology Fees Aff. just show one line with a total of -8,339 + 8015 (-384) and 1 line for C Materials & Services Affiliate (Project) and C Prov.Pend.Losses Cust.Con with the relevant total.

In another subform it will be possible to expand this out to see what that P & L Group for that Department and Income Statement section is made up of so for example click on C Technology Fees Aff and see both entries shown. The data above is just a small sample of the full data so there could be a lot of lines making up one P&L Group sometimes with different FA_&_GL

P.S I will likely remove out any null/zero value lines later but am keeping them in for now for testing purposes.

Thanks

Paul
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:03
Joined
May 7, 2009
Messages
19,169
is it possible to remove the FA_&_GL from the group and just Group on the "main group"?
 

plog

Banishment Pending
Local time
Today, 15:03
Joined
May 11, 2011
Messages
11,611
And what [FA_&_GL] value do you want returned for [P&L_Group]="C Materials & Services 3rd party (Project)"?

You really need to clean up your field names--only use alphanumeric characters (no ampersands, no spaces, etc.)
 

bastanu

AWF VIP
Local time
Today, 13:03
Joined
Apr 13, 2010
Messages
1,401
Please have a look at my free utility (http://forestbyte.com/ms-access-utilities/fba-pivot-table-designer/) that would help you to easily analyze your data by allowing to easily create a pivot table in Excel based on your query. The beauty of that is that it allows drill down on any pivot table cells and your data is independent of your real one (the raw data gets exported to the Excel file itself so it is self-contained).

Otherwise as arnel said just remove the FA_&_GL from the group by.

Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Feb 19, 2002
Messages
42,973
You are grouping by the amount field. You need to change that to Sum()

Looks like you didn't take our advice to normalize the schema. You will end up with a large number of "duplicate" queries/forms/etc using your method. Changes will become problematic.
 

cowenpa

Member
Local time
Today, 20:03
Joined
Apr 7, 2021
Messages
38
@Pat as already mentioned the data comes from Finance team and from existing spreadsheets so I have to work with them to normalize the data - not the kind of thing that can be done instantly but I have already discussed it with her and she will be working on it - as it is I'm working with what I've got
 

cowenpa

Member
Local time
Today, 20:03
Joined
Apr 7, 2021
Messages
38
And what [FA_&_GL] value do you want returned for [P&L_Group]="C Materials & Services 3rd party (Project)"?

You really need to clean up your field names--only use alphanumeric characters (no ampersands, no spaces, etc.)
Yes I have also explained that field names should not have spaces .. and will work with finance to update their original spreasheets which will be imput into the database as forecasts become actuals..
 

cowenpa

Member
Local time
Today, 20:03
Joined
Apr 7, 2021
Messages
38
Hi - removing the FA and GL did the trick
1621395409665.png

Where forecast has a value for Feb through December and the user can pick a tab with a matching name e.g. tab 0 is Feb, tab 1 is Mar is there a way I can show the relevant query without having to update the VBA with an SQL line with a case statement for each tab? If not then not a problem I can do that upon an update

Thanks

Paul
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Feb 19, 2002
Messages
42,973
as already mentioned the data comes from Finance team and from existing spreadsheets so I have to work with them to normalize the data - not the kind of thing that can be done instantly but I have already discussed it with her and she will be working on it - as it is I'm working with what I've got
I realize that you can't necessarily alter the format of your input files. How the data comes to you should not influence how you build the app. Normalize the data as you import it. fix the column names as you import. Is it better to have 12 queries that select each of the 12 months or one query that either selects all months or a specific month using an argument?

Is using 12 queries causing you to make 12 forms?
 

Users who are viewing this thread

Top Bottom