Reports: Summarising multiple column data from tables into rows in reports

[nnabbale]

Hi all

I would really appreciate help in trying to crack this - Im new to Access but i will do my best to explain it as clearly as possible

I have the following structure

Project > Multiple Units>Multiple Sites>Multiple Savings Types

I have created the tables to enter the records and they are working well as in the table structure below in columns and the records are entered in rows. However I would like to report all data for each project in one row as summaried in the reports table below, but every time i pick up the data from the tables to create the reports, there are multiple entries for each report for each of the types, how do i summarise all the data per project to consolidate into one row and not multiple rows?

Tables
Project ----Unit -----Site ----Type----- Saving
A----------1-------- X------- K-------- 2
A----------1-------- X-------- L-------- 3
A----------1-------- Y-------- K --------2
A----------1-------- Y-------- L--------- 1
B---------- 1------- -X-------- K --------3
B---------- 1------- X--------- L-------- 4
B---------- 1------- Y--------- K------- 2
B ----------1------- Y--------- L-------- 1
Report (that I would like)
Project----- Unit------ Site ----Type K---Type L
A----------- 1 ---------X ------2 ----------3
A -----------1 ---------Y ------2 ----------1
B -----------1 ---------X ------3 ----------4
B----------- 2--------- Y ------2 ----------1

Thanks - my db is attached for reference

Nabbale

 

[pbaldy]

What table? You might get that with a crosstab query, if you want to play with that wizard. Your data is not properly normalized, with the field for years and what I assume are months (P1 - P12).

 

[nnabbale]

Thanks, I have tried crosstabs but Im just gambling and not sure what to do.

In the table [Saving], for the same ProjectID there are multiple [Site] and for each site multiple [Plan Type], each split from P1 to P12

They are entered into the table as different rows, when i report them i would like to have the [projectid] and the different plan sites in one row not separate rows for each plan type.

Also im not sure how to normalise the data, appreciate your help

thanks

 

[nnabbale]

Sorry to be a pain, I would really appreciate anyone looking at this for me

thanks
Much appreciated

 

[pbaldy]

Sorry, holiday weekend. Normalized data wouldn't have fields for years, types, months, etc. Given the sample data in the Saving table, what are you hoping to see?

 

[nnabbale]

Hi Paul

thanks for getting back to me

the concept behind the saving table is as follow
Every project has savings (£s) over a 12 months period (P1 to P12), I want to be able to capture the savings broken down per period e.g if the savings is £120 a year then i would capture £10 for each period.

I would then like to sum this in a report for each type by project

Is that any clearer?

thanks