Summary Table's in Reports

Hi Guys,

Fingers crossed you can help me out with this...

I have a table in a database (tbl_events), which includes thre
important fields;

EventType (Text - Lookup)
EventDate (ShortDate)
EcentLocation (Text - Lookup)

Now, i need the contents of this table summarised, so, for example..
lets say the table contains the following data..


Code
-------------------
    
  Type     Date           Location
  A        01/01/06       Office1
  B        01/02/06       Office1
  A        01/02/06       Office1
  A        01/02/06       Office1
  B        01/01/06       Office2
  B        01/02/06       Office2
  A        01/02/06       Office2
  A        01/02/06       Office2
  A        01/02/06       Office
-------------------



Now, what I need is a report that shows a result similar to th
following...


Code
-------------------
    
  Location = Office 1
  
  January        February
  A             1                2
  B             0                1
  
  Location = Office 2
  
  January        February
  A             0                3
  B             1                1
 
-------------------


Is this at all possable?

Any help appreciated

--
Knape9
-----------------------------------------------------------------------
Knape97's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2162
View this thread: http://www.excelforum.com/showthread.php?threadid=51181

0
2/13/2006 4:32:48 PM
excel 39879 articles. 2 followers. Follow

2 Replies
397 Views

Similar Articles

[PageSpeed] 21

Assume your table is on Sheet1 occupying cells A1 to C10, i.e. you have
headers in row 1 - highlight A1 to C10 then Insert | Name | Create and
untick the "Left Column" box and OK. This creates the named ranges
Type, Date and Location.

Insert a new sheet and enter the following:

A2:    Location:
B2:    Office1
A3:    Month:
B3:    January
C3:    February
D3:    March           and so on up to
M3:    December
A4:     A
A5:     B
Highlight A2:M5 and copy to A7. Enter:
B7:     Office2

In B4 enter this formula:

=SUM(IF((Type=$A4)*(Location=$B$2)*(MONTH(Date)=COLUMN()-1),1,0))

Do not press <enter> when you have typed it in, do <CTRL><SHIFT><enter>
at the same time, as this is an array formula. If you do it correctly
then Excel will wrap curly braces { } around the formula - do not type
these yourself.

Select B4 and <copy> then highlight C4 to M4 and press <enter>. Then
highlight B4:M4 and copy these cells to B5, B9 and B10. This should
give you what you want.

Of course, there are only 9 lines of data in your table, and in reality
there will be far more - ensure that the named ranges are adjusted to
cover the whole of your data.

Hope this helps.

Pete

0
pashurst (2576)
2/13/2006 8:55:34 PM
Slight amendment.

When you copy the formula to B9 you will have to edit it to point to
B7, i.e. change the $B$2 in the middle to $B$7 and do
<CTRL><SHIFT><enter> again. Then copy across and down as before.

Pete

0
pashurst (2576)
2/13/2006 9:00:32 PM
Reply:

Similar Artilces:

Query
I have a transaction # for each record in my main form with a subform "Approvals" and "Checkouts". I created a query to show the sum of all Approvals and all Checkouts by Transaction # for each record. (one for Accruals and one for Checkouts). All approvals and checkouts come up in these queries. I want to create a report showing, per Cost Center, the $ amt of Approvals, the $ amt of Checkouts, and a calculated field to show the remaining value. The report comes up with all approvals and checkouts per cost center, however, if there is an approval that does not hav...

Converting Crystal report from MSCRM 1.2
I have a client who has upgraded to V3.0 for CRM. They want to continue using Crystal for their reporting engine because they have 40 custom reports. Does anyone know how to easily accomplish this? I have created a new connection to the new CRM Views in SQL, but I can't seem to figure out how to easily replace all the fields that are currently in the report using the SSO connection to my new connection. Any help would be greatly appreciated. Thanks, Amy many of the services such as rpttosql.com say they will only convert so far. some even say they will only get the general lay...

Printing a Year-End Report Again #2
I was closing the Receivables Management for the year and I accidentally closed the Receivables Year-End report without printing it. How could I open this report again? I would like to print and save this report but I am already done closing the year. I am using Dynamics version 7.0. ...

