Querying Great Plains

I apologize for asking an "operational" question, but I'm trying to get a
running start without much Great Plains experience.

I'm testing out Great Plains for an implementation for an investment firm.
There are appoximately 400 invested partners in the fund, so I've set up
about 38 Income and Expense Accounts as Fixed Allocation accounts, with each
of these "parent" accounts allocating to 400 (investor) "child" accounts
(esentially I want to divide up the income and expense between the 400
partners).

My allocation is based on the sum of their cumulative Equity, and their
cumulative earned Income/Expense. Because I want to Allocate Income (and
Expense) accounts based, in part, on the same Income Account, it creates a
type of circular reference and Great Plains won't let me do it - which is
fine. Instead, I've just setup the 38 "parent" accounts as fixed allocation
using hard-coded percentages and everything works fine. The problem is this:
occasionally one of the partners will up their Equity stake and I need to
change the 400 percentages in each of 38 accounts - not something that can
be done by hand.

With that said, I'm looking for a recommendation on the best way to "query"
Great Plains to sum cumulative totals for each investor, something like this
InvestorPercentage = (InvestorEquity + InvestorIncome - InvestorExpense) /
(EverybodyEquity + EverbodyIncome - EverybodyExpense). My plan of action is
that if I can get a "query" going, then I'll use the eConnect XML features
to simply rewrite the allocation percentages. So my biggest hurdle at this
point is figuring out how to come up with the percentages to use - I don't
know if there is some way to directly query the tables from SQL, or if there
is some eConnect feature that I can use to run a "report"?

Any help or pointers would be greatly appreciated.

Thanks,
Chris


0
10/14/2004 4:59:02 PM
greatplains 29623 articles. 6 followers. Follow

4 Replies
573 Views

Similar Articles

[PageSpeed] 2

Hi,

Can you give me a simple example using numbers?  I'm staring at your formula 
and it looks like you already have to know the percentage in order to know 
the numerator, and you're using the numerator to come up with the percentage. 
 You can use any number of SQL query tools to get the data directly from the 
tables.  I do alot of work with Crystal reports as an example.

I've never been much good at simulteneous equations - is that what you are 
trying to figure?

Leslie

"Christopher D. Wiederspan" wrote:

> I apologize for asking an "operational" question, but I'm trying to get a
> running start without much Great Plains experience.
> 
> I'm testing out Great Plains for an implementation for an investment firm.
> There are appoximately 400 invested partners in the fund, so I've set up
> about 38 Income and Expense Accounts as Fixed Allocation accounts, with each
> of these "parent" accounts allocating to 400 (investor) "child" accounts
> (esentially I want to divide up the income and expense between the 400
> partners).
> 
> My allocation is based on the sum of their cumulative Equity, and their
> cumulative earned Income/Expense. Because I want to Allocate Income (and
> Expense) accounts based, in part, on the same Income Account, it creates a
> type of circular reference and Great Plains won't let me do it - which is
> fine. Instead, I've just setup the 38 "parent" accounts as fixed allocation
> using hard-coded percentages and everything works fine. The problem is this:
> occasionally one of the partners will up their Equity stake and I need to
> change the 400 percentages in each of 38 accounts - not something that can
> be done by hand.
> 
> With that said, I'm looking for a recommendation on the best way to "query"
> Great Plains to sum cumulative totals for each investor, something like this
> InvestorPercentage = (InvestorEquity + InvestorIncome - InvestorExpense) /
> (EverybodyEquity + EverbodyIncome - EverybodyExpense). My plan of action is
> that if I can get a "query" going, then I'll use the eConnect XML features
> to simply rewrite the allocation percentages. So my biggest hurdle at this
> point is figuring out how to come up with the percentages to use - I don't
> know if there is some way to directly query the tables from SQL, or if there
> is some eConnect feature that I can use to run a "report"?
> 
> Any help or pointers would be greatly appreciated.
> 
> Thanks,
> Chris
> 
> 
> 
0
LeslieVail (702)
10/15/2004 12:49:04 AM
Chris,

Just to clarify, what you are trying to do is to allocalte  operational (or
uncategorized ) income and expenses, right?  From your formula, it appears
tht each investor already has income and expense accounts in addition to
equity.

First, i would have a segment for investor in your chart of accounts.
For this discussion, your chart would have two segments:
1. investor - atleast 3 chars wide, but more would be a good idea
2. account - as much as you want/need (sub-accounts lumped in here for this
discussion.)
You should start your investor numbering from say 101, numbering each
investor consecutively.
Uncategorized income/expenses would use 000 in the  investor segment.

