External data query using MAX/SUM

I'm trying to create an external data link using Microsoft Query.  Ho
do I write SQL in Microsoft query that will return a max date and su
an amount field for the max date only?

Current SQL is as follows:
SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME
Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT)
Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS)
FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM
CB_PORTFOLIO_DMN
WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME

This returns the sum of the exposure amount for all rows, not just th
max post date

--
amarch00Posted from - http://www.officehelp.i

0
12/21/2005 9:28:06 PM
excel 39879 articles. 2 followers. Follow

1 Replies
394 Views

Similar Articles

[PageSpeed] 9

Not knowing what kind of database you're using I did 2 things to try to help:

1)I built a simple model of the technique using Excel "tables"
2)I edited your SQL to make it resemble what you should try to achieve.

1)
The XL list is named myTable and has 2 columns: myDate and Amount.

To get the MaxDate as a usable field, I embedded a subquery in the SQL.
Here's the SQL I put in MS Query:

SELECT 
myTable1.myDate, 
SUM(myTable1.Amount) AS myAmtSum  
FROM `C:\Excel Stuff\ForumHelp\QryReturnDatesMatchingMaxDate`.myTable  
myTable1, 
(SELECT Max(myTable2.myDate) AS MaxDate 
FROM `C:\Excel Stuff\ForumHelp\QryReturnDatesMatchingMaxDate`.myTable 
myTable2) tblMax 
WHERE 
myTable1.myDate = tblMax.MaxDate
GROUP BY 
myTable1.myDate 

2)My probably feeble attempt as correcting your code:
SELECT  
CB_PORTFOLIO_DMN.PORTFOLIO_NAME,
CB_POOL_PORTFOLIO_1.POST_DATE, 
Sum(CB_POOL_PORTFOLIO_1.EXPOSURE_AMT),
Count(CB_POOL_PORTFOLIO_1.NBR_AVG_MATY_YRS)
FROM 
CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO_1, 
CB06U.CB_PORTFOLIO_DMN CB_PORTFOLIO_DMN, 
(SELECT MAX(CB_POOL_PORTFOLIO_2.POST_DATE) AS MaxDate 
FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO_2) tblMax
WHERE 
CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
CB_POOL_PORTFOLIO_1.POST_DATE = tblMax.MaxDate 
GROUP BY 
CB_PORTFOLIO_DMN_1.PORTFOLIO_NAME, 
CB_POOL_PORTFOLIO_1.POST_DATE

I suggest playing with the XL model first, then try to adapt the technique 
to your "real" situation.

Comment: It may be possible to eliminate the subquery by using some form of 
DMAX function, but I'm afraid I don't have enough time to work through that 
scenario.

Good luck

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"amarch00" wrote:

> 
> I'm trying to create an external data link using Microsoft Query.  How
> do I write SQL in Microsoft query that will return a max date and sum
> an amount field for the max date only?
> 
> Current SQL is as follows:
> SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME,
> Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT),
> Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS)
> FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DMN
> CB_PORTFOLIO_DMN
> WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
> GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME
> 
> This returns the sum of the exposure amount for all rows, not just the
> max post date.
> 
> 
> -- 
> amarch00Posted from - http://www.officehelp.in
> 
> 
0
12/22/2005 12:21:01 AM
Reply:

Similar Artilces:

Can we use WINCE 6.0 R2 or R3 to build Windows Phone OS Image??
Hi, Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone OS Image?? If yes Which option I need to select while building the OS Image?? Since the Windows Phone 7 core is Windoes CE 6.0.I am curious to know whether Windows Phone 7 OS Image can be built using Platform builder. TIA, Nithin On 29 June, 10:29, Nithin <nithin.papd...@gmail.com> wrote: > Hi, > > Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone > OS Image?? > > If yes Which option I need to select while building the OS Image?? > > Since the Win...

