Table Import or Mail Merge Macro?
I need to set up (item card, assign site, assign creditor) about 250 new
items. I've been testing the table import process in the test company, but
keep getting handling exception errors when testing the item after the
"successful" import because of an obscure (for me) blank field.
All the items have the same Class ID, creditor, site, etc. They only differ
in item code, desc, curr cost, list price, gen desc, and creditor item code.
So would I have better luck recording a macro setting up the first one and
mail merging the necessary fields?
If you believe table import is ...Pivot Table
I have one issue when i get the data through the pivot table
option. after creating a pivot table, i enabled the sub-total and the
grand total in the pivot table properties. after enabling that the
sub-total and the grand total columns appear as #VALUE!.
I have office 2003 in OS 2000 proffesion.
Please help me
You have one suggestion in .excel
> Hi all,
> I have one issue when i get the data through the pivot table
> option. after creating a pivot table, i enabled the sub-total and the
> grand total in the pivot table properties....on NoData, show value in table
My report recordsourse is based on qrystudent where i have 2 tables.
'duration' table has 2 fields [from] and [to].
on NoData event of the report, I want to give message like 'You have not
logged in during ' [from] and [to]
If NoData, how can I give message include fields value in 'duration' table?
'duration' table has only one record.
Song Su wrote:
>My report recordsourse is based on qrystudent where i have 2 tables.
>'duration' table has 2 fields [from] and [to].
>on NoData event of the report, I want to give message l...Changing font in rich text table
I want to standardise the font of all data in a table's rich text
field. If I use:
Dim DB As Database
Dim rst As Recordset
Dim strSQL As String
Set DB = CurrentDb()
strSQL = "SELECT tblReportTermly.Aims FROM tblReportTermly;"
Set rst = DB.OpenRecordset(strSQL)
Do Until rst.EOF
!Aims.FontName = "Calibri"
!Aims.FontSize = "11"
i get "Object doesn't support this property or method". Am I
approaching this incorrectly?
...Alternatives to MS Query for Pivot Tables & Cubes
I am using Excel 2003. I would like to know if there is an add-in or other
(preferably free) tool out there that I could use to create OLAP cubes for
Excel pivot tables that would have better functionality than MS Query. I find
MS Query very difficult to use and almost impossible for creating calculated
fields that can then be built into a cube. (Note: I thought I could just make
calculated fields in the pivot table itself, but now I see that Excel will
not let you do that when using an OLAP cube as a source.)
Any suggestions would be greatly appreciated.
...Dimension Update and Pivot table report
I have an Excel (03) Report file which uses Analysis Services (SP4) Cube.
Works fine still using AS 2000 not 2005.
When I make any changes to Dimension and process Dim. and Cube Excel file
doesn't takes my Dim changes... I have to take (drag) the changed Dim. back
to field list and bring it back to report.... then only it works.....problem
is we have 100s of Excel Cube Reports.
Error I get: The item Could not be found in OLAP Cube.
Thank you - Ashok
I'm curious I can change apperance in chat window. Like the background of
the tab of my messages and different background of the tab of the different
I don't mean the background of the text.
And is there any way of increace the spacing of lines between them? The
lines are really near...
For more understanding: I wish to control it somewhere with like a css
command "Padding" or "margin".
font color: gold;
font size: 1.5em;
font color: black;
I think y...Getting a value from large table.
Hi, I have a data table where column A = Make (named MAKE), B = Model (named
MODEL), Row 1 = Model Year (named MODELYR).
On a different sheet, I have a list of miscellaneous combinations. On this
sheet, column A contains the make, B the Model and C the Model Year.
How can I get the contents from the table to be populated in column D for my
I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but
continue to get the #Value error. I have also array-entered each of the
I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1.
TIA, Br...Using ad hoc table to select which records to print
Hi all. I have a situation I could use some help with. I have a
database used for a high-volume electronics repair business, and I'm
trying to streamline pre-shipment paperwork functions for customer
service. Depending on the customer and order type, up to 6 different
reports might be printed for each order.
I've set up a table populated on an ad hoc basis by customer service
through a continuous form with a text box for the order identification
number and a check box for each report to select which to print. Each
of these reports might also be printed from elsewhere in the database
(...Re: Debtor Period Summary Table RM00104
i was doing some customer monthly sales report, and i come across this
it has a field called HISTTYPE, History Type. and it has a value or either 0
or 1, for the financial year of 2006, which is also the 1st year GP started
in the company, there are some customers having both 0 and 1 for the same
customer1, period5, year2006, histtype1, $309
customer1, period5, year2006, histtype0, $253
could anyone shed some light on what the history type is? coz in GP, when
reviewing past month sales, seems it brings up figers in the row where
histtype is 1
...VBA Import from Access using ODBC
I am trying to import from Access using ODBC - then I want to
basically concatenate two of the columns.
If I run my macro it actually runs the concatenate part first, then
brings in the odbc information.
If I put a pause or step through it - it works no problem. Code is
What am I doing wrong?
Thanks in advance...
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & fileandpath & ";DefaultDir="
& spath & "; DriverId=281;FIL=MS Access;MaxBufferSize =2048;
PageTimeout =5;")), Dest...changing proportion of chart and data table
I have a chart with 19 lines of data and I have been requested to print the
data table with the chart. When I add the data table so that I can read the
text (e.g., 11 or 12 pt font), I cannot see all the data table line in the
page viewer or print it all out. I can only print out all the data table
lines along with the chart if I make the font so small that it renders the
data table useless (or similarly, if I shrink the size of the size the chart
so that what I am looking at is so small you can't read the labels anymore).
In either case, when I do this I also I end up with half ...performance issues linking sql 2000 table in access
I hope I'm just overlooking the obvious and there is an easy solution for my
issue. I scanned the messages here and didn't see anything that looked
My issues is I have a SQL database with a table containing a little over 2.5
million rows linked to access. We typically open the linked table and filter
for a subset of records to modify in table view. The database was recently
moved to a new faster server with more memory and a bigger hard drive. After
the initial move performance when opening and using the table seemed more
robust than on the older slower machine. The ...Pivot Table Filter Error
When I filter data in my pivot table in Excel 2007, it is splitting up the
same data. For instance, if there are numbers describing a job poisition the
filter is splitting the people who are in group 12. instead of showing me 25
people in group 12 it is showing two groups of 12's and splitting them up as
23 and 2, instead of one group of 25.
I formatted the column as all numbers and that didn't work.
Any other suggestions?
...pivot table #5
I have a pivot table and would like to view and change my SQL and database
Are there any way to open it to debug and verify it?
I know that I can edit SQL from MS SQL if I use MS SQL to retreive data.
I could not figure to open the cub file to deit and debug.
Your inforamtion is great appreciated,
The free Pivot Play add-in, located at Debra Dalgleish's website, may help:
It allows you to view/edit the connection and SQL code for Pivot Tables that
use external data sources. It has many other features, too.
Does that help?
-----...Report from 2 tables
Just wanted to get some advice before I attempt to make a report that
will use 2 tables as source material.
Basically, my database stores information on a sport.
One table is a list of teams and their historical venues/successes etc
The second table is a list of the teams annual results.
The report I want to make will be a page per team report, that at the
top shows the data from the 'venues' table, so where there are more
than one venue, it would need to 'loop' through the table to show all
As far as the 'successes' (League Championships/cup w...Pivot Tables #7
Before i post my question am I on the correct board to answer my question?
I've a question on pivot tables and want to make sure I'm in the correct
Here is my question.
I have a pivot table that does what I want it to except the category field
that is a column field shows two different figures and I would like the
final column to calculate the percentage of one against the other.
For example extract of pivot table is like this:
Calls answered Calls resolved
100 5...limitation of pivot tables in excel 2000
When i choose anything from any columns to zoom in datas ,it cannot b
done. An error message appears as follows: "Microsoft Excel cannot mak
this change because there are too many row or column items. Drag a
least one row or column field off the Pivot Table, or to the pag
position. Alternatively, right click a field, and then click Hide o
Hide Levels on the shortcut menu." I have tried to hide some column(s
but the same error message appearred. Anybody have any idea about thi
error message, please reply to this forum. Thank
Message posted from http://www.ExcelForum.com
tcm wro...Hour manipulation
i have a file download that shows hours worked for employees in th
following format 72:09:50(hh:mm:ss). The problem is this, the data i
displayed in that format but in the formula bar it shows the data a
1/3/1900 12:09:50 AM. Because of this, I can not manipulate the data
How can I, in essence, make the data a string or some state in whic
it is not recognized as a date and time. I have tried copying the dat
and pasting it as a value, one hundred different formatting tries, an
everything else I can think of. pls hl
Message posted from http://www.ExcelForum.com
Redwings, this will tur...Excel 2003 Pivot Table Interactivity
With Office 2003 Web Components installed, I saved a Pivot
Table Report as a web page with interactivity. When I
clicked the final Publish btn., the P.T. opened in I.E.
with interactivity. Using AceFTP software to upload the
P.T. to my personal web site, all I got was the P.T. field
layout and no labels or data. The data field had the
No Details. The query could not be processed: The data
provider didn't supply any further error information.
What am I missing?
...last name May sorted as Month in pivot table
I am importing data into an excel 2002 spreadsheet. The
data has a field called Last Name that is formatted as
In a pivot table the last name of May shows up first. I
have verified that excel thinks it is a date. I have
unclicked auto formatting and clicked preserve formatting
among other things. This field keeps reverting to general
Thanks in advance
In your source data, you can replace the name May with May<space>, and
it should sort properly.
To change the names, select the column, and choose Edit>Replace
In the Find what box, t...Collecting data from more than one row in a pivot table
I'm making a customer base where I want to make a pivottable that ca
give an statistical overview on when it is most likely to get throug
to a particular type of professional (it-director, sales manager, CEO
In each company I have made room for up to four contact persons, eac
with his/her own sets of columns (name, position, telephone, etc) an
up to three contacts so I can see the development in the contact (e.g
first contact results in a call-back, the second a reference to contac
person 2, the third a meeting, etc).
Each time I choose the result of a particular contact from a...Text in pivot table
I'm aware that summary functions will not work for text data in Excel 2000
(eg something like 'first' in Access crosstabs doesn't exist). Two
- Is this functionality available in later versions?
- What are the alternatives (eg anyone got some VBA they've used to do it)?
Pivottables are still quantitative summaries.
You may want to give more info about what your data looks like and what you
want. I might lead to better alternatives.
green biro wrote:
> I'm aware that summary functions will not work for text data in Excel 2000
> (...How to filter field button items in pivot tables
I am making a spreadsheet that displays test times for various software tests
across several sw builds. It looks like this:
Build A runs tests 1-100
Build B runs tests 101-200
Build C runs tests 201-300
Build D runs tests 301-400
All of the data is in my data worksheet.
I build the pivot, then go to the filter button for build and select A, C,
B. Then I go to the filter button for test # and I see 1-400. Is there some
way to actively filter the options for test # so that I do not see 201-300
when I don't select C? The solution would have to be active in that when a
person makes a ...Pivot Table #22
My knowledge of pivot table is limited... so kindly help if possible...
Say if I have a column of data (Satisfaction Level) as the one on the
horizontal field (Y axis) of the pivot table and straight away, it will
show the different group of Satisfaction Level.
Sat Level has a range from 1 to 5 so there will be 5 columns... My
question is how can I include another two columns in the pivot table to
show the sum of 1 and 2 and the sum of 4 and 5? Is this possible?
Currently, I am doing the calculations manually... which is taking alot
of my time especially if there are many different...