Now, you can create an SQL View that looks like this
[InvestorID], [Sum of Income accounts], [Sum of Expense Accounts] ,
InvestorPercentage =  [Sum of Income accounts]/[Sum of Expense Accounts]
(Group by investor)

You can treat this view as a table,  so you build another view on it that
returns
sum([Sum of Income accounts]), sum([Sum of Expense Accounts]) to return the
denominator, which is the sum of all income and expenses.

and finally you can create another view that joins the first two:
[InvestorID], sum([Sum of Income accounts]) * InvestorPercentage, sum([Sum
of Expense Accounts]) * InvestorPercentage

I have tried to keep this a little abstract because I did not want to bore
you with SQL statements.  You can get fancier and create User Defined
functions or Stored Procedures tht might fill your need even more.

Because views are dynamic, it will automatically adjust itself as you add
new account of any type - no need to recast anything even as you add or lose
partners or expense accounts.

Regards
HS

"Leslie Vail" <LeslieVail@discussions.microsoft.com> wrote in message
news:AD0A2EBE-8F91-45E4-82D8-A97CF6EC3EBF@microsoft.com...
> Hi,
>
> Can you give me a simple example using numbers?  I'm staring at your
formula
> and it looks like you already have to know the percentage in order to know
> the numerator, and you're using the numerator to come up with the
percentage.
>  You can use any number of SQL query tools to get the data directly from
the
> tables.  I do alot of work with Crystal reports as an example.
>
> I've never been much good at simulteneous equations - is that what you are
> trying to figure?
>
> Leslie
>
> "Christopher D. Wiederspan" wrote:
>
> > I apologize for asking an "operational" question, but I'm trying to get
a
> > running start without much Great Plains experience.
> >
> > I'm testing out Great Plains for an implementation for an investment
firm.
> > There are appoximately 400 invested partners in the fund, so I've set up
> > about 38 Income and Expense Accounts as Fixed Allocation accounts, with
each
> > of these "parent" accounts allocating to 400 (investor) "child" accounts
> > (esentially I want to divide up the income and expense between the 400
> > partners).
> >
> > My allocation is based on the sum of their cumulative Equity, and their
> > cumulative earned Income/Expense. Because I want to Allocate Income (and
> > Expense) accounts based, in part, on the same Income Account, it creates
a
> > type of circular reference and Great Plains won't let me do it - which
is
> > fine. Instead, I've just setup the 38 "parent" accounts as fixed
allocation
> > using hard-coded percentages and everything works fine. The problem is
this:
> > occasionally one of the partners will up their Equity stake and I need
to
> > change the 400 percentages in each of 38 accounts - not something that
can
> > be done by hand.
> >
> > With that said, I'm looking for a recommendation on the best way to
"query"
> > Great Plains to sum cumulative totals for each investor, something like
this
> > InvestorPercentage = (InvestorEquity + InvestorIncome - InvestorExpense)
/
> > (EverybodyEquity + EverbodyIncome - EverybodyExpense). My plan of action
is
> > that if I can get a "query" going, then I'll use the eConnect XML
features
> > to simply rewrite the allocation percentages. So my biggest hurdle at
this
> > point is figuring out how to come up with the percentages to use - I
don't
> > know if there is some way to directly query the tables from SQL, or if
there
> > is some eConnect feature that I can use to run a "report"?
> >
> > Any help or pointers would be greatly appreciated.
> >
> > Thanks,
> > Chris
> >
> >
> >


0
HSalim (1270)
10/15/2004 1:14:11 PM
Thanks - that's exactly what I'm hoping to do. Can you give me some idea of
the actual SQL mechanics (I'm an SQL developer, so I don't need syntax, but
I do need to know which tables, etc. to look in to create the view that you
describe). If nothing else, maybe you could simply point me to a point in
the documentation that might set me on my way...? Thanks, again!

Thanks,
Chris

P.S. We have already created our chart of accounts almost exactly like you
described - sounds like we're barking up the right tree.