Use Copied Outlook PST file as default...How?
If I copy a PST file on my PC, how can I configure my Laptop Outlook to use that copied file as its default. Is it possible to copy new Defined Views in Contacts from one computer to another. Dmahanay <anonymous@discussions.microsoft.com> wrote: > If I copy a PST file on my PC, how can I configure my > Laptop Outlook to use that copied file as its default. Outlook version? > Is it possible to copy new Defined Views in Contacts from > one computer to another. I think views are registry items and not kept in the PST. -- Brian Tillman My outlook version is 2002. >--...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Hidden data when pasting from html page into Excel?
I have a situation where I copy a report, which is generated as an html page, into Excel for analysis. When a new copy/version of the report is available, I copy and paste the new data over top of the old in Excel. I've noticed that the size of my Excel file is growing after each copy/paste situation. To manage this, I can delete the worksheet where I paste the data and reconstruct it. After doing nothing else, saving the workbook results in an immediate reduction of file size. If I simply select everything on the sheet and delete, the file size does NOT go down; I have to delet...

Outlook 2002 and Retrieving Data Error Message
OS is Windows XP SP1 Email - Outlook 2002 SP3 Error Message: Outlook is retrieving data from the Microsoft Exchange Server <server name>. You can cancel the request or minimize this message to the Windows taskbar until Outlook closes the message automatically. Other Related Issues: At times, the message displays the correct server name and sometime it does not. At times, it displays the name of an active directory server. This only happens to workstations located in an remote office connecting to the exchange server via ADSL\VPN connection. We have one exchange 2003 server - public...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Fire a Workflow using a Callout
Hi, I would like to fire a WorkFlow using a callout. I got problems to do that. Can you tell me how I can do that? I tried it like that: CrmService.ExecuteWFProcessRequest wf = new LetterSalutation.CrmService.ExecuteWFProcessRequest(); wf.ProcessId = GetWorkflowProcessId(workflowName, entityContext); myService.Execute(wf); But I get an error: Server was unable to process request. Can you help me? Simon ...

how to route messages using the internet and not the VPN c
Hi, we are currently in the process of evaluating exchange 2003 in the past we used 3rd party POP3 mail servers for each office, so each office also had it's own MX record no problem that way..BUT now we implemented AD and one of the exch2k3 server is the US is holding the Primary dns MX record for the company the same MX record that we want everyone else to use ( i.e someone@company.com ) regardless to which office they are located in. when the mail is being intercepted on the primary mail server, it's routing itself via the AD GC's servers internally on the vpn connection, becasu...

map data (x-y axis) with diffent series name
I have a set of x-y coordinate data set and i would like to plot them showing the respective series name. Is there an automatic way of doing this without haing to plot the data points separately. eg Name x y A 1 6 B 2 3 C 3 4 D 4 2 E 5 5 .. . . .. . . .. . . I will like to have a plot of x and y with A, B, C as series name without having to plot the points one after the other. Hi, Here are a couple of free addins that allow you to link data labels to cells. Rob Bovey's Char...

Free Quantities Using Extended Pricing
Hi, I would like to know if there is a way to enter a promotion using the Extended Pricing as "buy 5 get 1 free", all what I figured that you can build "buy 1 get 1 free" but my customer case is making it in layers each 5 Units with one free. How can I build that? Thanks in advance, ...

Chinese letters in a Query
Hi all, i have this strange issue: I just converted to 2007..and in a memo field of a query i receive chinese letter???? what have i done wrong? See if this link about memo fields in queries helps. http://allenbrowne.com/bug-16.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Peter" <Peter@discussions.microsoft.com> wrote in message news:BC86408F-9337-4E09-BE4A-52E50FC912A2@microsoft.com... > Hi all, i have this strange issue: > > I just converted to 2007..and in a memo field of a query i receive > chinese > ...

what causes the recently used file list option to be unavailable .
Tools / Options / Recently used file list is greyed out - How do I correct this ? John You don't say which version so try searching the knowledge base http://support.microsoft.com/default.aspx With a search string of MRU Disabled in Excel -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "JohnPrice" <JohnPrice@discussions.microsoft.com> wrote in message news:ECA43906-EC33-4B92-8580-39923E449B9C@microsoft.com... > Tools / Options / Recently used file list is greyed out - How do I correct > this ? Hi! So...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...

