Breaking up one field into 2 columns and 2 rows

I have a table with 3 columns Totals, SalesOffice and Type. Type contains 4 
different entries Cash Contribution, Cash Distribution, Security 
Contribution and Security Distribution. I want to set the report up like 
this:
SalesOffice                    Contributions     Distributions     Net
                    Cash
                    Securities

I can't figure out how to get it to display correctly. I've tried using 4 
labels with
=IIF([Type]="Cash Contribution","[Totals],"")
=IIF([Type]="Cash Distribution","[Totals],"")

etc but it's just showing #Error when I run the report. Any insight would be 
highly appreciated. I wrote a stored proc on a SQL 2005 server to create the 
output table so I can modify it if needed to make this easier. I'm working 
with Access 2003.

TIA

-Matt 


0
Matt
3/11/2010 9:47:16 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
1668 Views

Similar Articles

[PageSpeed] 53

Your "I want to set the report up like this" only shows what I think are 
labels. Can you share some actual data and do a better job of how these 
should appear on the report?

Both of your IIf() expressions have    "[Totals]    where the quote is 
certainly not needed. Also, IIf() should be written to return a consistent 
data type. I expect [Totals] is numeric while "" is text. IMO, this is 
wrong. I would replace "" with either Null or 0.

-- 
Duane Hookom
MS Access MVP

"Matt Williamson" <iH8Spam@spamsux.org> wrote in message 
news:#3qYsRWwKHA.3536@TK2MSFTNGP06.phx.gbl...
> I have a table with 3 columns Totals, SalesOffice and Type. Type contains 
> 4 different entries Cash Contribution, Cash Distribution, Security 
> Contribution and Security Distribution. I want to set the report up like 
> this:
> SalesOffice                    Contributions     Distributions     Net
>                    Cash
>                    Securities
>
> I can't figure out how to get it to display correctly. I've tried using 4 
> labels with
> =IIF([Type]="Cash Contribution","[Totals],"")
> =IIF([Type]="Cash Distribution","[Totals],"")
>
> etc but it's just showing #Error when I run the report. Any insight would 
> be highly appreciated. I wrote a stored proc on a SQL 2005 server to 
> create the output table so I can modify it if needed to make this easier. 
> I'm working with Access 2003.
>
> TIA
>
> -Matt
> 
0
Duane
3/11/2010 9:56:18 PM
"Duane Hookom" <duanehookom@gmail.com> wrote in message 
news:uGlevWWwKHA.1984@TK2MSFTNGP05.phx.gbl...
> Your "I want to set the report up like this" only shows what I think are 
> labels. Can you share some actual data and do a better job of how these 
> should appear on the report?
>
> Both of your IIf() expressions have    "[Totals]    where the quote is 
> certainly not needed. Also, IIf() should be written to return a consistent 
> data type. I expect [Totals] is numeric while "" is text. IMO, this is 
> wrong. I would replace "" with either Null or 0.
>

Sure. Here is a sample of the data

Totals             salesoffice     type
774001           200             Cash Contribution
1778542.48     200             Cash Distribution
1438952.08     200             Security Contribution
2004847.06     200             Security Distribution
2553564.31     300             Cash Contribution
4134069.93     300             Cash Distribution
2603129.57     300             Security Contribution
3117264.46     300             Security Distribution

Here is how I want that to display on the report

Salesoffice                Contribution    Distribution     Net

200           Cash        $774,001        $1,778,542    ($1,004,541)
                Security    $1,438,952     $2,004,847    (   $565,895)

300           Cash        $2,553,564     $4,134,069    ($1,580,505)
                Security    $2,603,129     $3,117,264    (   $514,135)


____________________________________________________

        Total Cash        $3,327,565    $5,912,611    ($2,585,046)
        Total Security   $4,042,081     $5,122,111    ($1,080,030)


TIA

Matt
 


0
Matt
3/12/2010 2:01:58 PM
The SQL for your query Could look like the following.

SELECT SalesOffice
, SUM(IIF([Type]='Cash Contribution',Totals,Null)) as Contribution
, SUM(IIF([Type]='Security Contribution',Totals,Null)) as Distribution
, SUM(IIF([Type]='Cash Contribution',Totals,Null))-
SUM(IIF([Type]='Security Contribution',Totals,Null))  As [Net Cash]
....
FROM [YOUR TABLE]
GROUP BY SalesOffice

BUT Easier yet would be a crosstab query used as the source of your report. 
You could do the math in the report

TRANSFORM Sum(Totals) as Amount
SELECT SalesOffice
FROM [Your Table]
GROUP BY SalesOffice
PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security 
Distribution','Security Contribution')

That query should return 6 fields.

To build the crosstab query in query design view
== Add your table
== Select the fields
== Select Query: Crosstab from the menu
== Under Totals change Group by to Sum  and Select Value as the crosstab value
== Under Type select Column Heading as the crosstab value
== Under SalesOffice select Row Heading as the crosstab value


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Matt Williamson wrote:
> "Duane Hookom" <duanehookom@gmail.com> wrote in message 
> news:uGlevWWwKHA.1984@TK2MSFTNGP05.phx.gbl...
>> Your "I want to set the report up like this" only shows what I think are 
>> labels. Can you share some actual data and do a better job of how these 
>> should appear on the report?
>>
>> Both of your IIf() expressions have    "[Totals]    where the quote is 
>> certainly not needed. Also, IIf() should be written to return a consistent 
>> data type. I expect [Totals] is numeric while "" is text. IMO, this is 
>> wrong. I would replace "" with either Null or 0.
>>
> 
> Sure. Here is a sample of the data
> 
> Totals             salesoffice     type
> 774001           200             Cash Contribution
> 1778542.48     200             Cash Distribution
> 1438952.08     200             Security Contribution
> 2004847.06     200             Security Distribution
> 2553564.31     300             Cash Contribution
> 4134069.93     300             Cash Distribution
> 2603129.57     300             Security Contribution
> 3117264.46     300             Security Distribution
> 
> Here is how I want that to display on the report
> 
> Salesoffice                Contribution    Distribution     Net
> 
> 200           Cash        $774,001        $1,778,542    ($1,004,541)
>                 Security    $1,438,952     $2,004,847    (   $565,895)
> 
> 300           Cash        $2,553,564     $4,134,069    ($1,580,505)
>                 Security    $2,603,129     $3,117,264    (   $514,135)
> 
> 
> ____________________________________________________
> 
>         Total Cash        $3,327,565    $5,912,611    ($2,585,046)
>         Total Security   $4,042,081     $5,122,111    ($1,080,030)
> 
> 
> TIA
> 
> Matt
>  
> 
> 
0
John
3/12/2010 2:22:03 PM
> BUT Easier yet would be a crosstab query used as the source of your 
> report.
> You could do the math in the report
>
> TRANSFORM Sum(Totals) as Amount
> SELECT SalesOffice
> FROM [Your Table]
> GROUP BY SalesOffice
> PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security 
> Distribution','Security Contribution')
>
> That query should return 6 fields.
>
> To build the crosstab query in query design view
> == Add your table
> == Select the fields
> == Select Query: Crosstab from the menu
> == Under Totals change Group by to Sum  and Select Value as the crosstab 
> value
> == Under Type select Column Heading as the crosstab value
> == Under SalesOffice select Row Heading as the crosstab value

Thanks John the crosstab query worked great. The only issue I'm having now 
is getting a total field in the page footer to work. I've set the running 
sum property in each of the Cash and Security contribution and distribution 
text boxes to Over All and added text boxes with =SUM([Cash Contributions]), 
etc  in the Page footer but it's just giving me #Error when I run the 
report. Any idea what might cause that?

TIA

Matt 


0
Matt
3/12/2010 9:58:43 PM
Aggregate expressions like =Sum() or =Count() don't work in the Page Footer. 
Place them in either a Group or the Report Footer.

-- 
Duane Hookom
MS Access MVP


"Matt Williamson" <iH8Spam@spamsux.org> wrote in message 
news:#33Gx8iwKHA.4492@TK2MSFTNGP05.phx.gbl...
>
>> BUT Easier yet would be a crosstab query used as the source of your 
>> report.
>> You could do the math in the report
>>
>> TRANSFORM Sum(Totals) as Amount
>> SELECT SalesOffice
>> FROM [Your Table]
>> GROUP BY SalesOffice
>> PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security 
>> Distribution','Security Contribution')
>>
>> That query should return 6 fields.
>>
>> To build the crosstab query in query design view
>> == Add your table
>> == Select the fields
>> == Select Query: Crosstab from the menu
>> == Under Totals change Group by to Sum  and Select Value as the crosstab 
>> value
>> == Under Type select Column Heading as the crosstab value
>> == Under SalesOffice select Row Heading as the crosstab value
>
> Thanks John the crosstab query worked great. The only issue I'm having now 
> is getting a total field in the page footer to work. I've set the running 
> sum property in each of the Cash and Security contribution and 
> distribution text boxes to Over All and added text boxes with =SUM([Cash 
> Contributions]), etc  in the Page footer but it's just giving me #Error 
> when I run the report. Any idea what might cause that?
>
> TIA
>
> Matt
> 
0
Duane
3/13/2010 2:23:02 AM
Reply:

Similar Artilces:

loop through pivottables columns and rows
I have a pivot table that have 2 columns that I need to loop through and read both columns and each items of each columns...the pivotitems only allow reading one column...does anyone know how to programmatically read both columns? Thanks You can loop through the column fields and their items: Sub test() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.ColumnFields For Each pi In pf.PivotItems Debug.Print pf.Name & " - " & pi.Name Next pi Next pf End Sub hpham77 wrote: > I have a pivot ta...

Having 2 workbooks open simultaneously
I have a wide screen laptop that can accommodate 2 Excel workbooks side by side. I used that feature for a while until sometime about two weeks ago something happened (I don't know what) and now I a only able to keep one workbook open at a time. When I try to open a second one, the first one minimizes. Can anyone tell me how to get it back so that I can once again view 2 workbooks side by side? Many thanks for your help -- Royal Did yesterday's response work: "How about just window|Arrange|Tiled (or whatever you want)" Royal wrote: > > I have a wide screen l...

Edit or Create a Partylist Lookup field
I have an issue - we have Cytrack running between our AVAYA phones and CRM 4.0 and I have enable the option that when a call comes through it opens a Phone Call activity and it automatically connects to the contact for the person calling in. Great except while we have all cleitns in as contacts - we also have clients either in as a Lead or an Opportunity when we are doign current business and I want to change the phone call activity recipient. I can do this but it comes up a "Look Up Records" screen and you havea drop down list to pick what entity you wish to locate the cl...

Update for Microsoft XML core service 6.service pack 2
Updates are ready. Message bubble shows constantly. Updates won't load millypede wrote: > Updates are ready. Message bubble shows constantly. Updates won't > load Operating System? Service Pack level? Architecture? Anything that you have tried? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html ...

Find a value in list 2 that is not in list 1
I have text values in column A and also in column B, most of which match. I would like to take the values in column B that are not in column A and put those values only in column C. Any help would be greatly appreciated. Maxxwell, Put this formula in column C, and copy down. =IF(ISERROR(MATCH(B2,$A$2:$A$20,0)),B2,"") To get them contiguous, use Autofilter, filter on nonblanks in column C, and copy/paste to somewhere. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "maxxwell2" <maxxwell2@discussions.microsof...

How do I stop 1 day tasks splitting over 2 days?
I am using Project 2003 and it has got an annoying habit of splitting most tasks with a duration of 1 day into 7 hours of work on one day and 1 hour of work the following day when you look at them within the Resource Usage view. This is mucking up my Gantt chart as the tasks effectively look as if they are 2 day tasks (although the duration cell definitely says 1 day). Any idea what is causing the problem and how do I fix it? Things to note: - The project calendar and resource calendars are definitely showing 8 hour working days (8-12 then 1-5) so that isn’t causing the probl...

I can't back space more than one character or repeat a character,
I was prompted while filing out a form to have duplicate key strokes stopped. I made the mistke of accepting and now I can't type any dupe characters without difficulty (must pause momentarilly) and I can't back space without the same pause. I also cannot find how to disable the thing. This happens in all typing situations, including here. You've probably enabled FilterKeys, one of the Accessibility Options. You can turn it off in Windows Control Panel and at the same time disable the shortcut key that you pressed to enable it. -- Suzanne S. Barnhill Microsoft M...

Training registration: how to pick multiple entries for one course
A client is currently using a QBF to select an employee to register for a training class. Now, we want to allow for multiple employees to be selected to register them at the same time for one class. Looking for examples, sample code, or even a basic layout of how to approach this change. Thank you in advance for your help, Dawn -- Dawn Bjork Buzbee On Tue, 29 May 2007 09:05:00 -0700, Dawn Bjork Buzbee <DawnBjorkBuzbee@discussions.microsoft.com> wrote: >A client is currently using a QBF to select an employee to register for a >training class. Now, we want to allow for multip...

Confidentiality Notice #2
How do i configure Exchange 2003 to add a Confidentiality Notice at the bottom of all outgoing emails?? Can this be done without using Visual Basic? Thanks, Greg ...

Pivottable Row SubTotals
I have just started using pivot tables, and have created one with sub totals at the end of each row. Is there a way of getting the totals to be at the begining of each row? Thanks in advance. That isn't an option provided by pivot tables. -- Regards, Tom Ogilvy Matthew <matthew@coxagri.com> wrote in message news:a69014c2.0310091309.59241b0f@posting.google.com... > I have just started using pivot tables, and have created one with sub > totals at the end of each row. > > Is there a way of getting the totals to be at the begining of each > row? > > Thanks in ad...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

Preventing writing in a combo box field
Ok this is my problem: I've a combo box with several employee names. Of course that when the employee is chosing it's name it writes the first letter so that the combo box filters the names to the closest one. Access then higlightes the employee name execpt the letter he wrote. Employee then hits enter and Access displays its full name, has in the combo box options, neverthless I have a hidden form that copies employee name to feed other forms. The problem is that if employee chooses is name has discribed, Access will only select all the other letters of his name execpt the one ...

Adding additional rows for data entry
I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

Supress col/row increment with copy
Question with (hopefully) an easy answer.... When you copy a cell that uses a formula referring to another cell, for simple illustration say "=C4" it will increment based on where you paste it. So, it will increment up to "=C5" if you paste it in the next cell, or "=D4" if you paste below. How can I suppress that, so that when I paste the new cell receives "=C4" as well. (I know I can simply copy the text and paste that in, but I want to copy a whole row of formulas to be the same. I thought Paste>Special>Formulas would work, but it seems to ...

Insert new row as cell contents change
Insert new row as cell contents change. After importing data I have a spread sheet with a column that contains a series of alpha numeric characters. At various random intervals in this column the contents change. EG rows 1 to 4 could contain ABC, then rows 5 to 15 could become 222. I am looking for a method to insert a blank row automatically between the rows were the contents change. Many Thanks Geo George If you are familiar with VBA the code below will do what you want. Preselect the column of data first Sub InsertRowAfterValueChange() Dim myCell As Range Dim sCurrVal As String ...

Help to modify a macro for deleting rows based on two or more colu
Hello I have a macro I have been using to delete duplicate rows which contain the same value in two columns. This works well, as long as I manually sort Column A in ascending order first, before running the macro. The code is as follows: -------------------------- Sub DeleteDuplicates2Columns() Dim LR As Long, i As Long Application.ScreenUpdating = False LR = Range("A" & Rows.Count).End(xlUp).Row For i = LR To 2 Step -1 If Cells(i, "A") = Cells(i - 1, "A") And _ Cells(i, "L") = Cells(i - 1, "L") Then _ Rows(i).Delete...

How I can print full text bigger than column, in repeat column
When I print statements with repeat columns in excell which have text bigger than repeat columns, complete text is printed on all sheets except first sheet. How I can overcome this problem? I want to print complete text in repeat columns, which is expanded to other columns which are blank. ...

selecting rows of data
I have an excel worksheet with 4 columbs. I want to automatically select multiple rows of data according to search which will partly match one cell in the row and put the full ro as results in a new worksheet. for example 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete 22/03/03,Texas,90.78,incomplete 23/04/03,Oregon,467.99,complete If the search string is "New" how can I produce a new worksheet whic will contain 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete Any suggestions would be appriciated -- Message posted from http://w...

Moving rows in Macro/If statements
Trying to figure out the best way to use an "IF" statement in a macro that will move data down to the next row. Have a spreadsheet that has the following columns: Name-Address-City-State-Child1name-Child1DOB-Child1age-Child1sex-Child2name-Child2DOB-Child2age-Child2sex-Child3name-Child3DOB-Child3age-Child3sex. The first 4 columns will remain the same. However, if the parent has several children, the formula/macro can identify the columns and insert them below, matching the columns above. So now the data should show: Name-Address-City-State-Child1name-Child1DOB-Child1age-Chi...

Outlook Express #2
I have Outlook Express installed on two computers at two separate residences. Both are set to check the same comcast email address. One works fine but the other I keep getting errors when I try to send out emails etc. Should I be able to use Outlook express at two separate locations to check the same email at different times without a problem. Sounds a bit like the problem I am having (see my post from earlier today). Both my PCs are configured the same: one keeps prompting me for the POP3 password for each e-mail account, the other I have been able to successfully save the pa...

2 Axes, same scale wanted
Hi Is it possible to set my second y axis so that the scale is identical to the first y axis. (My chart needs to have 2 axes as one plots daily values and one plots annual) but I find that excel automatically assigns diferent scales to each? This same reference should apply to dual axis charts. You can apply scales to both primary and secondary axes using worksheet cells via Jon's explanation below. http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html -- John Mansfield http://cellmatrix.net "jonny" wrote: > Hi > > Is it possible to set my sec...

Data validation for 2 valid values
I want a cell to allow only 2 valid values but not have a drop-down list appear. How do I do that with Data->Validation? Data>validation>allow>list, put the 2 values separated by a comma in the source box, uncheck in cell dropdown -- Regards, Peo Sjoblom "Hall" <hall@garp.org> wrote in message news:ujuB%23w52DHA.2000@TK2MSFTNGP11.phx.gbl... > I want a cell to allow only 2 valid values but not have a drop-down list > appear. > > How do I do that with Data->Validation? > > Using a list, as Peo suggested, will force users to match the ...

CRM 3.0 Installation Error #2
Receiving the following error during "System Requirements" step of the server installation: "One or more Microsoft CRM groups are not valid. The group scope must be Domain Local or Global. The group type must be Security." Environment: SQL Server 2000 CRM Server: Server 2003 SP2 Details: I am trying to reinstall CRM on one of our redundant app servers. This server was already running CRM but was uninstalled in attempt to fix an issue. CRM is still running on the primary app server. I am therefore connecting to an existing DB. The AD groups exist, have the correct ...

NDR in 2 Exchange Servers 2003 on 2 domains
Hi, 2 Exchange servers 2003. 2 domains. a.fr and b.fr. The Active Directory domain is xxx.local. Each domain is hosted on a distinct server, on the same Exchange organization. When an email arrives from outside the non delivery receipt is sent from postmaster@xxx.local. As almost every MTA now is checking if the 'from' belongs to a valid domain, this does not work. It is possible to change the NDR originator to postmaster@a.fr or postmaster@b.fr. But this is valid for the whole organisation. And I need the a.fr server to answer with postmaster@a.fr and the b.fr server answer from po...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...