"HSalim" <HSalim@msn.com> wrote in message
news:epBSGjrsEHA.2788@TK2MSFTNGP09.phx.gbl...
> Chris,
>
> Just to clarify, what you are trying to do is to allocalte  operational
(or
> uncategorized ) income and expenses, right?  From your formula, it appears
> tht each investor already has income and expense accounts in addition to
> equity.
>
> First, i would have a segment for investor in your chart of accounts.
> For this discussion, your chart would have two segments:
> 1. investor - atleast 3 chars wide, but more would be a good idea
> 2. account - as much as you want/need (sub-accounts lumped in here for
this
> discussion.)
> You should start your investor numbering from say 101, numbering each
> investor consecutively.
> Uncategorized income/expenses would use 000 in the  investor segment.
>
> Now, you can create an SQL View that looks like this
> [InvestorID], [Sum of Income accounts], [Sum of Expense Accounts] ,
> InvestorPercentage =  [Sum of Income accounts]/[Sum of Expense Accounts]
> (Group by investor)
>
> You can treat this view as a table,  so you build another view on it that
> returns
> sum([Sum of Income accounts]), sum([Sum of Expense Accounts]) to return
the
> denominator, which is the sum of all income and expenses.
>
> and finally you can create another view that joins the first two:
> [InvestorID], sum([Sum of Income accounts]) * InvestorPercentage, sum([Sum
> of Expense Accounts]) * InvestorPercentage
>
> I have tried to keep this a little abstract because I did not want to bore
> you with SQL statements.  You can get fancier and create User Defined
> functions or Stored Procedures tht might fill your need even more.
>
> Because views are dynamic, it will automatically adjust itself as you add
> new account of any type - no need to recast anything even as you add or
lose
> partners or expense accounts.
>
> Regards
> HS
>
> "Leslie Vail" <LeslieVail@discussions.microsoft.com> wrote in message
> news:AD0A2EBE-8F91-45E4-82D8-A97CF6EC3EBF@microsoft.com...
> > Hi,
> >
> > Can you give me a simple example using numbers?  I'm staring at your
> formula
> > and it looks like you already have to know the percentage in order to
know
> > the numerator, and you're using the numerator to come up with the
> percentage.
> >  You can use any number of SQL query tools to get the data directly from
> the
> > tables.  I do alot of work with Crystal reports as an example.
> >
> > I've never been much good at simulteneous equations - is that what you
are
> > trying to figure?
> >
> > Leslie
> >
> > "Christopher D. Wiederspan" wrote:
> >
> > > I apologize for asking an "operational" question, but I'm trying to
get
> a
> > > running start without much Great Plains experience.
> > >
> > > I'm testing out Great Plains for an implementation for an investment
> firm.
> > > There are appoximately 400 invested partners in the fund, so I've set
up
> > > about 38 Income and Expense Accounts as Fixed Allocation accounts,
with
> each
> > > of these "parent" accounts allocating to 400 (investor) "child"
accounts
> > > (esentially I want to divide up the income and expense between the 400
> > > partners).
> > >
> > > My allocation is based on the sum of their cumulative Equity, and
their
> > > cumulative earned Income/Expense. Because I want to Allocate Income
(and
> > > Expense) accounts based, in part, on the same Income Account, it
creates
> a
> > > type of circular reference and Great Plains won't let me do it - which
> is
> > > fine. Instead, I've just setup the 38 "parent" accounts as fixed
> allocation
> > > using hard-coded percentages and everything works fine. The problem is
> this:
> > > occasionally one of the partners will up their Equity stake and I need
> to
> > > change the 400 percentages in each of 38 accounts - not something that
> can
> > > be done by hand.
> > >
> > > With that said, I'm looking for a recommendation on the best way to
> "query"
> > > Great Plains to sum cumulative totals for each investor, something
like
> this
> > > InvestorPercentage = (InvestorEquity + InvestorIncome -
InvestorExpense)
> /
> > > (EverybodyEquity + EverbodyIncome - EverybodyExpense). My plan of
action
> is
> > > that if I can get a "query" going, then I'll use the eConnect XML
> features
> > > to simply rewrite the allocation percentages. So my biggest hurdle at
> this
> > > point is figuring out how to come up with the percentages to use - I
> don't
> > > know if there is some way to directly query the tables from SQL, or if
> there
> > > is some eConnect feature that I can use to run a "report"?
> > >
> > > Any help or pointers would be greatly appreciated.
> > >
> > > Thanks,
> > > Chris
> > >
> > >
> > >
>
>


0
10/15/2004 2:25:54 PM
Chris,
 Tools > Resource Descriptions> Tables is a good place to start.
I would think that you would need only the following tables:
GL20000 GL Year To Date Transaction Open
GL00100 - Accounts Master

