New to Access, How to Sum up columns in final row.

[Enemee]

Hi, I have a Pivot table in the form :

A B C Total
A 1 2 3 6

B 4 5 6 15

C 7 8 9 24

Total ? ? ?

How do I fill in the ?'s
Ive tried displaying as row headings but it groups the totals together and this is not that I want. How do I go about this?

Thanks

[June7]

You can build a report that will do the aggregate calcs.

Or

Open query (or bound form in Datasheet view), click the Totals button (looks like sigma) in the Records section of Home tab. In bottom row under each field, select the aggregate function.

[Enemee]

Thanks,
But is there a way to show the indivual values? For the row and column cross sections I want the names of the people there rather than the count. And in the totals I want the count. I can do this for the row totals but not for the column totals:

How ,,,,,,,,,,,DNM,,,,,,,,,,,Meet,,,,,,,,,,,,Exceed,,, ,,,,,,,,,Total
What
DNM,,,,,,,,Associate 1,,,,,Associate 2,,,,,,Associate 3,,,,,,,,50%
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,Associate 4,,,,,,,,,
Meet,,,,,,,Associate 5,,,,,Associate 6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,25%
Exceed,,,,Associate 7,,,,,,,,,,,,,,,,,,,,,,,,,,Associate 8,,,,,,,,,25%
Total,,,,,,,,,,,37.5%,,,,,,,,,,,,,25%,,,,,,,,,,,,,,,37.5%,,,,,,,,, ,,,,100%

Totals are the sum of the respected (columns/rows)/Total Number of Associates.

I have a database with this information listed on. How would I go about making a report/query of this?

[June7]

You said you have a pivot table (crosstab query?) that you want totals for. Now this looks like something very different. I doubt a crosstab will get you what you want. If you want detail info, need to build a report using Grouping & Sorting with aggregate calcs. Show sample source data.

[Enemee]

Yea I am using a Pivot Table. This is a project for work so I do not have permission to upload it for you, sorry. Is there a chance you could walk me through it?

[June7]

Don't need actual data, just the table structure, dummy records wouldn't hurt but not critical. You could copy the db, delete real data, or just type some representation of the source table into a post. As I said, doubt pivot table will serve, build report using Grouping & Sorting with calcs. I usually say check Access Help but 2010 Help sucks bad. Find web tutorial on designing reports or get an introductory reference book. Report design is elementary Access functionality. Here is one for starters http://office.microsoft.com/en-us/ac...010006894.aspx

[Enemee]

Here is a representation of the data:
Associate.....Band.........What.................Ho w
1................5..............Meet.............. ....Did Not Meet
2................6..............Exceed............ ....Exceed
3................7..............Did Not Meet.........Meet
..................8............................... .......
.
.
191

There are a total of 191 Associates
There are 4 Bands which I need an option to filter table to select individual bands.
What and How, are criteria for associates which have 3 brackets, Meet, Exceed, Did Not Meet.

Sorry I couldn't upload it.

[June7]

I am baffled by your requirement. You want to show the names but the nature of a pivot query is to aggregate data. To condense 191 rows into the matrix you describe necessarily eliminates the detail of the associate identities. You could use the First function and this will show the associate ID from first record for a grouping.

However, in spite of my lack of understanding, suggest the following. Create columns for the How values with expressions:
SELECT What, IIf([How]="Meet",[Associate],Null) AS Meets, IIf([How]="Did Not Meet",[Associate],Null) AS DoesNotMeet, IIf([How]="Exceed",[Associate],Null) AS Exceeds FROM tablename;

If the AssociateID is not their name, then include in the query a join with whatever table has the name.

Now build a report with Grouping & Sorting with calcs in header/footer. This will allow you to show details as well as aggregate calcs. Open the report with filter criteria for the Band.

[Enemee]

Hi June,
Thank you very much for your help.
But I am a bit confused.
I have made a mock-up of the database in the excel file.
What I need is to enter data into the main database in the first sheet. Then it automatically enters the associate into the correct sheet ( band) and the correct intersection within the sheet.

I didn't know how to automate this using excel so I tried Access but I am still very new to it.

Thank you again for the help so far, any more is much appreciated.

Thanks

[June7]

Think I understand now. You want to concatenate names into the approriate matrix cell. Doing this in Excel would require some Excel functions I am not familiar with or complex VBA.

As stated, crosstab (Transform, Pivot) query manipulates data with aggregate functions. Details are lost.

If you want details, do a SELECT query with the IIf expressions I suggested then build a report.

Examine the attached sample db for possible solutions. The example report organizes records by grouping. Concatenating the names into one textbox is not practical and would require VBA code I've never attempted.

I will remove the file after you respond or in a few days.
EDIT: purpose served, file removed.

[Enemee]

Thanks thats brilliant June.

I have another problem though. I have Access 2010 at home but 2007 at work and I can't open the file at work. Is there a solution to this?

[June7]

As far as I have experienced, files created by Access 2010 cannot be read by earlier versions. Check this article http://office.microsoft.com/en-us/ac...010341553.aspx. This is reason I don't want our office to upgrade.