Odd Parameter request in Crosstab
I read the threads about creating parameters in crosstab queries and this
helped me to get it to work but the parameter box pops up when I try to save
the query. Though my changes are saved, I don’t think this is normal? It
happens in all views. Can anyone tell me what might be wrong? Here is my
PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
WHERE...Error 'Invalid object ##Options' while running Multilevel query in
I created a company in GP9.0 with Analytical Accounting + Service Pack 1,
created a transaction dimension and some codes, posted transactions in POP,
GL and Inventory.
When I ran the Multi level query wizard, i got the error '[Microsoft][ODBC
SQL Server Driver][SQL Server]Invalid object name "##Options" when I gave all
the values and the system started to run the report.
What could be the problem?
Thanks - Murali
The options you are selected in MQW are stored in temp table and using that
"##Options" table is created in run time.It might be because the temp ta...Creating Date Range in Pivot Microsoft Query
I have a pivot that I've connected to our SQL db using the MS Query. I'm
having two problems with it:
1. In Access I create a date range to see dates between 3 and 15months ago
with the SQL statement "
WHERE (Trx.TrxDate) Between (Date()-485) AND (Date()-120)"
- no problem in Access but not the MS Query program. When I do it through
the Excel MS Query it says the Function 'date' is not valid. How do I
describe the current date in the MS Query program?
2. In the same wizard I cannot combine two fields (in Access I would use
Expr1: [Field1]&", "...Freezing Panes for both Rows and Columns
Is it possible to freeze desired Rows and Columns at the same time? I've
tried it, but once either the Row or Column is freezed, the other cannot
be done as when right-clicked it shows unfreeze.
MetalUrge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36975
View this thread: http://www.excelforum.com/showthread.php?threadid=566960
Yes! For example, if you select cell B2 and Windows/Freeze Panes, the first
row and the first column will be frozen.
Hope this helps.
...Union queries and crosstab...
I created a union query that combined three tables. Three tables that store
three types of deposits (ID, Coupon, Misc). The union query has a criteria
of a date range. The fields include SalesAmt for all of the deposit types.
If one of the tables do not have data for that date range (let's say Misc),
the SalesAmt column for the Misc table does not show up in the query. Is
there a way for me to have a filler column so it can be displayed in a
report? Currently, if a table has no data, the report errors out saying the
column does not exist.
Use a NZ function on that fie...Sorting data in a query
I have a report based on a query that returns employees holiday dates in
This works well but new boss only wants to see the last holiday that
staff had. I have tried various methods of trying to return the LAST
HOLIDAY DATE FOR EMPLOYEES but I can not achieve it.
Could anyone help please?
Current query example returns:
Mr Smith 21 Oct 07
Mr A 1 OCT 07
mRS b 28 Sep 07
Mrs b 1 Sep 07
Mr z 29 Aug 07
Mr Smith 28 Aug 07
Mr A 20 Aug 07
Miss Y 1 Aug 07
The new query needs to return:
Mr Smith 21 Oct 07
Mr A 1 OCT 07
mRS b 28 Sep 07
Mr z 29 Aug 07
Miss Y ...Counting rows in a column
How do I add up the "not blank" cells in a column? I just want to know how
many rows are in a column.
In article <56507A79-5EA9-41D4-84E0-6CC26F3CD1EF@microsoft.com>,
Mar <Mar@discussions.microsoft.com> wrote:
> How do I add up the "not blank" cells in a column? I just want to know how
> many rows are in a column.
Gord Dibben Excel MVP
On Mon, 5 Dec 2005 13:11:03 -0800, Mar <Mar@discussions.microsoft.com> wrote:
>How do I add up the "not blank" cells in a column? I just want to know h...a hurry question
I build up a SID. and base on CHtmlView.i draw CSplitterWnd,and draw to htmlview on it. two htmlview load to html
now i want when one html roll then another roll synchronous,how to do
1.how to capture the message "scroll the page ", i set a message function to deal WM_HSCROLL and WM_VSCROLL, but it dones't work
2.how to set the position of the page in the htmlview
please give me code as possable as you ca
...Trouble with Query Statement
I keep getting the following error with the code below: runtime error
'13'; Type mismatch.
Set rst = CurrentDb.OpenRecordset("SELECT tblCaseInfo.CaseID,
tblCaseInfo.DHSNo, tblCaseInfo.Region, tblCaseInfo.DHSAttny,
tblCaseInfo.CaseName, " _
& "tblAction.CaseID, tblAction.Actn, tblStatus.CaseId,
tblStatus.ClosedDate, tblStatus.StatusRptNotes " _
& "FROM (tblCaseInfo INNER JOIN tblAction ON tblCaseInfo.CaseId =
tblAction.CaseID) " _
& "LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId "
& "WHERE ((...exporting and archiving question
I have a couple of problem transactions that I cannot
edit. Salvage/repair not working, no restorable backup. I
do have a backup file from May of this year, I was
wondering if there is any way to export the transactions
from may thru july? When I tried an export, it exported
everything and didn't ask for a date. I suppose I could
make a copy of the file and delete everything else, and
Other thing I tried was to archive my file, getting rid of
the problem records. I specified the date to archive
before, but EVERYTHING is still in my archive file. Is
this just ...Query to Determine if Course is Taken
Hi, I have a table that lists courses an employee has taken. I also have a
list of courses, when then expire and what their retraining course should be.
Now I need to determine if the employee has taken not only the original
course, but the retrained course when they're on separate lines.
So.. if he's taken course 123 which expires in 12 months. Then he takes
course 124. It shows in the list of courses that he's taken that 124 was also
completed. However, my flag just determines that 123 is expired. I need that
flag to not show if 124 has been taken.
Hope that's a compl...Date parameters on crosstab report?
I am reporting on orders analysed by advertising source.
I have built my first crosstab query that works fine, and then a report
based on that which also works OK.
However, we need to select the date range (order date) for which it is
In the past with ordinary select queries I have simply added Between [Enter
start date] And [Enter end date] to the appropriate date field column in the
design grid and it has worked fine.
But on this one it makes no difference - it either continues to display the
whole set of data, or nothing at all, depending on the syntax I use.
Is there som...Public folder questions
I've got a couple of questions:
1. Is there a way to export a listing of what folders exist and which users
have what role for each folder?
2. Is there an 'ExMerge' type utility to pull the postings from Public
Folders into something like a .pst file?
Thanks for any help.
As far as item 1, check out this post on the MS Exchange Blog. It discusses
the various public folders tools MS has.
Windows Server MVP
"...Query Criteria 04-15-10
Some of the fields I have in a table which is imported from a csv file are:
Salesperson (has up to 20 names in it)
Status (has Won,Lost,Submitted,Prospect,Did Not Proceed,Estimation)
Status Date (has date)
Value ($ value)
What I am getting stuck on is a query to get the total value per Salesman
for each status and also the count of each status, I would like it to show
in datasheet form something like below.
Salesman $won No. Won $lost No.Lost
A $100000 5 $150000 3
B ...Excel query #5
I'm using Microsoft Query to get data from my Access tables. After the 64th
query, I get the message "[Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed", which means I can't add another query. I need
several more queries. Each query has 53 fields. Can I increase the number of
queries the driver can open some way? If not can, pivot tables or OLAP cubes
open more queries. I have played with them some. Can they open that many
fields? Are pivot tables or OLAP cubes the best way to handle the data?
I'm not looking for a 'how to' ans...Queries to Views?
I posted this question in a SQL Svr newsgroup and was basically slapped down
for doing so. I was told this is an "Access" issue and I should try an
"Access" group (man, those guys can be sensitive!). So, here we go...
I've tried a few different approaches but, each time I try to convert an
access query to a view in SQL Svr 2000, the result is a table not a view.
Does anyone know how to take the dozens of queries I've created and move them
into SQL server as views without copying and pasting the SQL statements into
query analyzer one at a time?
I've ...Templates and Queries
I have an Excel Template that contains a query pulling from and Access
Database. I have tried to modify this query, however, it doesn't seem to
accept my changes. I can create a new query within a new template and then
it works fine. Rather than doing this for everyone, I would like to just
modify the query and save that within the template. I've tried saving the
query and then resaving the template also. What am I doing wrong. This is
...Network connection for query
G'day there One and All,
I'm currently trying to adapt a few of the Dept's workbooks to suit our
district. There is a single master book, to produce reports, that
is available to all on a read only basis and read write to the
relevent manager. There are also a number of data entry books that are
identical to each other and that provide the data from each division.
The master book uses a query to obtain data from the division's books
and thereby build the reports. The data books are read write for
everyone with only a single sheet from each providing data for the
If I run
Set olfolder = olapp.GetNamespace("MAPI").PickFolder
and select a folder, the TypeName is always "MAPIFolder". Is there a
property or method to determine if it's an inbox item and not a contact
or journal item?
See the object browser (f2) for what properties are available for a
MapiFolder object. The one you're looking for is DefaultItemType.
Michael Bauer - MVP Outlook
Manage and share your categories:
Am Mon, 22 Feb 2010 16:32:34 -0800 schrieb ...Query to find when a total is hit
We are trying to get some statistics on our Inventory people to see if they
are issuing material to work orders on time. My work order table has a
field that shows the last transaction date of the order, but if Inventory
issue all the material then has to issue more because of scrap later they
get dinged as being late.
We have a Transaction History table that shows every issue however I don't
know a good way to find out when the total number of needed parts was
reached. For Example I am creating a shop order that needs 3 parts, 50 of
ShopOrder PartNeeded D...LDAP Query needed
I need an ldap query to find all users with an exchange mailbox where the
users alias does not equal the nickname of the smtp email address(s). Thanks!
"MrRAlan" <MrRAlan@discussions.microsoft.com> wrote:
>I need an ldap query to find all users with an exchange mailbox where the
>users alias does not equal the nickname of the smtp email address(s). Thanks!
I don't believe LDAP supports what you want to do. You'll have to
write a small program (vbs or perl) that creates a mail address and
then queries the AD for a match.
MCSE+I, Exchange ...Queries?
Is it possible to make an "update query" to roll back year to date overtime
hours? I have an tblhours that totals all overtime hours worked and refused
in which I have ran queries to sum all of those. Once a year we have to
rollback the year to date overtime hours which means we take the lowest
employee, he goes to "0" and we subtract the lowest employees hours from the
highest employees hours. I was trying to do this in an update query, but
everything I try subtracts, for example, 10 hours from every line of
overtime in the tblhours table. I know you are no...Center a horizontal line in a newsletter column
How do I center a horizontal line in a column of a newsletter? I've set up a
newsletter with 2 columns. In between the text boxes I would like to put a
short horizontal line which is centered in the column.
Draw it in.
MVP Microsoft [Publisher]
"Legvidal" <Legvidal@discussions.microsoft.com> wrote in message
> How do I center a horizontal line in a column of a newsletter? I've set up
> newsletter with 2 columns. In between the text boxes I would like to put a
> short hor...Convert a Column of Data into multiple rows
I am trying to convert a column of data into multiple rows but cant
seem to figure out how to do it.
I want this column to be broken down into rows of 3 items
a b c
d e f
g h i
Thanks in advance
1.Fill a B column with numbers in order, start from number 3 ->
3,4,5,6,7,8... and so on
2.In cell C1 add =if(MOD(B1;3)<>0;"";A1) - drag command in column C to the
end of data
3.In cell D1 add =if(MOD(B1;3)<>0;"";A2) - drag command in column D to the
end of data
4.In cell E1 add =if(MOD(B1;3)<>0;"";A3)- drag command in ...Excel VBA SQL Query Filename
I recorded a macro to pull in an external dbf file using a SQL query
in VBA (Excel 2003 on an XP machine).
Everything works great until I point it at the original file (was
pointing at a renamed copy) that I want to pull in. The files I need
to pull in will have the format <output filename>~<model 1>~<model
The SQL query doesn't like the strange characters (~'s) in the
filename and if I try a wildcard within the filename (i.e. <output
filename>*<sub model> or <output filename>%<sub model>) it doesn't