Regards
HS


"Christopher D. Wiederspan" <wiederspan@reachdigital.com> wrote in message
news:Oej9nKssEHA.3200@TK2MSFTNGP14.phx.gbl...
> Thanks - that's exactly what I'm hoping to do. Can you give me some idea
of
> the actual SQL mechanics (I'm an SQL developer, so I don't need syntax,
but
> I do need to know which tables, etc. to look in to create the view that
you
> describe). If nothing else, maybe you could simply point me to a point in
> the documentation that might set me on my way...? Thanks, again!
>
> Thanks,
> Chris
>
> P.S. We have already created our chart of accounts almost exactly like you
> described - sounds like we're barking up the right tree.
>
>
>
> "HSalim" <HSalim@msn.com> wrote in message
> news:epBSGjrsEHA.2788@TK2MSFTNGP09.phx.gbl...
> > Chris,
> >
> > Just to clarify, what you are trying to do is to allocalte  operational
> (or
> > uncategorized ) income and expenses, right?  From your formula, it
appears
> > tht each investor already has income and expense accounts in addition to
> > equity.
> >
> > First, i would have a segment for investor in your chart of accounts.
> > For this discussion, your chart would have two segments:
> > 1. investor - atleast 3 chars wide, but more would be a good idea
> > 2. account - as much as you want/need (sub-accounts lumped in here for
> this
> > discussion.)
> > You should start your investor numbering from say 101, numbering each
> > investor consecutively.
> > Uncategorized income/expenses would use 000 in the  investor segment.
> >
> > Now, you can create an SQL View that looks like this
> > [InvestorID], [Sum of Income accounts], [Sum of Expense Accounts] ,
> > InvestorPercentage =  [Sum of Income accounts]/[Sum of Expense Accounts]
> > (Group by investor)
> >
> > You can treat this view as a table,  so you build another view on it
that
> > returns
> > sum([Sum of Income accounts]), sum([Sum of Expense Accounts]) to return
> the
> > denominator, which is the sum of all income and expenses.
> >
> > and finally you can create another view that joins the first two:
> > [InvestorID], sum([Sum of Income accounts]) * InvestorPercentage,
sum([Sum
> > of Expense Accounts]) * InvestorPercentage
> >
> > I have tried to keep this a little abstract because I did not want to
bore
> > you with SQL statements.  You can get fancier and create User Defined
> > functions or Stored Procedures tht might fill your need even more.
> >
> > Because views are dynamic, it will automatically adjust itself as you
add
> > new account of any type - no need to recast anything even as you add or
> lose
> > partners or expense accounts.
> >
> > Regards
> > HS
> >
> > "Leslie Vail" <LeslieVail@discussions.microsoft.com> wrote in message
> > news:AD0A2EBE-8F91-45E4-82D8-A97CF6EC3EBF@microsoft.com...
> > > Hi,
> > >
> > > Can you give me a simple example using numbers?  I'm staring at your
> > formula
> > > and it looks like you already have to know the percentage in order to
> know
> > > the numerator, and you're using the numerator to come up with the
> > percentage.
> > >  You can use any number of SQL query tools to get the data directly
from
> > the
> > > tables.  I do alot of work with Crystal reports as an example.
> > >
> > > I've never been much good at simulteneous equations - is that what you
> are
> > > trying to figure?
> > >
> > > Leslie
> > >
> > > "Christopher D. Wiederspan" wrote:
> > >
> > > > I apologize for asking an "operational" question, but I'm trying to
> get
> > a
> > > > running start without much Great Plains experience.
> > > >
> > > > I'm testing out Great Plains for an implementation for an investment
> > firm.
> > > > There are appoximately 400 invested partners in the fund, so I've
set
> up
> > > > about 38 Income and Expense Accounts as Fixed Allocation accounts,
> with
> > each
> > > > of these "parent" accounts allocating to 400 (investor) "child"
> accounts
> > > > (esentially I want to divide up the income and expense between the
400
> > > > partners).
> > > >
> > > > My allocation is based on the sum of their cumulative Equity, and
> their
> > > > cumulative earned Income/Expense. Because I want to Allocate Income
> (and
> > > > Expense) accounts based, in part, on the same Income Account, it
> creates
> > a
> > > > type of circular reference and Great Plains won't let me do it -
which
> > is
> > > > fine. Instead, I've just setup the 38 "parent" accounts as fixed
> > allocation
> > > > using hard-coded percentages and everything works fine. The problem
is
> > this:
> > > > occasionally one of the partners will up their Equity stake and I
need
> > to
> > > > change the 400 percentages in each of 38 accounts - not something
that
> > can
> > > > be done by hand.
> > > >
> > > > With that said, I'm looking for a recommendation on the best way to
> > "query"
> > > > Great Plains to sum cumulative totals for each investor, something
> like
> > this
> > > > InvestorPercentage = (InvestorEquity + InvestorIncome -
> InvestorExpense)
> > /
> > > > (EverybodyEquity + EverbodyIncome - EverybodyExpense). My plan of
> action
> > is
> > > > that if I can get a "query" going, then I'll use the eConnect XML
> > features
> > > > to simply rewrite the allocation percentages. So my biggest hurdle
at
> > this
> > > > point is figuring out how to come up with the percentages to use - I
> > don't
> > > > know if there is some way to directly query the tables from SQL, or
if
> > there
> > > > is some eConnect feature that I can use to run a "report"?
> > > >
> > > > Any help or pointers would be greatly appreciated.
> > > >
> > > > Thanks,
> > > > Chris
> > > >
> > > >
> > > >
> >
> >
>
>