Pivot Table #5
I create a pivot table of growing data each month. Now when I create the pivot table after I set up the layout. I see the message "calculating Pivot Table" but nothing happens. Is there some setting I need to change on my computer. All I get now is a empty new worksheet. ...

Reporting Services Error 04-27-07
Last night I moved the CRM databases from our CRM Web Server to another SQL server in the same domain in hopes to increase performance. I followed the docs from Article 917948. I want the reporting services to remain on the CRM web server so I also followed the document on "Additional setup tasks when MS Reporting Services is installed on a seperate server from MS CRM or MS SQL Server". I setup the delagations and checked the SPNs. Our current setup is this: server1 CRM app Reporting services Win 2000 server, SQL 2000 Server 2 CRM databases Win 2003 server x64, SQL 2005 x...

Table of Contents font ?
How do I change the font of the table of contents? Thanks, -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". Modify the styles named TOC 1, TOC 2, ... as described in http://www.shaunakelly.com/word/styles/ModifyAStyle.html. (If you have Word 2007, press Ctrl+Alt+Shift+S to open the Styles pane, right-click the one of the style names, and choose Modify to get to the necessary dialog.) -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all f...

business portal tables
Howdy there kind folks. I need to track down the tables in the Dynamics database that hold all the purchase order information. Particularly the bits that deal with transferring a purchase request to a purchase order. i started looking but theres just too many. I know all the requisition ones start with reqmgmt. Sadly the purchase order ones dont seem to follow this idea. many thanks. they are not in the Dynamics database, the purchase order tables are contained in the company database (like TWO) and they begin with POP the POP10xxx series are the work tables you should be looking at. ...

how to use temporary tables in dex
Hi Somebody has one example of how to use temporary tables in dex Cesar Hi Cesar. Define temp tables just as you would define a SQL table in Dex - but with physical name as "temp" without the quotation. I recommend using database type ctree for performance as it would just create a temp file in your directory. From there on, just use the temp table as you would with normal dex table but remember that once you are done with your routine, your temp table will be deleted automatically. Use temp table as a temporary repository for your calculations, reports, etc. ---Darryl Baj...

Select null values in Crystal Report
I want to select those records in a Crystal Report where the country is not filled. I can select those where it is filled, however I get zero record back when I want to select where it is not filled. I use the following selection formula in the record selection part: Length ({account.address1_country}) < 1 Any idea is appreciated. Thanks, Miklos ...

Money 2006 Deluxe Bills Summary Sorting
When in the Bills Summary view and I sort on Account, not all of the line items with the same account are grouped. Upon further review, it appears that duplicate line items with the same Payee, Next Due, Amount, etc are being created and not properly sorted by Account. When I resort based upon Next Due, these duplicates go away and the line items are properly sorted by Next Due (ascending or descending). When i've seen this before, it can mean corruption in the file. Because you can see those additional bills, you have the opportunity to delete these duplicates if you are seeing ...

Hourly Report
Does anyone know how to set up the following report: Hourly sales, by day of week over a period of time. For example, I'd like to know what our sales are by hour on Saturdays from Jan-Mar (i.e. total sales on Saturdays from 10-11AM for the three month period). Hoping this will give us a better idea of whether or not our store hours should be adjusted. It's hard to look at them one day at a time on the Z reports. It would be much easier to analyze over a period of time. -- Kris Hi Kris, I am not sure which Kris you are, but this is Akber Alwani Yes I have done this report ...

pivot tables #9
i posted this on the general forum but i thought maybe someone here can help me. I have a table that has four regions and total sales amount for each reason by week. my pivot table lists all those and totals it per region per month. i want to add in the pivot table a percentage of the regions sales for that one week over the total sales for all four regions. is there a way i can add that to the table? i.e. the table is currently like this: Week 1 East 500 Midwest 487 South 529 West 492 Total 2008 i want to add the percentages like this: Week 1 East ...

Difference between XML templates and Active Reports
Why does RMS use XML templates (for receipts and P.O.s) and for most other reports Active Reports? I realize that the XML receipts can not be changed or resorted on the preview screen, but why use not all active reports? Active reports are based on formating the results of a SQL Query - if you can work out the SQL to get the result set you want, you can create almost any report you want. The XML Templates are based on objects internal to RMS, like the current transaction for reciept printing, the active PO or Transfer request for PO prining, or a given customer for Statements. Y...

