Complex Query Problem

I have a database with a number of fields  Program, LTV, Amount, Price, Rate, 
Fee.
Each row has the same Program, a given list of LTV's, Amounts and Prices.  I 
want to retrieve only the lowest Rate and Fee at each give each program, ltv 
and amount.

I used an update table query but it is inconsistant in returning the lowest 
rate and fee even though the table from which i am selecting is sort 
correctly.  

Any help would be greatly appreciated.

thanks


0
Utf
12/12/2007 6:51:00 AM
access 16762 articles. 3 followers. Follow

1 Replies
962 Views

Similar Articles

[PageSpeed] 40

Try using a Totals query with Group By as the Total criteria in every field 
but either Rate or Fee and Min in the Totals row of that field. Assuming 
that the row with the lowest fee will also have the lowest rate. If that's 
not the case use the Min criteria in both fields.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"bobquan" <bobquan@discussions.microsoft.com> wrote in message 
news:AA8FBD61-AF90-4EE7-BFF1-E87960DCF09B@microsoft.com...
>I have a database with a number of fields  Program, LTV, Amount, Price, 
>Rate,
> Fee.
> Each row has the same Program, a given list of LTV's, Amounts and Prices. 
> I
> want to retrieve only the lowest Rate and Fee at each give each program, 
> ltv
> and amount.
>
> I used an update table query but it is inconsistant in returning the 
> lowest
> rate and fee even though the table from which i am selecting is sort
> correctly.
>
> Any help would be greatly appreciated.
>
> thanks
>
> 


0
Arvin
12/12/2007 7:20:58 AM
Reply:

Similar Artilces:

Dates from query
I have a report with the control source as a query. This query consists of one table and another query. This original query has a field called "day." In the criteria I have Between #12/01/2007# And #12/31/2007#. I would like in the report to have these dates captured at the top. How do I do this since these fields are not considered apart of the control source for the report? -- Message posted via http://www.accessmonster.com See: Limiting a Report to a Date Range at: http://allenbrowne.com/casu-08.html Explains 2 approaches: parameters in the query, or text boxes re...

