Crosstab with subquery as source to another query
I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects ...Calculating a Date
I have two fields in a query: "Date Completed" (mm/dd/yyyy) and "Time Used
(hours)" (nn.n). I am trying to calculate a third "Date Started".
Assuming an eight-hour day, I am trying to use the expression [Date
Completed] - [Time Used (hours)]/8 but I can't figure out which Date/Time
functions to use to convert my dates and hours to the same units to do the
calculation and then back to a date again.
I am using Access 2007 and am not all that competent with it. Thank you in
advance for your help!
You can try the following.
DateValue(DateAd...Saving Queries from Address Book Views
Operating System: Mac OS X 10.6 (Snow Leopard)
Email Client: Exchange
I am working on a test machine with at least 40 custom search queries in Address Book View for the current profile; I would like to get a copy of 40 custom queries and apply to another system without having to re-write it again. <br><br>Anyway to export queries from Address Book Views? <br><br>OR <br><br>Where is the location that keeps these queries? Prefs? Hoping if copy and paste query file on other machine would work. <br><br>Thanks i...tagging rows to columns???
I just created an excel catalog for my cd jukebox as I loaded the tray (300
discs). Now I want to alphabetize column A (artist) and have column B
(album title) follow column A. Is there a way to do this?
Thanks for all help.
Try on a duplicate copy of your sheet:
Assuming your lists are in cols A and B,
data from row2 down, viz.:
Select cols A and B
Click Data > Sort
Check "Header row" under "My list has"
under "Sort by" are: Artist > Ascending
For the sample data ab...Trying to calculate average call length
Usually our phone system spits out a report for me, but it's broken right
now... and I have to provide this info to another department ASAP.
I'm trying to calculate the average call length for our call center agents.
My spreadsheet looks like this:
The cells are custom formatted as hh:mm:ss. All I want to know is the
average length for those four calls. But when I try the AVERAGE function, I
get a DIV/0 error. Any ideas on what I'm doing wrong? Thanks in advance!
> Usually our phone ...Exporting outlook data from a different harddrive
My motherboard has died so I will need to re-install windows with a
new motherboard. I have a backup of my old drive, which includes my
outlook 2000 directory. Once outlook is installed on the new drive, how
can copy the old data in the new installation? Normally I would export
as a pst, but since the directory is on a different hard disk, I'm not
sure I will be able to actually run outlook on that drive to do the
Any ideas or suggestions?
Thanks in advance
Thanks a lot!
That makes things much simpler
Normally, you should never export to a PST to back up data.
Just c...Outlook time stamp different from OS
I have a user who's outlook time stamp is out of sync with
the OS time stamp. This occured at the daylight savings
time switch over. Does anyone know how to resolve this?
does outlook, windows, and the server all have the same time zone
configuration, including the DST setting?
Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide)
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com
"HLi CS" <email@example.com...Calculation Help Needed
A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs),
and the shifts repeat after
they complete. Shift 1 to 4 is Monday to Thursday, so the following
Shift 1 to 4 is Friday to Monday and this continues on a 4-day cycle.
If an employee works only on shift2, how do you calculate the number
from shift3 until his scheduled shift2.
On Jul 27, 11:37=A0am, "Greg (code...@gmail.com)" <code...@gmail.com>
> A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs),
> and the shifts repeat after
> they complete. Shift 1 to 4 ...Inventory Turnover Report Calculation Seems Incorrect...
We are running GP version 9.0 and I noticed the results from the inventory
turnover report are incorrect. Regardless of what item I run the report for
the results are including in the Qty sold YTD sales from Dec06. This is
causing the turnover # to be incorrect also. Is this report including Dec06
sales in error because the Dec06 ending inventory #'s need to be used in the
calculation of the 2007 turns?
When did you do your inventory close at the end of 2006? It sounds like it
might have been done early in December rather than at the end of the month.
Richard L. Whaley
Auth...Extract different strings from a cell?
If I have "100BDDDABAABD" in a cell and I'd like to extract the last
10 characters and put each character into a different cells. Then, the
first rest of characters (first 3 in this case) into another cell.
How do I do that in Excel?
Since you said "first 3 in this case", I'll assume that the number of
characters will vary in each cell.
With data in A1, enter this in B1, and copy across to K1:
Then, in L1, enter this for the remaining characters:
-----------------------------------...Insert Text From Different Cell
I would like to enter a company name, such as "ABC Co" in a cell o
sheet1 and then on sheet2 have a cell with text "Proposal for ABC Co".
I would also like to be wildly rich. Can anyone help with either o
Joradi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2686
View this thread: http://www.excelforum.com/showthread.php?threadid=40105
Lets say you manually enter in Sheet1, cell A1
In sheet2, cell A1, enter
="Proposal for " & Sheet1...Query Date Range
How can I use Between/And to prompt for date range that includes the start
and end date?
Design a select query for your table.
add all fields to the design grid.
In criteria box of the date field write this
Between #BeginningDate# And #EndingDate#
and run the query to find results.
> How can I use Between/And to prompt for date range that includes the start
> and end date?
My current criteria is in a query date field and is written
Between [Enter start date] And [Enter end date]
but when I run the query from the first day of the month to the...Running Windows Media Player from MFC Application Query?
I am making an application in which i displaying in a list control all the
mp3 files belonging to a folder selected by the user.
I want to add the following functionality.
As soon as the user double clicks on any of the displayed MP3 file, i want
to invoke the windows Media Player and play that MP3 file.
Now how i invoke the Windows Media Player for my MFC Application?
Waiting for suggestions
On two workbooks in a worksheet I use I am having a problem with some rows shrinking to where i can't see them. They are different rows in each workbook. (4,5) and (6,12,13,17). I don't have anything set to hide. Everytime i manually set the row height and save the file as soon as I open it the rows have shrunk again. Is there a setting I am missing.
I've seen a few posts that complain about columnwidth changing (usually in some
version of xl97), but never a post about the rowheight changing.
Any chance you have a macro running that changes the rowheights?
Try opening excel ...default row height and column widths
I need to paste alarge amount of text into a cell without affecting the width
of column or height of row
I couldn't get my copy of Excel (2003) to do otherwise, without choosing
Format> Cells> Alignment> Wrap text
Format> Column> AutoFit Selection
AFTER pasting text into the cell. Are you saying this is not your experience?
PS: posting your e-mail address in a public forum is a good way to get both
spam and viruses sent to it.
> I need to paste alarge amount of text into a cell without affecting ...Find Duplicates Query
I have a Find Duplicates Query based on the "Name Field". Some sample
results are below.
John Doe 12/3/2007
John Doe 12/15/2007
John Doe 1/28/2008
Is there a way to further restrict results to only return results that the
date fields are within 30 days of other? So in the above example, the bottom
result would not be returned because it is not within 30 days of any other
Any help is greatly appreciated.
Use a subquery to identify whether there is another record within 30 days.
This kind of thing:
SELECT ID, [TheName], [TheDate]
FROM ..."default" calculation
A1= sub-total 1
A2= sub-total 2
A3=total; that is, sum(A1,A2)
.....but in case I want to manually enter A3 (i.e. because
there is another additional cost for some reason), I want
the user to have that option.
However, if the user presses delete on A3, or starts
typing something but then backspaces and presses return
but then does not enter any new data (perhaps because they
realize that they just want to use the formula), rather
than have the formula deleted and being left with a
completely blank cell, i woud like the formula to
recaculate and have that calculated value appear in A3.
...Vlookup next row
I am attempting to use vlookup with values that have many decimal places.
The problem is they are close enough for an exact match so I must use an
approximate. I always get a value that is one before the value I wish to
have because it takes a value lesser than the one I am trying to match. Is
there a way I can get it to take the value in the next row? The numbers are
in numerical order.
In article <4363DF28-A994-4D9A-9C84-3E3AA7BC6800@microsoft.com>,
"Mark" <Mar...How Calculate How Many Toolbars Will Fit On a Frame "Row"
I have several toolbars that I'd like to display on as few "frame rows" as
possible. Since the User may change display resolution I can't hardcode
which toolbars should be displayed on a row and I therefore need to
calculate how many will fit at runtime. To do this I get the size in screen
units of the Frame that holds the toobars and then, for each toolbar, I get
the size (again in screen units) of the toolbar and use that to calculate
how much "space is remaining" on the row. If there's enough room left I add
the toolbar to the row and if not I star...Two identical formulas for a different result?
Hello everyone, :)
A very quick question, but nonetheless very intriguing to me (I jus
lost 4h on that :( )
I don't understand why my first formula works, and the second and thir
(result is #value)
(this one could work too I thought, but result in a 0)
I am simply trying to sum (amount) when the fiscal year is 2003 an
IncomeFeeId is RB......Mailbox in a different AD forest
We've just created a second Active Directory forest "domain3" (win2k3) on
our network and setup a 2-way trust with our exisiting domain
"domain2.domain1" which contains an Exchange 2003 server.
Can I setup mailboxes for the domain3 users in the domain2.domain1 Exchange
Ideally I do not want to create disabled accounts in domain2.domain1and then
associate their mailboxes with accounts in domain3, or deploy an Exchange
server in domain3. I just want to host the domain3/user mailbox on the
domain2.domain1 Exch2k3 server. So far I haven't read an...How do i select different cells to be use in a formula.
I need to know how to select different cells to be used in a formula, but i
don't know what is the separator that i need to use to this, see the example
Cells that i need to include on my formula:
you didn't mention what formula but here's a starting point:
or depending on your regional settings
hope this helps
"Manuel" <Manuel@discussions.microsoft.com> wrote in message
> I need to know ho...Product key for different language
Just ordered a second copy of Office 2004 for Mac and with the shipment
confirmation of the shop I have noticed that I have ordered the wrong
language version and not the English one. As I already have a copy of
the English Office 2004 I was wondering if it is possible to use the
product key from the new non-english version for an installation from
the english cd. In other words: can I use (technically and legally) the
license which I have bought now to install a second copy of the english
version or do I have to return the non-english version to swap it for
the english one? I would ra...won't calculate far enough
I have a formula (see below) that will work for rows 2-1000, but once I
change it to look for columns beyond 2000, it will not work anymore... Any
=SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
This DOESN'T work:
=SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
Both you formulas work for me.
Replace@mailinator.com with @tiscal...shared pop3 account with different outlook clients.
We are using different versions of Outlook. The versions
used are Outlook Express 5.5, 6.0sp1 and Outlook 2000 and
The problem is a shared pop3 account, which all the
clients receive (and leave a copy to). Some clients
receive the mail properly, but some clients receive
corrupt messages, i.e. messages with no subject and sender
fields and a blank message body.
Now, i found a corresponding KB article, but we dont have
any IE 6.0:s installed. Is there any other software which
might have that mlang.dll problem, or should i upgrade all
browsers to 6.0SP1?
Thanks in adv...