Consolidating tables for reporting through crystal
I was wondering if anyone out there has come up with a good way of reporting
across multiple companies? We have 30 distinct companies in GP and the best
solution I have come up with is to create a stored procedure that pulls data
from each company db. I can then use Crystal to report off that sp and get
consolidated information. Any thoughts would be appreciated!
Consider using views using union queries, instead of stored procs.
there is one other way
You cn use an OLAP tool such as Proclarity from Professional Advantage or XL
Reporter from solver Inc
These tools give you more tha...Speeding up access programs with linked tables
The business that i help program is running with alot of access programs and
many of them seem to be extremely slow when they are using linked tables.
what are some possible solutions for this situation?
What are the linked to? Other Access databases or some other database such as
SQL Server or Oracle?
If another database, look into creating views in them that gathers up the
data especially if there are table joins. Also research pass-through queries
where the work in done on the database server and not Access.
If linked to other Access databases, then prop...How do I reference one cell in a table by using both the row and .
If I have a table with column headings and row headings that are different
and I want to find out the information for a particular cell based on what is
in those headings, how would I do that. i.e. if I had columns marked 1-10
and rows labled 2005 - 2010 how would I find the information of the cell that
matched column 6 and row 2008?
Assuming the column/row you want to look up is in K1/K2, respectively,
or, if you named your table "table":
In artic...Deploy a DDL trigger to prevent dropping tables for mulitple datab
The SQL 2005 server I am using has mulitple databases. The database MyDB is
one of them. I make a DDL trigger to prevent deleting columns in tables in
MyDB. Here is the script to make the trigger:
CREATE TRIGGER Safety
RAISERROR ('Table schema cannot be modified in database. You must disable
Trigger "Safety" to drop or alter tables!', 16, 1);
The trigger works well for MyDB. Whenever deleting a column in a table
inside MyDB, the transaction is not allowed. Now, I want to set up the same
trigger not only mon...Pivot Table
Is there any way to set the default settings for the Table Options for every
new pivot table that you build?
Also, is there a way to change the default format for a numeric field
(number, 0 decimal places, instead of the original 2)
...Linked Tables headache
I have a database with 43 linked files. Normally, when I need to link
it to a new version of the back end, I use the Linked Table Manager, I
thick Always Prompt for New Location, then click on Select All, then
browse to where the back end is, and it links all the tables. Now, it
is asking me again for each individual table. This is totally
unacceptable from the clients point of view. 1) It takes all day. 2)
It's too much can go wrong.
Is there any way I can write my own routine to do this properly?
See http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web"...Using Microsoft Query data in a pivot table.
I'm using MS Query/SQL to pull data from a database that holds accounting
I have a column (fee) that represents a payment that was made, so,
obviously, the number is negative. In SQL, I can change the code to multiply
the column by -1 (-1*fee)to make the data positive and everything works fine.
However, when I refresh the data in my spreadsheet, it takes that field and
moves it to the last column. Do Pivot Tables in Excel expect the columns in
a certain order, or does it only look for a matching header, regardless of
location? If the former is the case, how to I ge...differences between two tables
I'm trying to find the differences between two tables, A and B. There are
some records in A that are not in B, there are some in B that are not in A,
and there are some in both but with differences in the fields. I'm not
getting everything. Does anyone have any ideas how I can tackle this? Thanks!
Use a UNION ALL query.
Build a little - Test a little
> I'm trying to find the differences between two tables, A and B. There are
> some records in A that are not in B, there are some in B that are not in A,
> and there are s...Excel Graphs / Table not printing with the Word Document
Operating System: Mac OS X 10.5 (Leopard)
I've cut and pasted excel graphs / charts into a word document. When I print the document the spaces where the Excel products are located come out blank. Text prints fine. I'm fairly new with Mac so it's likely a setting I made but cannot find what I did to correct problem.
> Version: 2008
> Operating System: Mac OS X 10.5 (Leopard)
> I've cut and pasted excel graphs / charts into a word document. When I print the document the spaces where the Excel products are locat...Pivot Table with two row header?
Can I create a Pivot Table using a combination of two rows as the
Row B1 = May_05
Row B2 = June_05
A3 thru A10 = Dates
B3 thru B10 = Values
I want to establish B1 & B2 together is the header row. I need to do
this because C1 = May_05, C2 = Aug_05. My header is a pair of months.
Does anyone know if this is possible?
sslack's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16442
View this thread: http://www.excelforum.com/showthread.php...Problem with pivot table #2
I am trying to create a pivot table. I have the following fields: cust#,
item#, and quantities for different months.
I want the pivot table to look like this:
cust item jan feb march
123 1 1 2 3
123 2 4 5 6
124 1 1 1 1
But I am getting
cust item Data
123 1 sum of January 1
sum of February 2
sum of March 3
123 2 sum of Jan etc
How do I do this?
In the Pi...Pivot Table showing wrong data
I have a table with information such as Salesperson, Customer, Sales $, Year,
Month, Date_Customer_Started. The data contains info for 2009 and 2010. I am
trying to determine for every new account created in 2009 and 2010, Sales by
new customer and salesperson. The Pivot table has sales data and count of
customers by year and the rows contain Salesperson, Customer, Month and
date_Customer_started. I expect if a customer started in April 2009, and the
customer continuing to buy in 2010, sales figures by year and month and the
count of customer only in 2009. But the pivot table sh...Copy setup tables and changing functional currency
I have a Company database named PRIMARY where the Default currency is USD.
The Company has 8 other currencies. I have a brand new Company database named
SECONDARY and I want the Default currency to be VEB and not USD. I have read
tech articles 872709 and 874208 on using DTS to get the setup data over to
the new Company database. This is a 7.5 version of GP. I seem to have an
issue with database triggers on the SECONDARY Company database after the DTS.
Has anybody been through the process of creating a new Company database along
with DTS of the setup tables and changed the functional cu...Creating Lookup Field Between Two Tables
I have a main table with about 5000 records of companies I may want to do
business with. Now I've created another table showing the differences in the
business practices of the companies. I'd like to be able to lookup the
company name and information in table one by ID number rather than retype it
for table two. I know there is a way to create a lookup table in table two
in order to look it up and select the applicable ID number but I've tried
using the Wizard and I'm getting nowhere. HELP! Thanks, Ron
You need to create forms based on your table(s) or quer...store multiple timestamps in one row on table
I am trying to create a form that allows an employee to select their
name and click a command button to store the time they leave for lunch
and another button the time that they return. I have each button
coded to be written to a table, but I would like to have the in and
out times be stored on the same row so I can have a function subtract
the two. Currently, it writes a new row for each click of the
button. Any help would be appreciated.
On Fri, 8 Feb 2008 12:19:56 -0800 (PST), email@example.com wrote:
>I am trying to create a form that allows an employee to ...New Excel data does not appear in linked Access table
I have an Excel2000 worksheet which contains 5 columns of data. I created a
table in Access97 which is linked to the worksheet.
For the last year, any data added to the worksheet appears in the table.
Now the worksheet exceeds 1,683 rows. All data in rows 1684 and beyond does
not appear in the table.
The worksheet has never had any Named Ranges. Following the suggestion in
article 197232, I defined a name range which included data in rows 1 to 2000.
Data in rows 1684 and beyond is still not visible in the table.
Is the "restriction" in Access or Excel? How can I get around thi...Pivot Table
Good day everyone.
Is there a way to deselect multiple items from a field that was dragged into the Pivot Table? I have some fields that have 100 choices and I would like to quickly display just areas that I need. This means that I may select 10 items and deselect 90. Then, get the numbers I was looking for and repeat the process for a different 10. Grouping isn't an option because people come with different requests, many with overlapping records.
Thank you and have a great day.
You can use a macro to hide or show the items -- there's some sample
code on my web sit...External table is not in the expected format
Access 2003 - trying to import some dbf/cdx files
On one machine - access 2003 sp1, they import fine. On another machine, also
Access 2003 sp1, I get an error 'External table is not in the expected
Why would this be so ?
...Convert Word Table to Excel Spreadsheet
I know how to use the mail merge feature from Excel to Word, but how
about from existing labels in Word to a usable Excel spreadsheet. My
approaches have been to save word as .txt and then get external data in
Excel. That works great accept I need to be able to have different
column fields like Name, Address, City, State etc. What is the best way
to go about doing this?
Any help?? Thank you in advance.
Have you tried Copy and Paste?
You may need to play with cell borders if the borders show in the Word doc.
Bernard V Liengme
remove caps from em...About Purchase Order Number Type
Would there be any ways to handle different automatic number series for
Standard Purchase Order rather than manually changing it during input?
GP only offers one numbering sequence for Purchase Orders, so this is not
possible without either manually changing the numbers of a customization.
Dynamics GP MVP
Flexible Solutions, Inc.
"Alexis" <Alexis@discussions.microsoft.com> wrote in message
> Would there be any ways to handle different automatic number series for
> Standard Purcha...How to analyze data and output it in table form?
I am a beginner in MS Access and would like to try some data analysis.
I have a database like this:
And I would like to analyze the data and output a table like this:
Please let me know what I should use(e.g. query, functions, reports, etc.).
If possible, please let me know the corresponding tutorials so that I can
understand what's going on. I wonder if crosstab query is the answer. Thank
...Pivot table order active
how do i get my order feature in a pivot table actvie
it's greyed out now?
what are the steps?
What are you trying to do in the pivot table?
What do you have selected when you choose the Order command?
If you're trying to rearrange the items in a field, right-click on the
item in the heading area, and choose Order, then one of the Move options.
> how do i get my order feature in a pivot table actvie
> it's greyed out now?
> what are the steps?
Excel FAQ, Tips & Book List
http://www.contextu...Adding records to a table via a subform
I have a many-to-many relationship between event outings and
participants. On the form for the outing, the user may select
participants from a dropdown box in a subform to add an entry to the
table that links the event outings table and participants table.
I'm running into an issue with the subform. The entry to the link
table is not being created until I go to the next row down on the
subform,. So if I select Albert Adams from the dropdown, no record is
created until I go to the next row in the subform. Is there a way to
Jeffrey Marks wrote:
...populate table using multiselect listbox
I have a main form called f001ProjectReview (PK ProjectID).
There is a subform called f022Shipping(PK ShipID, SK ProjectID) with a
simple multiselect listbox called lstIncoTerm. The listbox can have a
multiple choice selection.
When the user selects one or more items in the listbox, how do I get the
table called t12ShipIncoTerms to update with the ShipID and lstIncoTerms for
each selection in the listbox?
Thanks in advance!
On Fri, 26 Feb 2010 12:00:01 -0800, deb <firstname.lastname@example.org> wrote:
>When the user selects one or more...Word table cell marking routine needed
I have a thorny problem that I am hoping for a creative solution to - I have
scripts that convert Word tables to tab-separated text, but because some
cells are (intentionally) left empty in the source document, I need a way to
mark them before they are collapsed so that in their collapsed state there
are not two consecutive tabs left behind, rather, a tab followed by a
placeholder string of my own devising, followed by the second tab. My script
consolidates consecutive tabs into a single tab later (because the vast
majority of consecutive tabs in such documents are truly unnecessary). ...