I'll start out the question with what I'm trying to accomplish. Basically,
I've created a form that populates data into a table (lets call it table 1).
I want to be able to reference this table against a master table (call it
table 2) and return the matching results. The form has a command button that
runs a query that links these two tables and returns the resulting matches.
The problem I am having is that if a user wanted to leave one of the fields
blank on the populating form the query will not return anything. So lets say
the form asks for State, City, County and Zi...pivot table 2007 formatting does not stay when refreshing
In the file, I have multiple tabs each having its own pivot table. When I
have to refresh one of the tabs, the formatting changes on all the other
tabs changes as well, most importantly the cells that have zero, I have
formatted this to hypens. These cells always goes back to zero's. Very
frustrating! I checked the box that says preserves formatting but it seems
that the formatting is not preserved. What is included in formatting?
Can someone explain why this is happening.
...hide 0 values in pivot table (2003)
Hi I can not find how to hide 0 fields in pivot table in office 2003.
You can hide blank values but not zero values (at least not built in the
pivot table options), you can hide all zero values under tools>options>view
and uncheck zero values. Debra Dalgleish has posted code for hiding zero
"Tim" <email@example.com> wrote in message
> Hi I can not find how to hide 0 fields in pivot table in office 2003.
> thanks tim
...Table Changes in GP 9.0 SP1
We can't seem to find any documentation whether or not there are any table
changes in SP1. Does anyone know if there are any? If so where can we find
Standard policy is not to make table changes in Service Packs.
David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific
Micorosoft Dynamics (formerly Microsoft Business Solutions)
Any views contained within are my personal views and
not necessarily Microsof...Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell
multiple items, but I only need to show the vendor once so I can pull other
information. There are also may be blanks in the rows if the vendor is
Let me add one more twist to this problem:
The unique values also need to filter out where it may be 'VendorA - West'
and 'VendorA - East' and combine them into one record. I only need it to
show VendorA and not both records East and West (same company, just
different drop ship location).
"Karl Burrows" <kfb1@spa...Delayed Response Time Forms/Reports 2003 Linked Table
I've been trying to create forms and reports (queries also) in Access 2003. I
linked an Excel file located on my server and the process is incredibly slow.
I get a significant lag when I try to adjust properties of any type for a
data field or label or any other object.
When I import the excel file it works fine.
Is there anything I could possibly do to fix this issue. It seemed to be
working fine last week, but this week I'm having trouble.
...Pivot tables to count unique records only
I have some data which is sometimes repeated. I want this data to feed
directly into a pivot table and only count the unique records as theres
records in there which appear about 8 times.
When you do count on a reference number it counts it each time, show its
showing about 131 records when its truly 22 !
Can anyone help please?
Windows 98 + Office Pro 97
A pivot table won't calculate a unique count. However, you could add a
column to the database, then add that field to the pivottable.
For example, if you want to count the customers in column A, use...table lines making dashed
I was trying to make a verticle line in a table dashed,
is there a way to do this. I didn' t find one in, format table.
Using basic shapes and a bit of tweaking you can.
MVP Microsoft [Publisher]
"Bruce" <firstname.lastname@example.org> wrote in message
>I was trying to make a verticle line in a table dashed,
> is there a way to do this. I didn' t find one in, format table.
> thanks bruce
Oops - wrong reply to this question
Try creating the table in Word then pastin...Why size of email with pivot table is so big?
I only send one sheet of pivot table to users, and i found every email is
about 1MB, why the size is so big? Did it send with raw data hiding in
...Erwin diagrams for HR Tables?
I am helping some of our HR people write Crystal Reports again the HR tables
in GP 9. I am aware of the various diagrams available in the SDK, but there
don't appear to be any for the HR tables.
Does anyone know a source for Database Diagrams for HR?
Beyond the information provided with the standard GP SDK, I am not sure you
will find anything else.
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
> I am helping some of our HR people write Crystal Reports again the HR tables
> in G...Read only table from replicated database
Our replicated database recently became corrupted (for reason or
reasons undetermined) and data was lost. Indeed some fields from one
table were deleted!
As I was unable to restore the data to the Master Copy, I imported the
tables (back end) into a new database and this was successfully
completed. However, one particular form is served by an underlying
query which brings together three tables. Now here's the strange bit:
when the query is run in the front end database using the corrupted
database, the query works fine and records can be added to the various
tables through the q...Creating a Report from one column of a table
I am not sure if this is possible to do but am hoping it is.
I've created a database that gives information on clients and jobs.
People fill out a form of a person details and tick different jobs -
theres about 50 to choose, but no more than 10 would be picked for one
person. Once they are ticked, it is shown in the table all correctly.
What i am hoping to do is produce a report for each job - over 50
reports. But the report only shows one of the column/ticked boxes.
For example, if theres 20 people and out of them 5 have the job
'retail' ticked, the report for Retail will only...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.
...How can I select pivot table row totals to fill color them
I have a pivot table with total rows. Other people in my team at work can
hover over the left edge of one of the total rows and all total rows get
selected so that a fill color can be applied. It does not work for me
From the PivotTable tool bar, Click on the PivotTable dropdown, select -
"Ruth5155" <Ruth5155@discussions.microsoft.com> wrote in message
>I have a pivot table with total rows. Other people in my team at work can
> hover over the left e...Displaying Total Amount Of Records In A Table
I would like to display the amount of records that are in a table on the form
- do I use a text box and assign the control source somehow...?
Thank you in advance
You could use something like below in the control source on an unbound text
field in the form. I would work best if you put in the primary key field name
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> I would like to display the amount of records that...What table contains the data displayed in 'My Tasks' view?
MS Project Server 2007
Does anyone know what table contains the data displayed in My Tasks?
Not sure which table it is, but when utilising the reporting db you can get
this info of the EPM task user view.
Hope this helps
Marc Soester [MVP]
> MS Project Server 2007
> Does anyone know what table contains the data displayed in My Tasks?
I am trying to pull the My Task information for one or more resources for a
specific period. I've used...Alter table Add column to merge publication
We are currently on SQL Server 2008 and I would like to add a column to a
table that exists in our merge replication.
Below is what I ran in our test environment
Alter Table TableName
Add ColumnNameTypeID uniqueidentifier Null
The column was added, but the Default value or Binding was set to NewID()
and the RowGuid property was set to Yes. I would like the Default value or
Binding not set to anything and the RowGuid set to No. Is there a way to
do this using the Alter Table command?
The new column added is a FK to another table. I am thinking after the
column is adde...Mathematical Calculations in a form linked back to a table
I palnning on implementing calculation between two fields, and the result within another field. Suppose I have three fields called A B and C respectively. In the form I plan on Inputing a value for[A] and Inputing a value for [B]. Now, Field [C] will calculate [A]-[B]. When filed [C] is calculated, it is not in the table, like A and B are. I would like to know how to solve this problem. It is rarely necessary to store a calculated value in a table in Access.
Much more commonly, a form (as you are using) or a query is used to do the
calculation "on the fly", as it were.
...Pivot table ranking
Is there a way to 'rank' the rows in a pivot table (form)? For example, I
have a set of data:
I can see my best seller by sorting (descending) on the "Total Sold" field.
Is there any way (calculated field?) to put an actual ranking next to the
Flavor field? But when the Flavors change during the sort, the Ranking field
should remain static, so the ranking is new with new/updated data.
Can this be done within the Pivot Table or is there another (better?) way of
doing this (Cros...Formatting Totals on Pivot Table
I have a pivot table with customers then product for rows, Month for column
header, and total dollars for the details. Is there a way I can change the
font automatically for all Customer subtotals to bold. The plain border
around the subtotals just is not enough.
...insert into when not exist in table
Hi using sql 2005. I want to insert into table A values(x,y) from table b.
However only when values x,y do not exist in table A?
I'm sure that this is really simply... I'm timing out when alternating
between <ahhh> brain freeze </ahhh> and <ahhh> max complicated </ahhh>
INSERT INTO A (x, y)
SELECT x, y FROM B
SELECT x, y FROM A;
INSERT INTO A (x, y)
SELECT x, y
WHERE NOT EXISTS(SELECT *
WHERE A.x = B.x
AND A.y = B.y);
...data in pivot table
My data is automatically being counted when I don't want it to be. I just
want the data displayed in the data section, but the heading keeps on coming
How do I just get the data to be displayed?
You can select the cell containing the "Count of" and overtype it with
On Wed, 6 Oct 2004 14:57:01 -0700, xausaj <email@example.com>
>My data is automatically being counted when I don't want it to be. I just
>want the data displayed in the data section, but the heading keeps on coming
>...Mail Merging a Table
I work for a church and I am trying to create a donation report for
2004. The data I have is in a CSV in the following format:
Name, Date of Donation, Amount of Donation, Various Address Fields
Does anyone have any suggestions on how I might perform a mail merge on
this data so that all donations by one particular person will be listed
in the same document? For example, if a particular person donated 5
times during the year I would like to create a document with the
Thanks in advance for any guidance you can give me in how I can d...View Pivot Table Source data as a Data Table
Hello kind & helpful people,
I am using Excel 2007
I have a pivot table and I want to be able to look at all of the data that
it represents at once.
That is, I want to see the original data table that was used to create the
I have located the source data specified in the "PivotTable Tools » Options
» Data » Change Data Source" area of the ribbon.
That value is: '\Documents and Settings\xgraham\My Documents\This
Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263
However, the user referred to th...Relink to tables with password on BE
I'm using Dev Ashish's code to relink tables and it works great on a
non-protected db. The issue I am having is that when I add a Password to the
BE, I can't get it to work. Where do I need to put the password in Dev's
code to get it to relink? I'm thinking it needs to be in the connection and
in the table link but I can't get it to work.
I added the password to the following code:
.Connect = ";Database=" & strDBPath & ";PWD=xxx10"
but it is breaking on this which is before the code above