0
HSalim (1270)
10/15/2004 5:21:39 PM
Reply:

Similar Artilces:

Great Plains should allow setting an inactivity timeout
When anyone logged into Great Plains has been inactive for a prespecified period (e.g. 30 minutes) they should be automatically logged out of the application. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGrou...

Append Query 02-28-08
I am trying to append data from tblitemline to tblitemlist. I only want to append new records. The unique field is ListID. I want to append 4 fields total. I can't get it to limit it to new records. Any help would be appreciated. Thanks! New on the basis of what criteria.? Whatever that criteria is it should either the primary key (there can be multiples fields involved as the primary key) or Indexed with NO duplicates, (this will work if the criteria is a single field). Once you set the table up that way, the append query will only add "new" records. You can still have a ...

database query/names issue, causing excel to be sluggish
I have an issue with an excel worksheet I've created. WorkbookA has "Names" which are used to define drop-down menus in WorksheetA. These "Names" are defined to reference a WorksheetB (also in WorkbookA) which itself is populated by a database query, pulling from another Excel WorkbookB. The logic is that WorkbookB should not be touched by the User, only an Admin, and Workbook A is used by User. The Names in WorkbookA are to prevent incorrect entries. Now that I've set it up, WorkbookA is moving really sluggish, but if I delete all the Names it speeds up again....

