Pivot Table Report: Getting average of difference of two date columns
I have data in excel sheet as:
WORK_ID PROJECT START_DT END_DT
1001 P1 1/1/2006 1/10/2006
1002 P1 1/25/2006 2/20/2006
1003 P1 3/15/2006 3/20/2006
I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...How to lock a column
I want to lock column B in my worksheet (meaning I want it hidden and no one
should be able to unhide it). And also, I have two tabs in my worksheet that
I don't want anyone to see but they need to be in that file.
Message posted via http://www.officekb.com
These would depend on the security of a protected worksheet/workbook.
The bad news is that worksheet/workbook protection (under tools|protection) in
excel is not made for this kind of thing. It can be broken in a matter of
If you really have secure data that other shouldn't see, don't put it in
excel...How to get Table Description Field Text to show on Forms? Hovering? Right-Click?
Can I get the fioeld description I enter in the Table Design View to
show on Forms or Queries?
When the mouse hovers over the field or field name??
When the user right clicks on the field or field name??
At the bottom of the screen when the mouse hovers?
Thanks for any help.
I've done some programming but not much in Access.
How do I (where do I click) to add a function to the "standard
And, am I correct in understanding that the first function would be
called each time the db opened?
And the 2nd function would be called each time the form op...Products/Services Table
I am using Access 2007. I have a database for my Products/Services industry.
I have a table containing my products/services, as well as a Work Order Form
that is linked to the Products/services table from which I can choose via
drop down the products that I am selling to that customer. The
Product/services table contains 3 fields; Product, Cost & Price. The Work
Order Form also contains those three fields, and when I choose a product from
the drop down, the corresponding Price field fills in as well. Now, sometimes
(after bargaining of course!) I want to change the price for ...Making a list of all persons in a table in a corresponding form.
In a database of mine I have a table containing persons' name, address and
so on. In the corresponding form I have of course all the fields where the
users put in the data. In this form I want to have a list showing all the
persons in the table as well, so that the users can click the right person in
the list and have his or her data filled into the fields. I have tried to
make a table form from the same table and include it as a subform. This,
however, was not successful because the subform only showed the person in
focus in the form, not all the persons in the table. Also...Record Locking 03-17-10
For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a peer-2-peer
I read that it can be beneficial to remove record locking on the forms and
I thought edited record was what should be setup. Why not? If you remove
it, then how are updates managed in the rare even 2 user work on the same
Thank you for the clarifications.
"QB" <QB@discussions.microsoft.com> wrote in message
> For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a
&g...pivot table multiple consolidation ranges
When I create a pivot table based on data from one excel worksheet, the pivot
table automatically creates one layout field for every column header on the
worksheet; but when I choose the multiple consolidation option and add two
consolidation ranges to my pivot table data, the layout shows only a field
named "Rows" and one named "Columns" - in other words, it doesn't create
fields named after the column headers that I can drag into the respective
pivot layout areas (page, column, row, data).
The column headers in both of the worksheets (consolidation ranges) are...Item.content field in RMS Items table
I was taking a look at the structure of the item table in RMS 1.3,
At the end of the record is a field called "Content". It is an ntext field.
I don't see anyplace on the Item Properties form where this field is
Is this an obsolete field, and if so, would it be safe for me to utilize
this field for some of my custom information?
The Worth Collection
There are a few fields in the Item table that either have never been
implemented or are no longer in use. It should be safe to use contents. We
have used th...Formatting data table
I have a data table shown under a diagram. However it won't display all the
decimals. I.e. in the input to the diagram for january 2006 is 1.123, but
only 1.1 is shown in the data table. The only solution seems to be to enlarge
the diagram but this is not an option in this particular case!
As a last resort I could embed a table under a traditional diagram without
the data table, but that is second best.
Looking forward to any help
If you right click on the data table you can change the font size by clicking
on 'format data table' and the font tab. You can type in the size ...Synchronizing Custom Tables
1) If I created a new customer profile in Store A, I want it to be available
in Store B and other stores. Is this possible? How?
2) I want to create a new table to be linked to Customer table. How can I
syncronize it to the HQ and other Stores?
...Tables in Publisher
I'd like to set up a table in Publisher with lines on the bottom of every
cell - like a list. When I select the table and ask for a line on the bottom,
it only gives me a line on the bottom border of the whole table. Is there a
quick way to get a line on the bottom of every cell without having to select
every single cell and add the line? Thanks.
> I'd like to set up a table in Publisher with lines on the bottom of every
> cell - like a list. When I select the table and ask for a line on the bottom,
> it only gives me a line on the bottom border of the who...Table window Size
Any way to make Access 2003 remember the size and position of a table
window? As it does with queries.
Kungen <email@example.com> wrote in news:1190993934.749281.64590
> Thanks for trying to help. I'm talking about the windows for the
> actual tables when working with them in access. Everytime I reopen a
> table it covers the entire workspace and I have to adjust the size,
> which is time consuming and annoying. Saving while the window is open
> doesn't help. But I guess there is no way to work around this minor
>...Processing Functions in a Table
I'm running Access 2003. I have a table that contains the following
Floating Copies =DCount("*","tblFloatingCopy","[Approved] = False")
Releases =DCount("*","tblReleaseReq","[Processed] =
Is there a way when I can get the functions to process, rather than
showing the code, when viewing the table through a form or simply
viewing the table? i.e:
Po...File Locked Error
I'm still trying to talk our IS group into rebooting the server...
I'm a little curious how the lock actually follows the file. Even i
we copy it to another computer, the XL file still complains the file i
locked. Is there a lock flag that is embedded in the file properties?
Lady Layla Wrote:
> If the file is stored on the server and the file is saying it is locke
> by a
> user -- if the user's system has been rebooted, most likely the onl
> thing that
> will release the lock is rebooting the server. It is unfortunate bu
> it does
> have to be do...Indexes in tables.
Can I add index for tables in SQL Manager. For example for SOP30200 index
for any field. Will it be a problem with program or not?
Any other ways?
I believe any future upgrade will blow away your index.
Charles Allen, MVP
"Vitali V" wrote:
> Can I add index for tables in SQL Manager. For example for SOP30200 index
> for any field. Will it be a problem with program or not?
> Any other ways?
Suggest you leave the tables alone. You'll just get into a pickle..
"Char...Help Locking Worksheet #2
Not working. I highlight the data area then go to tools/protec
worksheet. After I protect the worksheet the cell focus stil
continues on the same row instead of going to the next row. I can'
lock the entire worksheet
jlabsher's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1556
View this thread: http://www.excelforum.com/showthread.php?threadid=27359
Did you uncheck locked under format>cells>protection
for the highlighted data beforte you protected the sheet?
Rega...reportoutput to make table
Is there any way to take a report and make the results into a table? Sorry
if this seems a stupid question, but I'm a novice in over his head on a
project I volunteered for.
A report displays data that's in a table. Why would you need to create the
report as an intermediary step? Why not just create the table from the
original data or (better yet) create a query of the original data that
displays the desired data (so that you don't have to store it redundantly)?
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Dale" <...Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table.
Excel (07) is not recognizing the field as a date. This is a field I
would like to group by in the pivottable. I am aware of all the
techniques to convert this to a date field, but I am searching for an
answer as to why XL pivot tables cannot consume the dates directly
from a sql query.
I have played with bringing the dates back in a number of differenent
formats with no success.
Any insights into this would be appreciated.
Probably coming in as text and yuo may only need to copy an unused cell and
paste sp...Check Table, if found, fill Fields
I did a mess of a database, and I'm trying to clean it up. My original
database/Table has Date Received, Last Name, First Name, SSN, FormFrom,
FormType (pulldown) and several other fields, including Date Completed, and a
Record field that's an Autonumber.
Every time someone requests a letter/form etc from us they get added in as a
new Record. So one person may have 5 different things etc., and that 1 person
is listed 5 different times. It should really be 1 person with 5 different
transactions. I am trying to Normalize.
I want to break off the Last Name & First Name F...display / print 0's in pivot table cells
have a pivot table with blank cells. people would like to have 0's in the
Select a cell in the pivot table
On the PivotTable toolbar, choose PivotTable>Table Options
Check the box 'For empty cells, show'
In the text box, type a zero
> have a pivot table with blank cells. people would like to have 0's in the
Excel FAQ, Tips & Book List
...Large Pivot Tables
I have a data source with 165000 rows that I would like to make into a Pivot
Table, but it exceeds Excels 65K row limit. Any suggestions would be
If you have Access then throw it in there and query it from Excel using Data
/ Get External data
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------...Query Using Variable Table Sources
I've got a reasonably simple application that I want to dumb down further for
a novice user. We recieve quarterly data in three Excel files of which we
combine into a single tab and inport and keep as a separate table, "just in
case" we want to look at history.
Currently we name the tables "FYnnQn" and then the former user created
separate reports from each quarterly table.
I'd like to have a query ask for user input of "FYnnQn" and then report off
of that, instead of growing the number of reports and forcing the new user to
copy and edit reports.
Ea...Need to normalize some tables after database has been split
I realize that I have to normalize some of my tables in my database but I
have split the database.
My question is, what is the best way to do this?
All these tables have been referenced in the forms in the database.
How do I change the forms without recreating them all over again?
Thank you for any help in this matter.
On Wed, 19 May 2010 22:24:01 -0700, forest8
>I realize that I have to normalize some of my tables in my database but I
>have split the database.
>My questio...Frequent Lock Ups with Access 2007
We have a networked database, split into front and back ends. It is used
throughout the day usually by one or two users, but another occasional user
will join in. This application was used fine in the current setup under
Access 2002/2003 and brought over as-is to Access 2007. No areas of
functionality are suffering and the use of the application has not changed
at all except that it will slow down at times and lock up several times per
day. Waiting ten minutes has brought no resolution, and the app responds
snappily when it is not locking up. This database has older-style security
on i...Table Borders
I am new to Publisher and I need to create a table with double line borders.
I can't seem to figure out how to do this. When I go to line style the double
line is greyed out and if I go into format table the option to have a double
in is not there.
Is it possible to have a table with double line borders?
Thanks in advance for your help.
If you simply want a double border around the table and not the cells, draw a
box around the table.
Mary Sauer MSFT MVP