Lump sum calculation
Does any one know what function (PMT - PPMT- PV- FV) I have to use the get my results? I want to have 18 years from now the amount of $70,000 If the interest rate will always be 12% I want to make only one payment new. Can any one help me find the function? I couldn't do it by my self, I need your help. Thanks Joe Hi Joe! Try: =PV(12%,18,0,70000,0) Returns: -9102.77130820508 The negative reflects a payment out of 9102 in return for a payment in of 70000 You could negate the function or negate the 70000 if you want a positive sign but in general terms life is less confusing if you fol...

Email from Outlook using SharePoint
I have been able to sync a SharePoint library to my Outlook account. i can't seem to able to attach more than one SharePoint file to an email. Is this a system limitation or am I miossing something. ...

Stumped with LIKE query
I have a MS Access database (from Office XP) to catalog my movies in. Here's my dilemma... I have Shrek 1 an 2. From within Access, how can I query the names so Shrek and Shrek 2 are both returned? I either get one or nothing. Here's what I have tried (among other things): SELECT Table1.MovieName, Table1.MovieYear, Table1.Category, Table1.Rating, Table1.Plot FROM Table1 WHERE Table1.MovieName Like [Enter Movie Name]; That returns only "Shrek" and not "Shrek 2". ------------------------------------------------------------------------------ ----- SELECT Table1.M...

Outlook is trying to retrieve data from the Microsoft Exchange Ser
Hi I keep getting a msg saying "Outlook is trying to retrieve data from the Microsoft Exchange" everytime i try and send a mail... the mail then gets stuck in my outbox. I still receive all email and this only happends over one particular broadband connection??? If i use dial up it works and if i go to some other broadband connection it works... any ideas? _VERITAS_ wrote: > Hi > I keep getting a msg saying "Outlook is trying to retrieve data from > the Microsoft Exchange" everytime i try and send a mail... the mail > then gets stuck in my outbox. I s...

Using tables created in 2003 IN 2007
My office has recently upgraded to 2007. I enjoy new features such as the ability to highlight a few words within the table without the ENTIRE table's font changing; unfortunately, this only works in tables I have created since the upgrade. My old tables that were brought over from 2003 do not have this capability. Is there an add-on out there? I do not have to resort to re-typing and creating all new tables. PS. Copy and pasting into a new table does not work. Convertting the file using the office button does not work. Help? please? I think you talking about what is called...

Saving data #2
Hi all, I need to save data (results) from a base spread sheet program that i use on a weekly basis. i am in the middle of building this program, and have just discovered macros, but this, along with links is about my current knowledge of excel how can i automate to accumulate data from the base spread sheet (program) when i clear all data from the program to produce fresh results the following week, and to keep past data up to date and available for further use. Any help would be appreciated. legepe With a combination of formulas and dynamic named ranges, it is possible to just add the ...

Import and Export from/to Excel query
I need to import and export Excel files in and out of Outlook 2003. When I try and do it I am told I don't have the correct translator and do I want to install it - when I say yes it tells me to put in the CD - when I put in the CD it doesn't install but just freezes up and I have to cancel - the file it tells me I need is L4561403.CAB How and where do I install L4561403.CAB which I assume that I can find on the CD?? TIA for any help. Brian Tozer KiwiBrian <briantoz@ihug.co.nz> wrote: > I need to import and export Excel files in and out of Outlook 2003. > When ...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

Is it possible to use a formula in the middle of a sentence in exc
If so, how? Your question would be clearer if you wrote it in the body of the message, added more detail explaining what you were looking to accomplish and provided an example or two that showed what you were trying to do. I'll take a guess that this might be what you are looking for... ="Some text"&<YourFormula>&"and some more text" For example, something like this example maybe (where I'm assuming Column A contains a list of names)... ="There are "&COUNTA(A1:A100)&" people on the list" -- Rick (MVP ...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "fi.or.jp.de" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

Cut'n'Pasting data
Greetings ! I have a CSV data file wot looks something like this - "1529.17698720957","133.597550559965" "1685.21901149326","132.817184396522" "1900.00000000000","130.300887834893" "2193.34485206410","121.730777157435" "2363.17295960769","114.937652855693" "2523.01169878763","108.544103288496" "3240.77088467590","87.805735336415" "3590.69860622591","81.680775462264" "4229.19543928027","78.487652800160" This data i...