which query is faster, better to use?
There are multiple AdjournmentDates for any given NoticeID in tblNotices_AdjournemntDates. I want the most recent date for each NoticeID. Both of these produce the correct results. Is either one better than the other? SELECT DISTINCT NoticeID, (SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) FROM dbo.tblNotices_AdjournmentDates A SELECT NoticeID, AdjournmentDate FROM dbo.tblNotices_AdjournmentDates A WHERE (AdjournmentDate IN (SELECT TOP 1 AdjournmentDate FROM tblNotices_AdjournmentDates WHER...

Load query with outdata until search
I have a form with sub form on it I use this form to search for products we keep in stock with prices, I have a Search text box on the form then i type in the product then hit the search button and it requerys the sub form Only problem i have is when i first load the form it show every product in the subform, so takes a long time to load Is there a way to load the page with out the date then i can type in product i want to search for then it displays the products Simon What about setting the .RecordSource property of that form (?subform) to "" in the OnLoad event,...

Crystal Web Options and Great Plains
Hi All Crystal Web Options was shipped with Crystal 8.0 for Microsoft Great Plains. I don't see these options available with Crystal 10 or 11. I am currently upgrading a clients Crystal and need to upgrade the web components as well. Is there an upgrade path for the web components so that they can still access their reports via their Intranet and is the software freely available with MSGP? All related comments welcome... Thanks for the assistance ! Cheers Jules ...

query problem 02-11-08
Hi to Everyone I have a table (tblMunka) with the following columns: JobId; PersonalId;AreaId;StartDate;EndDate;ActiveJob I would like to a query which tell me how many personal was in a term in an area. I have a form (frmLetszam) where user can choose start and end dates from a calendar. I've tried with this but the result is not good: HAVING (((tblMunka.StartDate) Between [Forms]![frmLetszam]![cboEndDate] And [Forms]![frmLetszam]![cboEndDate])) OR (((tblMunka.StartDate)<[Forms]! [frmLetszam]![cboStartDate]) AND ((tblMunka.EndDate) Is Not Null)); Please help me! Best regards -- Mes...

Does Great Plains support unicode?
Can I enter unicode from client site and store the unicode on server (sql server) so that I can fetch the unicode late? Thanks, Baolin Thanks for asking the question. The Great Plains product line does not support Unicode or any double byte languages at this time. Currently there are no plans to have it modified to support unicode that I am aware of. Terry Borchardt tborchar@micrsoft.public.greatplains Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. "Baolin Ren" wrote: > Can I enter unicode from client site and s...

Query with Combo Boxes on a Form
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? Any help would...

LDAP Query #4
im sorry if this post is in the wrong group how can i create an ldap query to get all the users with exchange boxes inside a specific OU this OU might also have OUs indise it with users that hafve mailboxes i want this to be able to create customized address lists Thank you Address Lists cannot be targeted at particular OUs. You need to use some user attribute that's common for users in that OU - like department, location, company name, etc. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Nazgyl" <naz...

Creating a Report using two queries
I have created a chart report (bar chart) using one query. How can I create another single chart showing the results of both queries. One will show the bars of first query data and the other will show bars of the data from second query together in a single chart report. Any suggestions? -- FL This depends on how/if the two queries are related. I would expect the "X" axis values might be related. If so, join the two queries into a single query and graph the new query. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www...

Complex Update Query in SQl Server 2000
I need to update ACTINDX in UPR40500 for each JOBTITLE in MYTABLE. We have modified the ACTNUMBR in 'MYTABLE' whose segments are contained in GL00100 GL00100 ACTINDX ACTNUMBR_1 ACTNUMBR_2 ACTNUMBR_3 ACTNUMBR_4 ACTNUMBR_5 30562 222 411001 000000 150 350 30563 222 411001 000000 155 355 UPR40500 JOBTITLE ACTINDX 209401 30562 150001 30563 MYTABLE JOBTITLE ACTNUMBR 209401 222-411001-000000-150-350 150001 222-411001-000000-155-355 Sean: If you are storing the account number as a string in my table, an...

Select Query Broken
Does any one know, of the issue in A2k3 With the Query, I was creating some combo list filters, via query's then putting the SQL in a Marco Apply filter. work great then all i sudden, now, when i view the query in normal mode, (NOT Design mode) Its Showing all the fields that are related on the table the query is based on, even though only two fields have been selected. and the show tick is ticked? just for 2, any ideas whats happening here? ive tried repairing and compacting database and also restarting access no luck? On 10 Sep, 16:42, KARL DEWEY <KARLDE...@discussions.microsoft...

Public folders: query regarding the "synchronize now" function
Exchange 2003 SP2 I have a query regarding the "synchronize now" function for public folders. Say I have a public folder called "Bob" with replicas on 3 servers (ServerA, ServerB, ServerC) If I drill down to Folders/Public Folders Select the "BOB" public folder and select the "Status Tab" on say ServerA, I see all 3 replicas listed as expected: If I right click a replica I can select "synchronize now" what does this do exactly? Does it: 1) Push updates out from the replica where you right click and select "synchronize now" ...

Query Button Problem
On my form, I need to put a button to run a query. When using the toolbox in the Design View, I use the Command Button Toolbar, then use MIscellaneous, then Run Query. I select the correct one, and save it to the form. When I try to use it, I get an error message, and it will not work. What is going wrong? Can't figure it out if you don't provide an error message. On Fri, 1 Feb 2008 10:52:00 -0800, Shari <Shari@discussions.microsoft.com> wrote: >On my form, I need to put a button to run a query. When using the toolbox in >the Design View, I use the Command Button To...

criteria for filtering check boxes in a query
I am trying to do a permanent sort for columns using check boxes. I need to know the formula for the criteria box On Wed, 20 Jun 2007 14:54:41 -0700, Desiree wrote: > I am trying to do a permanent sort for columns using check boxes. I need to > know the formula for the criteria box What is "a permanent sort for columns using check boxes". Perhaps your terminology is clear to you, but I think you would do well to re-think and repost your question. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail On Thu, 21 Jun 2007 15:38:03 -0700, Desir...

adding fields in query
I am trying to add fields in a query to put into a new field. For example; FieldOne, FieldTwo, and FieldThree should equal FieldFour. FieldOne has a value of 1, FieldTwo has a value of 2 and FieldThree has a value of 3. When I try =FieldOne+FieldTwo+FieldThree in FieldFour with the expression builder, it does not add them, it just shoves all their values together. Instead of FieldFour having a value of 6 when I run the query, it gives me a value of 123. I have also tried the "&" instead of "+", and I get the same results. I have tried changing the To...

stop returning column headings from DB query
I put together a query to go against Oracle that returns a block of data to the spreadsheet. Is there an option somewhere to have the "Return Data to Excel" do so without also returning the column headings? I want the data only. thanks - Russ There are 2 places that you can change that setting..... 1)When you return data from MS Query the dialog where you select the location for the data has a Properties button. Click that button UNcheck: Include Field Names.......click [OK] Continue selecting the query destination OR 2)After the data has been pulled from Oracle Right-click...

