Urgent Request: Pivot Table Help
I'm building a Pivot Table and for the life of me can't figure out how to
put items in the Data Area in a columnar fashion rather than stacked rows.
I've done it before but can not recreate it. In fact, I have an older Pivot
Table setup in this fashion but can duplicate how I did it.
Want CUSTNO as the ROW AREA, then another 6 or so fields in the DATA AREA
but want everything on one row.
Your assistance would be greatly appreciated!!
Take a look at Debra Dalgleish's site:
She's got nice pictures of how to drag the ...Likert scale calculation
I have used the following table in my survey:
Frequency of use - check which on is applicable:
every few hours every few
mins every few secs
so the sample data looks like this:
id item1 item 2 ............. item10
1 hours hrs ................. mins
2 secs hrs ................. mins
3 mins hrs ................. mins
I want to add up the hours secs mins for each item..for example (from
the above sample):
ev...How to write back a SQL Server table from Excel
I want to read a SQL Server table into Excel and then make changes to
it and then update the Excel range back to SQL Server. Also, I want to
be able to add new rows or delete rows in a range and reflect back the
same into SQL Server. In simple words I want to do the same thing what
Access Link table does from SQL Server. One of the main problems I
face is how to identify the range the query populated in Excel ?
Then how to track what rows were deleted and what were inserted newly
and what was updated.
If any of you have a VBA / t-SQL code for this it is highly
B...create link table at run time
I would like to know is it possible to create link table at run time?
If yes, any information is great appreciated,
>I would like to know is it possible to create link table at run time?
>If yes, any information is great appreciated,
Certainly, it is possible. All you have to do is create the
TableDef object (if it doesn;t already exist) and set its
Depending on what you are linking to, the linking
information that's needed in the Connect property can get
kind of complicated. To see what you need, first connect
manually as if you d...Reading a hyperlink from a table and inserting it into the hyperlink property...
I would like to know how to read a hyperlink from a table and then in turn
insert that hyperlink into the hyperlink property of a command button to
My database has serveral hyperlinks stored in the fields of a table.
Thank you in advance
strHyperlink = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")),
strLinkName = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")),
int_PPT_Pg_Nbr = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")),
str_ToolTip_PPT = HyperlinkPart(Trim(Nz(Me!l...Add more cols to a pivot table
I want to make some calculations using the information showed by a pivot
table. The problem is that the pivot table's size varies, so I would like to
add more columns to the pivot table, so I will have no problem if the pivot
becomes bigger (because there are more cols or rows) or smaller.
I have tried to add a new column to the pivot table make some calculations
based on the information the pivot table shows, but I can't and I'll explain
I have configured the pivot table so that the data shown is an average (not
a sum). I want the calculated value to use that averag...Status Bar #2
I am using VC++ 6. I am using RichEdit. I am trying to write the current
line and column of the cursor in the Status Bar.
In MainFrm.cpp I use:
And in my void CMainFrame::OnUpdatePage(CCmdUI *pCmdUI) I use:
m_nCol = pView->GetRichEditCtrl()->LineLength(-1);
m_nRow = pView->GetRichEditCtrl()->LineIndex(-1);
I know the LineLength gives total characters in the row you are on and
LineIndex gives you the numbers of characters in the whole file. What ca...Non-verbose TABLE syntax
I just stumbled upon this (should appeal to the SELECT * fanatics<g>):TABLE Table1, Table2;Apparently valid Jet 4.0 SQL syntax that returns all columns for thecross join of the tables. What do you reckon: OK for production code<g>?Jamie.--...Pivot table drops columns on refresh
I have a spreadsheet with appx 100 columns and 500 rows,
that we use as a source for 15 odd pivot tables.
When we refresh, about 30-40% of the time, it drops
several columns. 20 of our columns are dates for the
next 20 weeks of the year. When it drops them from the
table, it also drops them from any subsequent calculated
fields, forcing me to recreate the sheet multiple times.
Strangely, it drops the first and last weeks of the
months, but not the middle ones.
Does anyone know of a way to get Excel to keep the tables
from dropping fields?
This is a known pr...Tabling
I'm doing some coursework using Microsoft excel 97. one of the tasks
have to do is use a table. The user enter, a number, and the length o
the table will increase. (columns), according to that number
The width stays the same. The table has a default length of thre
columns. I've been given this notation:
= cell above + 1
(a2) = b1 +1
copy formula down
Specify row number
Choose another cell
(b2) stop at 7
b2 =( if cell above = 7 then don't put anything, otherwise add 1 t
I don't know if that will help, it didn't do much for me. Thanks ...How do I search an entire table for a given values?
I have around 13 columns in a table and building a front end where
user enters a value in a text box and hit submit and the SQL query
should return all the rows that matched columns with the value entered
by the user.
Can some one please help me with how to come up with a sql please.
Actullay I need to write this in Oracle 10g but I am trying it to work
in SQL server and hopefully it works the same in Oracle too. I do not
have the much data in Oracle on the test box so trying to get it
working in on my sql box. The below simple sql seems working OK but
want to know if it ...All Tables as Queries?
Should all of my tables (except for Lookup Tables) be stored as queries?
Definitively no, since a query does NOT store anything.
You can imagine queries as if they were tables, or part of tables, but they
are not real storage.
Vanderghast, Access MVP
"lettyg" <email@example.com> wrote in message
> Should all of my tables (except for Lookup Tables) be stored as queries?
Great Thank you very much!
> Definitively no, since a query does N...pivot tables
i have a range of dates in one column and a number of data points in three
other columns. i want a pivot table that will show a line graph of the data
points (y axis) over time (the x axis). please help :)
...Table wkPostingValidationState Usage / Truncation
I am having some inventory posting speed issues. I have tracked this down to
some bad code from a third party product, and I have to put a patch on to
When I was looking for a solution I found a couple of Tecknowledge articles
(search the database with the table name) that mentioned flushing the
wkPostingValidationState table. This is the first time I have read anything
about this table. I am trying to determine if it is truly ok to flush this
table and to determine what this table is used for. This table appears to
have a bunch of old crap in it and that I should flush it. ...How to eliminate % totals in a Pivot Table
In my Pivot Table, I have a field in the Column part which contains
different items (from a DB).
I try to simplify with an example: 3 months containing volume data measured
in Tons (3 columns), then different additional columns measuring the Actual
Vs budget in %
All the % are calculated automatically into the source DB, so no calculation
is made in the pivot.
The issue is that in the Subtotals and Totals I get the sum for the volumes
of every month, which is fine, but I also get the sum of the % Vs budget,
which of course is not a good thing. So, I end up to have:
Jan Feb M...how can I stop sub total rows appearing in pivot tables in excel
I am working in excel and I need to prevent sub total rows appearing in my
pivot tables. Does any know if this is possible and if so how?
Also, I am working in Access and I need to be able to have multiple columns
headers (grouped) similar to what you can do in Excel, any ideas?
Thanks for your help.
If you rightclick on the button like header in your pivottable, you can select
there's a "none" option for subtotals.
If you don't get an answer for your access question, you may want to try an
> I am wor...Expand pivot table
I've added data to my pivot table, and want to change the data range. How do
I do this in Excel 2007?
Show the pivottable wizard for that pivottable.
Hit the back button as many times as you need to get to the step that asks you
for the range.
If the range grows and contracts rows, you may want to create a dynamic range
name so all you have to do is refresh the pivottable.
Debra Dalgleish shares instructions for that kind of dynamic name:
Fred Smith wrote:
> I've added data to my pivot table...Excel 2000 Pivot Table
when I create a pivot table or go to update an existing one, I can
select or de-select an item from the Feild Drop Down Arrow. Is this a
beccong's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28549
View this thread: http://www.excelforum.com/showthread.php?threadid=482088
Is the workbook shared? If so, you won't be able to change the pivot table.
> when I create a pivot table or go to update an existing one, I can
> select or de-s...Default Weight in Pivot Table Charts keeps getting reset
I have created a Pivot Table chart and I am using basic line chart to
display the data...I would like to make the line weight 2 points heavier than
the standard but each time I select new values from the Pivot Table chart it
resets to the default....where do you set this to remain at the values
Any help appreciated....
...Tricky Array Tables
I have tricky array tables problem, which you can find in this file:
Case is to get matching data from sheet2 to sheet1 (into green cells)
without using a Macro.
I'm trying to use array table to get data from other table. It's seems
to not very complicated, but it's tricky. Does anyone has an idea how
to solve this?
I looked at your file and you do not fully explain what you want but you
should be able to do easily with vlookup or match/index formulas.
Don Guillet...Validate control before status is updated
I need help tweaking setup for a CancelEvent validation and message
box. I'm working on a legacy Access 2000 database.
On the Job Entry form, there is a control [CompressorType] that
indicates whether equipment is a rental (1), from the maintenance co.
(2) or the service company (3). In order to prevent leaving rental
equipment on site, I need to add a validation that checks first to see
if a rental compressor was used. If it was ([CompressorType]=1), then
it needs to validate that something (no way to validate specific
entries) was entered in the [CompRetConf] field (which stores the
Re...Linking Combo Box Values to Access Table
I am trying to create a combo box and link it to values in an access table.
The access table has a list of well names, dates, rates and hours and several
other columns that I am not interested in. I need the combo box to be
populated with the well names, and when I select one I would like it to
return 3 columns with the Date, Rate and Hours.
I have already tried Data>External Data ... but the problem is that there
are too many columns for excel and therefore I am missing a lot of my data,
so I need it to pull directly from Access.
1) Create combo...Server status Unreachable
I got a problem in my Exchange2003 lab: all servers show as unreachable in the ESM\Tools\Monitoring and Status\Status.
The ESM is only installed on a separate server and NOT on the Exchange server themselves.
I have the same problem on all my servers (inside three RGs).
When I right click on one of those servers and select properties I got the following error:
Class not registered
ID no: 80040154
Exchange System Manager
I've made some lookups in the KB and found articles about services or components not being correctlt registered but nothing really about Exchange 2003.
All por...How do I get a web query to auto-refresh before a pivot table aut.
I have html data stored on an intranet site that I want to auto-refresh when
the user opens the Excel file. I then want a pivot table in the file to also
auto-refresh. Excel is updating the pivot table before it's refreshing the
data which is the reverse order of what I need. I can't figure out how I
change the order of the auto tasks.
...add additional column in pivot table
I have create a pivot table and used grouping function to grouping data into
two months, one column is June and the other is July, how can I able to have
one more column to calculate the two month difference in % ??