Consolidating like data together

[cowenpa]

HI there - I have a query as follows:

which returns

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]

is it possible to remove the FA_&_GL from the group and just Group on the "main group"?

 

[plog]

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]

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,

 

[cowenpa]

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

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]

Hi - removing the FA and GL did the trick

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