Pivot Table with Define name
Is it possible to use define name in Pivot Table that the define name is
link to another woeksheet?
You can use a named range in another workbook, if that workbook is open.
Or, in step 1 of the pivot table wizard, select external data source,
and connect to the other workbook.
Alan Tang wrote:
> Is it possible to use define name in Pivot Table that the define name is
> link to another woeksheet?
Excel FAQ, Tips & Book List
...Multiple Table Join Issue
In a payroll system there are 4 tables
One to many relationship
Check Summary (cs) - Master
Hours & Earnings (h&e)
Select cs.lastname, cs. firstname, cs.middlename, cs.clockno, cs.paydate,
cs.payperiod, he.paycodedescript, he.paycode, he.currentearnings,
t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct
right join cs on cs.csid = he.linktocsid
right join t.linktocsid = cs.csid
right join d.linkto csid = cs.csid
where cs.paydate >=(start of date range) and cs.paydate <=(end of date range)
The result looks like this
regpay...How do I create a one variable data table?
Look in Excel's help files for 'one variable data table'.
And there's information in the following MSKB article:
XL: How to Create and Use One-Input Data Tables in Microsoft Excel
Excel FAQ, Tips & Book List
...Case insensitive XPath query?
I'm using MS SQL Server 2005. I have this clause in a query that
looks at an XML column ...
@name[fn:contains(.,'Mountain Biking')]') = 1)
Does anyone know a way to make the "fn:contains" clause do a case
insensitive search? Right now the search is based on case but I would
like to write one in which case doesn't matter.
Thanks for your feedback, - Dave
laredotornado (email@example.com) writes:
> I'm using MS SQL Server 2005. I have this clause in...pivot tables formatting
I have a sales pivot with the months in columns and
products by category in the rows. I simply want to
have "normal" lines, which will make for easy reading like
any other report at print time. Also I would like the
product headers left aligned down in their column
Whatever formatting I apply in whatever way I can select
those items, my formatting is undone on a refresh, though
the auto-format option is off in the table setup. It does
keep seetings like the font and bold, etc, but lines and
alignment instructions are rudely overwritten.
Anybody with an answer to that one? I w...Total in Pivot Table
In my pivot table, for example, I have 20 customers with their sales
amount. Then I made a sort by sales amount and for presentation I put
in pivot table to show the 10 bigger customers.
But my problem is that the total only sum the 10 customer selected and
I would like that the total represents the sum of all customers.
I have been tried to click in pivot table - options and flag the field
- selected subtotal in hidden itens, but it is not working.
Could you help me with this matter???
Thanks in advance!!!
...Hiding rows containing zeroes or blanks in pivot tables?
How do I hide rows containing zeroes or blanks in pivot tables?
You can use programming to hide the rows with a zero total. For example:
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
'unhide all...Form for inputing criteria for a report's query
I have a report that is based on a query. The criteria for the start date
changes and I want a form where I can input the date range (Between
#2/2/2006# and #2/4/2006#) each time the report is run. Is there a way to set
up the form so that the forms asks for the user to put in the start date and
the end date and then put those results in the format above into my query to
run the report correctly?
>I have a report that is based on a query. The criteria for the start date
>changes and I want a form where I can input the date range (Between
>#2/2/2...pivot table question #2
I have two columns of data:
There may be a variable number of dates, and does not contain a
complete list of consecutive dates (i.e. some dates have no associated
data and are not in the list)
I want to produce a calculation the that subtracts the MIN for each
date from the MAX of each date. I do not necessarily have to display
the max and min values - I am really after the result
Date (max - min)
1/1/05 3 <12-9=3>
1/9/05 12 <16-4>
What is the best way to do this?
I have experimen...Storing Query Into Table
I have been trying to find some information about storing query sql strings
in a table and calling upon that string to run query's dependant on user
selections in a form.
I have a split database and I want the users of the front end to create
queries that everyone can use.
Can anyone point me in the right direction on where to start.
There is a complete query by form applet at
allows users to select field, enter criteria, sort, group, and run. The
results are displayed in a datasheet subform...How to automatically populate a table with query results
How do I do the following? My database is:
Table Title - Main
Field in Main Table
[Pricingdata] - Numeric - contains Pricing data
Query - Contains [Pricingdata] from Main Table
Contains Expression Field: ([New Pricing Data]= [Pricingdata] +
I want to automatically overwrite all data in the Main table [Pricingdata]
field with results of the query Expression Field.
Any assistance is deeply appreciated.
On Sat, 24 Mar 2007 09:31:56 -0500, "Frank" <firstname.lastname@example.org> wrote:
>I want to automatically overwrite ...combo box, how to limit to client table
I have the following tables: CLIENT, EMPLOYEE, OFFICE LOCATION. I have a one
to many relationship with the client> employee and client>office location.
Now i can associate all the clients employees as well as the clients
locations. On my employee form i would like to have a drop down box that
shows all the clients office locations. so i can specify which office the
employee is located.
think i have to run a query, then limit the list to the client ID???? am i on
the right track??? help thaks.
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Fo...what does "data source reference is not valid' in Pivot table
Can someone tell me how to resolve 'data source reference is not valid' error
while creating a pivot table
I Googled your question and found 21,000,000 + answers.
Could luck !
'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=126977
...Opening a form by clicking on a record in a table
Is it possible to click on a field of a specific record of a table in
microsoft access and open a form containing information about that record.
If so how is it done?
> Is it possible to click on a field of a specific record of a table in
> microsoft access and open a form containing information about that
> record. If so how is it done?
It's possible to open a form from another form, but not from a table. Tables
should never be used for anything after intitial development.
Rick Brandt, Microsoft Access MVP
Emai...Pivot Table Help...
Hello, i am having a little problem creating a Pivot table for my poker
league. Basically, there is a 15 week season, with different players
playing each week (well, new people can join anytime). You accumulate
points on how well you finish each with. I have an excel workbook with
keeps track of the standings for each week, and points for each person
for the week. I can't figure out though how to create a table to
compute the total points for each person. anyone want want to take a
look and help me out. My email is email@example.com. Any other
ways that would work?
...Display Data In Form From A Query
I have a form with the Control Source as
and this query runs prior to the form opening.
The query runs fine, and the proper data displays in the datasheet view, but
my form Text Box will not show the data.
Any suggestions out there?
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"GeorgeSoares" <GeorgeSoares@discussions.microsoft.com> wrote in message
news:6F2E1E06-8772-4E6F-B1A7-647E14A1A7F0@microsoft.c...Customer Internet Information Table
Anyone Know where the Customer Internet Information table can be found? I
have searched through the Resources in GP but can't seem to find any
documentation on this table.
How you access the form is open up a Customer Card and click the Italized I
Button next to the Address ID Field
It's SY01200, (Company table). The field "Master_Type" tells you which
series it comes from. In your case I think the value for this field would be
> Anyone Know where the Customer Internet Information table can be found? I
> have search...Edit Query
I am new to macros and am trying to write one which enables me to access
MSQuery file for editing from my worksheet.
The worksheet has to be updated each month so I only need to edit this
criteria of the query.
Any advice would be greatly appreciated
You have 3 different methods you can use
1) Manually create a query using the Excel menus. This type of query is
fix (can't change parameters) except using the Query editor from the
worksheet menues. Allows only reading of a database or excel file.
2) Record a macro while performing a query (use as a template). Then
mo...Query where 3 Fields must contain same value
I would like to write a query that pulls records where the value in 3
different Columns is the same.
Return records where each field below contains same value
Return below records
Field1 | Field2 | Field3 |
150 150 150
Do not return rows like below
Field1 | Field2 | Field3 |
175 26 15
Message posted via SQLMonster.com
WHERE Col1 = Col2 AND Col2 = C...PIVOT TABLE HELP #4
i have a spreadsheet with columns ( Vender, Month, Amount ) i'm trying to set
up my table as follows: Column Fields are MONTH, Row Fields are Vender, and
Data Items are AMOUNT. But when i put the AMOUNT in it changes to something
like 1,2,3,4,5. How do i keep my AMOUNT as currency
Please see reply to your post in excel.misc
Please do not multipost.
"Michele" <Michele@discussions.microsoft.com> wrote in message
>i have a spreadsheet with columns ( Vender, Month, Amount ) i'm...pivot table
Hi - have just switched to XP
In a pivot in old excel I use to to click in a subtotal
field eg "Europe Total" and excel would automatically
highlight all subtotals at that level. It just meant I
could colour the row and it would apply to all of the
subtotals - how can I do that it XP ?
To enable selection --
From the Pivot toolbar, choose PivotTable>Select
Click on Enable Selection
To format the subtotals --
Move the pointer to the left of a subtotal heading in the
When the black arrow appears (like the one that appears
when the pointer is over a r...Mac Excel 08 puts "#VALUE!" in all pivot table references when opening an WIN Excel 97 file.
Operating System: Mac OS X 10.6 (Snow Leopard)
I recently bought a MacBook Pro 15.4, 3.06GHz, 8GB RAM running Mac Excel 2008 (v12.1.0 080409). When I use Mac Excel 2008 to open excel files that I created in Windows Excel 97 2004, all my pivot tables convert correctly but any REFERENCES to any fields within the pivot table do NOT show the valid values that Excel 97 showed. Instead all refrences have "#VALUE!" in the cell. There is a valid reference startement there but no valid value. How can I fix this?
You are a few updates behind: make ...Display/Pull images from web onto form/into table?
I am experimenting with using Amazon Web Services to pull information into
an Access database. I want to be able to use the cover art images Amazon
makes available. At this point, I have gotten as far as being able to
acquire the URL that points to the image. I thought this would be easy but I
1. Is there a way, on a form, to set an image control to display the image
using a URL as a pointer? Is the web browser control the only way to do
2. Is it possible / really hard to do / easy to somehow actually download
the image into the table using VBA?
F...BOM Rolled Cost Report or Query
In GP 7.5 (or later versions?) is there a way to view the rolled up cost
of a BOM assembly at current costs of the items? Can I view the
assembled cost using the standard cost of the part items as well?
To put this anouther way, I want to update the standard cost on the item
master record for all items with BOMs with what it would cost today to
assemble the BOM.
A report(GP or Crystal) or SQL query/view, etc would all work fine.
You could modify Reports-Inventory-Setup-Indented Bill of Materials to
calculate a component Extende...Query works-Export fails "...you and another user are attempting to change the same data..."
Can't figure this out.
I am the only user in the database. The file (RealProperty) can be
queried using a select query that includes just the fields in the
table. I get the above error, however, if I try to export the
resulting table/query in any of the following fashions:
mdb (in a new db designed to store this one table)
..xls (get the same error)
..csv (get the same error)
If it simply try to copy the results, I get the same error.
I have checked: there are no dependencies that are missing; there was
an export errors table, but the three records that had data issues
have been fixed....