OCX module state problem in VS2005
Hi, I have an OCX ( a regular MFC DLL dynamically linked with MFC, built with _USRDLL and _AFXDLL). The problem I am facing is that when my MFC application load this OCX, the m_classList member variable of AFX_MODULE_STATE get resets to NULL. This was not happening with VS2003. OCX IinitInstance is defined as BOOL CFooApp::InitInstance() { return COleControlModule::InitInstance(); } CFooApp is derived from COleControlModule and COleControlModule is derived from CWinApp. The function exposed in this OCX is STDAPI DllRegisterServer(void) { AFX_MANAGE_STATE(_afxModuleAddrThis); i...

Oulook Express 6 Problem
Internet explorer does not open when i click a link on an email. Try these steps... http://support.microsoft.com/default.aspx?scid=kb;EN-US;q177054 -- "I don't cheat to survive. I cheat to LIVE!!" - Alceryes "Simon P. Fleming" <badseed247@blueyonder.co.uk> wrote in message news:9UHje.105419$Cq2.23051@fe2.news.blueyonder.co.uk... > Internet explorer does not open when i click a link on an email. > Also the "URL monitor fix" on this page may work. http://www.infopackets.com/gazette/2003/20030107_web_links_in_outlook_express_dont_work_...

Imap append problem
Hi, Im trying to move mails between accounts with imap. The target account is a Exchange account and on a 36Mb mail i get "This message is to big and can not be appended". Is it possible to change that maxsize setting somewhere? /Erik On Fri, 21 Jul 2006 09:37:51 +0200, "Erik Dunder" <erik.dunder@fsdata.se> wrote: >Hi, > >Im trying to move mails between accounts with imap. >The target account is a Exchange account and on a 36Mb mail i get "This >message is to big and can not be appended". >Is it possible to change that maxsize setti...

Problem w/ calculations in the Pager Footer
I am creating a report to record and provide information on how long a water pump station has been operating over the last 45 days. I have created the form to enter the information (on a weekly basis), a table to store the information, a query to extract the last 45 days of information and a report to display the last 45 days of information to the end user. But I don't seem to be able to get access to complete the calculations needed in the Page Footer of the report to display the the difference between the last two entries and the average difference over the last 30 days of opera...

Very Large Web-query
Hi All, I've an issue with a web-query I could really use some help on. Basically, I've a web-query that successfully returns data from an internal web-site. Works fine, has been for a while. However, the data being pulled down is getting very large and the query data is very shortly going to extend beyond the limit of a worksheet (64K rows). I'd like to split the query data across multiple worksheets, but can't find an easy way of doing it. Also, I know I could split the query manually into two web-queries, using table number or names, but there are a lot of tables and t...

Contacts synching problems.
I have another problem. I have consolidated 2 contact lists accidentally. How can I create a new contacts list so that I can slowly move over the contacts that I want to move to this new contacts list? Any and all help would be greatly appreciated. Thanks in advance. -- darylakagod darylakagod MCP MCSE CCNA <darylakagodMCPMCSECCNA@discussions.microsoft.com> wrote: > I have another problem. I have consolidated 2 contact lists > accidentally. How can I create a new contacts list so that I can > slowly move over the contacts that I want to move to this new > contacts...

2 column subreport problem
I have a report with a single column. I have a sub-report in the detail section of this report. The sub-report has two columns. Only one column prints in the sub-report. TIA for your help. Never mind....I got it. Gettin' old ain't for sissies! "R Fourt" wrote: > I have a report with a single column. > I have a sub-report in the detail section of this report. The sub-report has > two columns. > Only one column prints in the sub-report. > TIA for your help. ...

Problem automating outlook
Hi I am using the following code to automate outlook from within MS Access; Dim O As Object O = CreateObject("Outlook.Application") The problem is that I am getting the following error on the second line; Automation error The specified module can not be found What is the problem and how can I fix it? Thanks Regards Dim O As Object Set O = CreateObject("Outlook.Application") Regards Mark Dormer "John" <John@nospam.infovis.co.uk> wrote in message news:eFs9QqIUGHA.1236@TK2MSFTNGP11.phx.gbl... > Hi > > I am using the following code to...

POP3 Mailbox problems
Greetings, I am having the following difficulties that I could use some help on. I am running a small home/office system, it consists of 1 Small Business Server 2003, 2 desktop clients and 1 notebook client (all winxp). I have set up 7 accounts to the server all with an exchange account. I have email accounts out on the net on a domain that I own and would like to retreive that email and forward to the exchange accounts on my server. I configure the pop3 connector to get that mail, but it appears as though it does not retreive it or it goes elsewhere I don't know which. At lest i...

Problem with Import/Export
Hi, I have a problem with Outlook 2003. When I try to import or export to/from my PST file, I only get messages, contacts, appointments or tasks up to November 2003 - which is the date when I reformatted my computer and installed O2k3 (I imported my backup PST into Outlook 2003). I tried to repair the PST with scanpst.exe - but the problem persists. Any ideas on how to "remind" the file that it has newer items inside - will be greatly appreciated! Many thanks. This is rather a vague question. Importing and exporting are two different operations. I'm having a hard time unde...

Problem with mixed trend and XY chart
I have a number of large spreadsheets used for analysing quality data On each table there are several trend charts showing results over time On each chart I have also added control limits set up as XY ranges. Initially all worked well.....but..... for some reason when I filte the lists now (to select specific products) the x axis labels (set u as a named range) expand to fill most of the graph space. Without the XY ranges it all works fine but with them in place i doesnt. When I check the axis format I also find that I can no longe change the number of categories between tick marks. What am...

Outlook problem #26
Hi I have Outlook 2003 installed on my laptop. Earlier I had Office XP (with Outlook). The problem is, when I start my outlook (pst), it shuts download throwing an error message " Outlook encountered a problem and need to shutdown". This happens only when I don't have a connection to my mail server. As a result I am not able to read mails offline and access pst mails offline. Any takers? With regards and thanks Shareef See 26 May 11.57 Repeated Outlook Crashes >-----Original Message----- >Hi > >I have Outlook 2003 installed on my laptop. Earlier I had &...

simple problem, i think... with running totals
At work we have a competition and we keep up with a set of points we get each day. I want to create a worksheet that looks like this: Name | + | - | Total Points I want to be able to simply enter 3(or any number) in the Plus column have it add to the total points, keep that running total, and clear the 3 that i put in there so it will be ready for tomorrow. The same goes for the minus, I want to be able to deduct these points from the Total Points, then clear the number i entered in the minus field, saving the running total... I'm fairly good with the basics of excel, but i couldn&#...

Refresh All with parameter queries
Hi! I have a lovely file with numerous worksheets and five parameter queries. The queries use different tables and fields, but they all have the same parameter criteria, Month and Year. When I hit "refresh all", I have to enter the parameters five times. Is there a way to only enter them once? The month and year exist on the "summary" sheet in cells M1 and N1 -- is it possible to run the queries with a macro by referencing these cells? Thanks! ...

reference is not valid message for pivot tables based on Access query
I've looked around but can't find an answer to this. I have a workbook - an xlt - that has 6 worksheets that pull data from different queries in the same Access database. I have several worksheets with on pivot table each. The pivot tables are based on dynamic named ranges in the external data worksheets. I use dynamic ranges because I have some adjacent calculated columns in the worksheets. The workbook has only 6 pivot caches - the same as the number of worksheets that pull data. I copied pivot tables from sheet to sheet so that they'd use the same cache, where approp...

autoshape fill problems in Excel 2003
Hi, Just upgraded to Excel 2003 from Excel 2000. Now autoshapes are not transparent so that text can't be seen through them. I go to autoshape properties and choose No Fill and the shape remains black. I can also choose other fills but not "no fill". It is black. So the text is covered up. I've tried send to back on the autoshape object with no results. I've tried changing the color of the text to no results. When I open up the file in Excel 2000 the formatting is fine. Any ideas? Thanks, Kathy ...

Outlook 2003 error "The messaging interface has returned an unknown error if the problem persists restart outlook" #3
Hi, I am not able to perform actions like ReplyAll, Updating a task item.. on my outlook 2003, am getting the following error - The messaging interface has returned an unknown error if the problem persists restart outlook. Prior to this installtion i had outlook 2007 beta installed on my machine but I uninstalled it, actually during the uninstall process i had accidentally clicked the cancel button, later on i did the uninstallation from Control Panel. Then i tried installing outlook 2003 though the installation got executed smoothly but am not able to perform the above mentioned actions. Als...

XSLT Transform problem
I'm generating an XSLT document programatically in VB.Net. I'm then trying to apply that XSLT against a cXML document to generate my own internally developed XML document. I'm using RichTextBox controls to dump out the XSLT, cXML and internal XML. The following code works fine in Visual Studio 2002. However, after upgrading to VS 2003, this same code no longer works I'm declaring a variable "xslt" as a new XSLTransform. Dim xslt As XslTransform = New XslTransform I'm then calling the "Load" method of the "xslt" to load the XSLT...

Problem w. RUS in one subdomain
Hi, I have a problem with RUS. I have a forest with 5 domains. Root domain and four subdomains. Let's call them: top.tld a.top.tld b.top.tld c.top.tld d.top.tld Exchange 2003 server is installed in top.tld (exchange org consists of one server). RUS is created for each subdomain and domainprep has been run on each respective infrastructure master. a, b, and c domain users have exchange mailboxes and everything works well. However, "d" subdomain is having problems. Since RUS was created for that domain (today), I see two 8270 messages coming up on Exchange server and users ...

problem saving xls files into shared folders location
The users in my network are complaining about this problem: They open Excel files from shared folder location and after changes in the file, when trying to save, they get a message sating that the file will be saved under a name something like "E0720002" (different every time). After that the file with the original name disappears from the location, the E0720002 file is there but with no (xls) extension and the users are left without a clue about where the file is -- usually they do not read the message stating that "E0720002" will be the name of the file (I can't blam...

display query running in status bar
hi all, is there a way to display the name of the query that is running in the status bar? i have code executing that has a bunch of queries running and i'd like to see which query is running. TIA Ted application.SysCmd acSysCmdSetStatus, "MyQueryName" Be sure to reset the status bar text when done application.SysCmd acSysCmdSetStatus, " " (Note: space, not "") -- HTH, George "Ted" <tbest@rca-insurance.com> wrote in message news:uO3KPb8RIHA.3676@TK2MSFTNGP06.phx.gbl... > hi all, is there a way to display the name o...

Excel Menu Problem
Have no idea what happened. Using Excel 2003. Now, I don't have a File or Edit menu. I've tried right clicking on the menu bar and selecting customize but that doesn't help. Iv'e tried reset, but nothing. 've been able to add some the File and Edit features to the View menu (which is now my first menu), but printing is gone (among other things). Now I have these menus: view, insert, format, tools, data, window, help, Adobe PDF. On rare occasion, the File and Edit menus appear (on start up of Excel) but then disappear. I'm using XP SP1. THANKS!!! RON Search for ...

Web query and special characters
Hello, I have a simple HTML data dump which has special characters: ������ In the HTML source I placed <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> When I download this table, all the accents are translated into unprintable characters. I know there are macros which remove these unprintable characters, but then the word in uncomplete c�digo gets downloaded as cädigo and cleaned up it turns into cdigo. Any help would be apreciated. Thanks This was fixed by changing the meta tag to <meta HTTP-EQUIV="content-type" CONTE...

How can invoices be generated from multiple queries in Access?
I am an office manager for my local synogogue. I have created an Access database to track our membership and their pledges and payments. I would like to be able to generate quartetly statements that indicates a previous balance, each pledge and payment (i.e., transaction) for the quarter and the current balance. In the past I have used Mail Merge for things like this, but this time I am having trouble embedding in a document information from multiple tables/queries. Any assistance with this task would be greatly appreciated. Thanks! There are some free church management ...