Great Plains service pack
We are on Great Plains 8, no service pack. We want to apply Service Pack 4. My question is whether any service pack will need to be applied to the Business Portal or PDK as we are applying the service pack to Great Plains. We would like to avoid applying any service packs to PDK or Business Portal at this point. We use Business Portal for Project Accounting. You willl not need to apply a SP to Business Portal, but I would recommed applying the service pack to PDK. If you are worried about what the SP would do to your current system, try applying the SP in a test environment. Once y...

Tranform Pivot Query?
I hv 2 tables with structure as below: [Empl] ID Name 1 Mr A 2 Mr B 3 Mr C 4 Mr D 5 Mr E [Empl_Tran] TrID Empl_ID Date Code 1 1 10/11/2009 SL 2 1 10/11/2009 TR 3 1 11/11/2009 W 4 1 12/11/2009 W 5 1 12/11/2009 TR 6 2 10/11/2009 SL 7 2 10/11/2009 TR 8 2 11/11/2009 W 9 2 12/11/2009 W 10 2 12/11/2009 TR 11 3 10/11/2009 SL 12 3 10/11...

Web query again
Hello, I looked on MSDN about web queries with VBA. But the code I took from there doesn't work. Dim BaseURL As String BaseURL = "some URL" Const StartDate As Date = #11/2/2004#, EndDate As Date = #4/20/2005# Dim d As Date For d = StartDate To EndDate Dim newSheet As New Excel.Worksheet Set newSheet = Worksheets.Add With newSheet .Name = Replace(CStr(d), "/", ".") Call .Activate End With 'MsgBox ("URL;" & BaseURL) 'Next line gives an error 'Run-time error '5': 'Invalid ...

emailing query window type result messages
I'm creating some backup procedures. While testing them in the query window, I'll get messages like this: 100 percent processed. Processed 0 pages for database 'Caser', file 'Caser_Log' on file 1. BACKUP LOG successfully processed 0 pages in 0.097 seconds (0.000 MB/sec). The backup set on file 1 is valid. I knonw how to do this: BACKUP LOG [Caser] TO DISK = @BackupLocationAndName WITH NOFORMAT, NOINIT, NAME = @BackupName, SKIP, REWIND, NOUNLOAD, STATS = 10 SET @Err = @@ERROR IF @Err <> 0 BEGIN EXECUTE msdb.dbo.sp_notify_operator @name = ...

Outbound attachments to plain text ?
Ladies and Gentlemen I am trying to set our exchange 2003 server to send outbound mail as plain text when sent to a specific domain. This was available in exchange 5.5 in the internet mail service properties "internet mail" tab and click on e- mail domain. Does anybody know where this setting is located in 2003 ? Thanks sushi "sushi" <anonymous@discussions.microsoft.com> wrote: >Ladies and Gentlemen >I am trying to set our exchange 2003 server to send >outbound mail as plain text when sent to a specific >domain. This was available in exchange 5.5 ...

data base query
I have a workbook with 2 work sheets. Eacch contains data from a database query( 2 different databases) Is it possible to create a pivot table from the 2 sheets ? ...

Exporting a Query to Excel
Hi, I have a query of Purchase Oders, sorted by month, for a particular business unit and was wondering if there was a way to export the data to one excel workbook, but to have 12 tabs of monthly data instead of having to separate it out manually. thanks -- C. Williamson Here is some code that I wrote for a poster a few months ago that will do this type of export. You'll need to modify the code so that it will loop through a list of months instead of a list of managers. Post back with questions (and with more details) if you have problems. Generic code to create a temporary qu...