FRx
We have an FRx report containing 12 columns - one for each month. We are using the P<=B Print Control in the column layout to only print those months that are less than or equal to the Base Period. When we generate a report that contains six months or more and export it to an .XLS spreadhsheet it looks fine. However, if the report contains less than six months (fewer columns), we don't get our report heading. Is this due to a setup option somewhere in the catalog id? Have you checked the "Header Options" under your column layout? Look at the "spread from and ...

Register report by catagry
Is there a way to print a report similar to quickens register report, with the catagories sorted? Have you looked at Transactions by Category??? <anonymous@discussions.microsoft.com> wrote in message news:719a01c40250$7b085780$a301280a@phx.gbl... > Is there a way to print a report similar to quickens > register report, with the catagories sorted? ...

Pivot table returns `
I have data from an SQL WBC-A. When I pivot this data it returns ` (The character below the tilde) Any other data returns the correct pivot. E.g. WBC-X returns WBC-X ...

Table requires Custom Linking to Excel
I have a table in Word 2000 with various columns. Each row represents one order for a meal package. One of the columns indicates one of three baked pies to order (apple, cherry or pumpkin). Another column indicates whether or not the order has been paid or unpaid. Is there a way to put a total number for each of the pie types ordered into an Excel worksheet? Similarly, would there be a way to analyze all of the rows, and for all rows with an unpaid status, multiply this number by the cost for each meal package, and display this result in an Excel worksheet as money due? Thanks...

How do I add a secondary axis to a pivot table chart?
Using XL2007 - the secondary axis radio buttons are greyed out. Is there a way to add the 2nd axis? Hi, This is chart type dependent. Suppose you have a line chart, right click the series and choose Format Series, the Axis radio button should be available. What type of chart are you using? Cheers, Shane Devenshire "dangelor" <dangelor@discussions.microsoft.com> wrote in message news:2610028F-5FAF-4400-A3E3-B7D0CCEFB443@microsoft.com... > Using XL2007 - the secondary axis radio buttons are greyed out. Is there a > way to add the 2nd axis? ...

create a report using vba to select the names for the report
Hi, I need to create a report of selected Customers, I have gone as far as creating a string with customer names in it. Whats next? sample = Selected_Customer_Names= [ABC], [test], [New], [A New One] ...... and so on. Is this correct for the report and/or how do i open the report with the above only listed? -- Message posted via http://www.accessmonster.com On Wed, 28 Apr 2010 03:47:34 GMT, "trevorC via AccessMonster.com" <u44860@uwe> wrote: >Hi, >I need to create a report of selected Customers, I have gone as far as >creating a string with ...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...

Quarter-End Payroll Reports #2
When trying to process quarter end payroll reports I am receiving and unhandle script exception: [Microsoft][ODBC SQL Server Driver][SQL Server] Could not find stored procedure 'SCSB.dbo.uprGetPension'. ...

DailySales Table
Hi There, Does anyone know what Type & TypeID columns referring to in DailySales table in HQ ? I have customization guide but there is no details about that columns. Thanks, Arthur Hi Arthur - my understanding of the Daily Sales table is that it gets populated by some internal function that groups based on supplier, cashier, etc. Here are some old notes I found on what the Daily Sales Type refer to: 1: Supplier 2: Category 3: Department 4: Register 5: Cashier 6: Sales Rep -- the TypeID may refer to the batch that's feeding the info (ie batch.batchnumber ...

Tables and banding color in background
I am creating a template where many tables will need to be used. Our standard is to have banding of rows in the table, and when users want to expand the table, I would like them to be able to have the banding automatically occur. This option appears in Word 2007, but does not function well. Has anyone had success with this functionality? Thanks in advance for your help. Color banding can be applied as part of a table style. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "sharon27lily" <sharon27lily@di...

disabling data updates within excel pivot table
When saving a Office Web Component Excel Pivot Table, as displayed from with a Web Browser, using the excel export function, I want to be able to freeze the data displayed, so that when ever I reopen the saved excel file the data content does not change to reflect any subsequent changes in the under lying OLAP cube ...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...