My data in table are very simply organised as below:
Res.no City Code Var1 Var2 Var3
1 City1 CD0001 num num num
2 City1 CD0002 num num num
3 City1 CD0003 num num num
4 City1 CD0004 num num num
5 City1 CD0005 num num num
6 City2 CD0006 num num num
7 City2 CD0007 num num num
8 City2 CD0008 num num num
9 City2 CD0009 num num num
10 City3 CD0010 num num num
11 City4 CD0011 num num num
12 City3 CD0012 num num num
13 City3 CD0013 num num num
14 City3 CD0014 num num num
15 City1 CD0015 num nu...Adding to an imported table
I am a beginning Access user. When I order items, I am able to import
the order information (info about each specific ordered item) into an
Access database. I have figured out that I can add information from
subsequent orders to the same big table (and could use a query to ID
stuff from specific orders if necessary).
I want to mark items received or not received. If I do this in the
table, I mess up the column names and importing the order information
doesn't work (at least, it doesn't work with my limited knowledge). I
have tried to create a form where I add in a checkbox, but I ...Sorting Cube dimension names in a Pivot Table
How do I get the dimension names in the "pivot table field list" sorted by
the translated names?
The pivot table field list contains:
- one measure-node
- one dimension node for each cube-dimension.
The cube-dimension-nodes are represented by their translated names.
It looks like, this names are not sorted by this translated names,
but by the order of the data source.
The related radio-buttons in the pivot-table-options are greyed ("A to Z"
and "sort by data source").
I would need the "A" to "Z" order.
Pleas notice! I...Relative reference Chart data
Hi, This is probably easier than I make it out to be but here goes.
I'm making a list of references I've been using for a research paper in
excel and because the list is quite large (50 sources) I wanted to
visually depict how useful each reference is. So I created a bubble
chart with "relevance" on the x-axis and "quality of the source" on the
y-axis which references a 1-4 scale for relevance and quality. That
works fine, but when I want to sort references, let's say by date, the
references for the chart data are absolute, which means if a Row is
sorted up or ...Does anyone know of a software application for Pivot tables other. #2
We have some very complex pivot tables in Excel that unfortunately are not
very stable or flexible when you make changes. The data list is used to
generate multiple pivot tables in the same workbook but the data and the
number of records can change frequently. One time we might have 18000
records and the next time we might have 2500 records. So far it seems that
everytime we have to change the data we have to reset the data source range
for each pivot table. I believe each pivot table was copied via Edit, Move
or Copy, rather than being generated as anew pivot table or based on anot...Access 2007 Pivot Table Chart
I've created a Pivot Chart and would like to add value labels to the
Is that possible?
...Purging old data from pivot table
Is there any way to purge old data from a pivot table? I modified th
contents of a column of data, and "refresh" put the modified data int
the pivot table but also retained the old data (which no longer exist
in the original spreadsheet). It gets confusing when I need to selec
different items for a new view, since the old non-existant data appear
to be selectable as well as the current data. Saving, closing,
reopening did not help either
Message posted from http://www.ExcelForum.com
have a look at
Frankfur...Renaming forms tables and reports
Is choosing "save as" and making a copy and removing the original the only
way to rename a report, table, or form? I started with the Issues template
but I want to change the names of the objects. I can right click and rename
the shortcuts in the navigator window, but the name doesn't change and the
references are still under the old name. I do not want my shortcut to be a
different name than the reference name because then there will be confusion
for database maintenance.
I've also tried renaming the objects in their properties window, but I
couldn't find a na...Report using Fields with the same name from different tables
Hello all -
I created a db with no knowledge of naming convention. Now I am attempting to
write some reports pulling data from tables with the same field names.
Apparently Access doesn't like this....I attempted to change the names in the
BE but when I relink the FE it is prompting for parameters.
What is the best way to proceed?
Message posted via AccessMonster.com
TheLee via AccessMonster.com wrote:
>I created a db with no knowledge of naming convention. Now I am attempting to
&...Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data
table of a line chart. I've tried creating the chart without the formatting
applied to cells with the original data (data source), and then applying the
format to the source; as well as creating the chart after applying the format
to the source. The data table in the chart will naturally update with
changed data since it is linked, but will not incorporate any style
formatting. Any thought? Thanks.
Data tables in charts are not particularly flexible. They do not, for
example, support conditional fo...Changing Properties of Fields in Table
Below is part of a code that somebody had given me to change the property of
a field (in this case to make the field a required field).
fld1.properties("Required") = True
What do I need to do if I want to modify this line of code to do the
a) Change a field to have a DATATYPE of Number
b) FIELD SIZE should be DOUBLE
Any help will be appreciated.
You cannot change data types in that way.
Your choices are:
1) Create a new field of the correct type, run an Update query to transfer
the data from the old field to the new field, delete the old field then
rename ...Rotate data table by 180 deg.
How to rotate data table (matrix) by 180 deg.?
Kind of what Transpose function do just I need to do two
Transposes in the same direction, as to rotate picture by
For example to rotate table:
1 2 3 4
11 12 13 14
21 22 23 24
24 23 22 21
14 13 12 11
4 3 2 1
Of course it is possible to do by linking one cell to
other, but very long process for big matrix.
With your range called "myrng", select A1 of a new sheet,
enter this formula, and press <ctrl><shift><enter>:
=OFFSET(myrng,ROWS(myrng)-ROW(),C...GUID In Table
I have a GUID in a table that I fill programmatically. Often the table will
be searched on the GUID. It is just a GUID generated from dotnet in a
varchar field. The majority of the time the table will be searched on this
GUID... only sometimes will be it be searched on an the primary key ID (just
an identity). What type of index, if any, should I place on the GUID? Unique
Constraint, Unique Index?
The table may get to a couple million records.
I suggest you store the GUID value in a uniqueidentifier column instead of
varchar . This way, you'll store the native 16 byt...PayPeriod table
I am working on a payroll system. One of the task is to create a payperiod
table to capture the previous payroll currently store in the Excel file from
year 2006 to present. The payperiod is bi-weekly and I want to populate this
table with the pervios payperiod and up to year 2050. The columns in this
table will have the following fields:
Calendar Year - which is determined by the PayPeriod End Date
PayPeriod Start Date
PayPeriod End Date
PayPeriod - PayPeriod Start Date - PayPeriod End Date
How to write a sql query to populate this table? PayPeriod always start on
Sund...Removing a field From a Pivot Table
I'm working with a pvt table in excel. Right now there are several
fields in the pvt table, including the month different entries are made
and the account that made them. When i hit the show detail button in
the pivot table i get the detail all of the entries grouped by month
and by account on a separate sheet. I want to continue to do this, but
i want to also be able to get a second view where i hit the show detail
button and only see the detail grouped my account (month is either
hidden or totally removed from this list of data in the pvt table and
the rows that composed the different...Tables update interrupted
SOP is configured to post through to the GL, I came this morning and saw this
status message, 'tables update interrupted'.
I know I can use the batch recovery for this batch, but before I do
anything, why did this happen? And how can it be prevented?
You know your environment better than anyone of us. Without a description of
what processes, etc., run on your server while posting is happening is very
hard to even venture to give an aswer. This is one of those cases where it's
anyone's best guess if you do not provide any extra info.
My first guess would be check ...Linked Excel Table, Update Table
Using Access 2002. I import an Excel table into my database that is
regularly updated from my company's main database via a BusinessObjects
query. I need to create a table that constantly updates to mirror the linked
Excel table because of record locking issues (can't have multiple people in
the database if queries are based on the Excel table). I use a make-table
query as a starting point... My question is how do I keep the table updated?
Thinking I need to do an update query, a delete query and/or an append query
but not sure how or in what order. Any help would be apprec...Comments on a pivot table
How would one add comments to a pivot table? I want to add two comments to items in a pivot table however when I select different items in the filter list, thus changing the range covered in the pivot tables, the comments remain static and just stay on cell D12 for example.
Any items how I can apply a comment to a specific item in the pivot table? Please see pivot layout below:
Invoice Date Range
I want to add a comment to, for example, the cell with £12.50 i...Table name in Expression builder not allowed?
I tried to do a simple calculation (on a form) but fail to understand this:
=[UnitPrice]*[Quantity] works, however if I use the Tables list from
(below) and construct the formula it shows as:
[OrderDetails].UnitPrice * [OrderDetails].Quantity
and it does not work. I get #Name error
When field names are written as [UnitPrice] is it not a standard practice to
name them as such? Somewhere I also see [OrderDetails]![UnitPrice] notation.
"dindigul" <email@example.com> wrote in
> I tried to do a simple calculation ...Pivot Table Help
I have a challenging pivot table issue that I hope I can get some help
Here is the spreadsheet I'm working with:
I'm having problems with showing percentages in my pivot table such as
gross margin and operational profit. Here is the P&L pivot table as I
have it now, as you can see I can't get the "Gross Margin %" to output
a number. Both "Gross Margin %" and "Operational Profit %" are
calculated fields I've added to the pivot table since the raw data
only inc...Problem making versions of a table by changing sourse data in another workbook
How to make a version of a table by changing source data in another workbook
and retain original version of the table easily? I lose the original version
of the table if I change the source data in the other workbook even if I
make a copy of the original table workbook. I have to recreate all links if
I make copies of the original table workbook and the source workbook.
Have you tried making a "snapshot" of the worksheet? (Converting the
formulae into their values - fixed forever - using copy/paste special
Just don't do it unless you have a secure copy ...The pivot tables 101 article says to use the "Create List" comman.
I cannot locate the "Create List" command anywhere, yet in the beginning
article on pivot tables for excel 2002-2003, it says to start with a "list: A
series of rows that contains related data or a series of rows that you
designate to function as a datasheet by using the Create List command."
'Create List' is a new feature in Excel 2003. If you're using an earlier
version, you can ignore that reference. Create a table, with headings in
the first row, and data in the following rows.
There's a PivotTable intro and links on Jon Peltier's web site:
...Combining Tables #2
I have two tables with different sets of information, but they have a column
in common - the ID number. The tables have differing numbers of rows, ie.
some of the ID numbers in one table are not in the other table. How do I go
about combining the two tables, so that the ID's match up, and the info from
one table is carried to the matching ID in the other table? Sure appreciate
your help on this - i'm under a lot of pressure to do this quickly, and
cannot afford the time to go line by line matching ID's!!! :-) Thanks!
This is usually done with formulas...calculate net income i.e. revenues (-) expenses in a pivot table
I need to calculate net income i.e. revenues (-) expenses in a pivot
table. My source data is all positive amounts. I do have a field that
identifies the amount as a Revenue or Expense.
Source fields are:
4.GL Line item description
6 Rev_Exp _ID
Once the pivot table is setup how can I get a TOTAL calculation that is
revenues (-) expenses by Section by month/Yr?
Column fields are: Month/Yr
Row fields are: Section, GL Number, GL line item description,
Data is Sum of $_amount.
Try creating a ...Turn off "alerts" to save/delete tables
I am trying to run a Macro that runs 3 queries. In the process tables are
being deleted/created and there are a lot of prompts to save/delete etc... I
just want to turn these off. I got some advice but was having a few issues
Here is where I am so far.
In the Macro - I tried to turn off the alerts at the beginning and then turn
them on again.
End of macro...
ModuleName = Module1
Procedure = SetOff
In Module1 I have
Option Compare Da...