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. 2 followers. Follow

1 Replies
824 Views

Similar Articles

[PageSpeed] 4

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:

Public Folder tree
We have some public folder trees that are getting 5-10 levels deep, as they are by default matching our projects drive folder structure. I am just wondering, is there a limit to how deep and wide a public folder matrix can/should go? I can easily see a structure of 20 projects, each as many as a hundred subfolders in a tree up to 10 levels deep, with literally thousands of files in each project. Am I courting disaster? Thanks, Gordon On Tue, 1 Nov 2005 11:02:09 -0800, Gordon Price <GordonPrice@discussions.microsoft.com> wrote: >We have some public folder trees that are getting...

Displaying parameter query dates in a report?
I want to base a report on a query which used parameters to select dates. How can I include the dates I have chosen in my report? On Wed, 4 Jul 2007 15:40:50 +0100, C Tate wrote: > I want to base a report on a query which used parameters to select dates. > How can I include the dates I have chosen in my report? Add an unbound control to the report header. Set it's control source to: ="For sales between " & [Enter Start Date] & " and " & [Enter End Date] The text within the brackets must be identical to the bracketed text in the query parameter...

Could my ADODB connection be causing a network problem?
My employer's network is experiencing an unusual problem. An 8 mB write to the network drive takes about 8 seconds but reading the same file takes around 5 minutes. I'm wondering if the ADODB connection that I'm using (so that our parts room can log part issues to an access db) could be causing the problem? The db and the spreadsheet were created in Office 03 but the db is used nearly continuously in Office 07. The Spreadsheet is used nearly 24/6. I have included my code. Many many thanks in advance and Happy Thanksgiving Ted BTW There are 4 PC's (2-2007s...

Problem installing outlook 2002
Hi I get the follwing message and cannot use it. Any ideas? Unable to display the folder. Microsoft Outlook could not access the specified folder location. The .DLL file for the information service could not be found. MAPI was unable to load the information service PSTPRX.DLL. Be sure the service is correctly installed and configured. cheers steve The info here may help: http://support.microsoft.com/default.aspx?scid=kb;en-us;293058 "Steve M" <baz100uknosp@hotmail.com> wrote in message news:eue4l5j0FHA.1032@TK2MSFTNGP12.phx.gbl... > Hi I get the follwing message ...

formating problem
Hi, I am using Excel 2000 my problem is cell formating, I am typing number in a cell say 5 at it appears as 0.05 I have tried going an formating the cell as number it does not change, I tried formatting a general it did not change. I also tried going into options in tool but I don't know what has changed so I can correct it. Any idea? Thanks Sazi -- Message posted from http://www.ExcelForum.com Hi Tools/Options/Edit Take the check mark out of Fixed Decimal -- Message posted from http://www.ExcelForum.com Cutter wrote: > *Hi > > Tools/Options/Edit Take the check mark...

Complex formula change making m,e crazy
I have thsi formula... myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN""," _ & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))<999," _ & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN"...

How to get the info from form to query to report
I have a form that uses 5 tables for the information so I built a query to have the info all in one place. I also want to use the submit button to send the information to a report to be emailed, right now the report comes in blank. Please help I am fairly new to access and trying to build this for work. Base you report on a query. Use the form reference as criteria in the query like this --- [Forms]![YourFormName]![YourObject] [YourObject] is displaying the data from the 5 tables. -- KARL DEWEY Build a little - Test a little "sandyL" wrote: > I have a form that us...

VLOOKUP Problem
Hello. I'm trying to check if a cell value is found on another page of my workbook. I'm trying to use the VLOOKUP function Well, what exactly is the problem? It might help if you can tell us what you have - sheetnames, columns used etc. If you just want to see if the cell value is on another sheet you could use MATCH. Pete On Dec 10, 10:18=A0pm, ckrogers <ckrog...@discussions.microsoft.com> wrote: > Hello. =A0I'm trying to check if a cell value is found on another page of= my > workbook. =A0I'm trying to use the VLOOKUP function ...

Problem importing items using MYSQL Import from EMS Hitech
I am attempting to set up RMS. I have an excel item list from my largest supplier. It has the MFG ID (which I believe is used to generate the UPC code), Description, sale price, cost, UPC code, Category ID. I have added a category ID field to the file as well. But every time I map this file to my RMS DB Item table and try to import I get the same error: "Key columns are not defined". Since it says "Columns" I assume the problem is with the excel file? Or is it telling me that I am failing to load a key field in the Item table? Mark - What are you using to do the imp...

Printing Complex Workbooks
Hi -- I am wondering if anyone has experienced this issue. Some of my users have created very complex workbooks, with different page orientations, formulas, colors and different page sizes. When these documents are printed (NDPS), Excel crashes. This only happens when the entire workbook is printed. Not the active page. The only solution is to use an older version of the Novell print driver - NDPPNT.DLL. Environment specs: Windows 2000 Excel 2000 Netware 5.1 SP6 Novell Client 4.90 HP PCL 5E (latest firmware on printers, latest version of PCL 5E driver) Novell's stance is if pr...

a running sum (by date) in a query?
Is this possible? I have dates (some of which are the same) and I would like to use them to create a running sum of another column in my query. Is this possible? On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote: > Is this possible? I have dates (some of which are the same) and I > would like to use them to create a running sum of another column in my > query. Is this possible? Sorry, not only do i need to create a running sum by date but it also has to be PER sales person. So even though all the sales people are in one table and I am querying the table different ways, I need to m...

BIG PROBLEM #2
OK. I JUST INSTALLED MY NEW COPY OF OFFICE XP PROFESSIONAL WITH FRONTPAGE. THE PROBLEM IS THAT WHEN I TRY TO ADD AN EMAIL ACCOUNT IN OUTLOOK, IT SAYS SOME REGISTRY ERROR. I QUOTE: "THE OPERATION FAILED DUE TO A REGISTRY OR ISTALLTION PROBLEM. RESTART OUTLOOK AND TRY AGAIN. IF THE PROBLEM PERSISTS, PLEASE REINSTALL." I TRIED TO REINSTALL, SAME THING. I HAD THIS PROBLEM BEFORE AND THE TECHNICIAN ON THE PHONE TOLD ME TO SOMEWHERE AND CHANGE SOMETHING. I DONT REMEMBER WHAT IT IS. CAN SOMEONE PLEASE HELP ME. Pratik, you wrote on Sun, 4 Sep 2005 16:05:02 -0700: > OK. I JUST IN...

Notes Connector Synchronization Problem
Hi, When I run synchronization in the Notes connector the users from the Domino directory do not show up in the Active Directory. I get following errors in the event logs: Event ID: 8307 Category: Domino Directory Synchronization Source: ExchangeDominoConnector Description: Error 4b8: Problem occurred when importing entry username/domain to exchange Error 2: Event ID: 8270 Category: LDAP Operations Source: MSExchangeADDXA Description: LDAP returned error [32] insufficient rights when importing the transaction Dn: cn=username/domain, cn=users, dc=domain, dc=com Error 3: ...

Automatically backing up Mailboxes Query
I am using Exchange 2000 SP3. I have been manually backing my Exchange Mailboxes using ExMerge. Is there a batch file (.bat) to do it automatically. IE One that doesnt add to the previous pst file. It would have to overwrite the previous pst file as space is an issue. Any help would be appreciated. Joe. ...

MenuItem properties problem
hi i created a multi document interface applicatio in that resources i add a menu with submenus to the existing menu resource "IDR_CLIENTTYPE when i run this application, the submenu items only(file & other menus are in correct state) which i added are in 'disabled' state. how can i make it enable when the menu is loade please help me ...

query is too complex error message
does anyone know if access 2007 has increased the capabilities of their queries? i have a query that was built in the 2003 version that keeps giving me the subject line error message. the query includes 7 fields that have a criteria requirements. it works for about a month and then just stops working. any help would be appreciated. thanks,andrea-- Andrea On Tue, 27 Mar 2007 18:13:51 -0700, Andrea <Andrea@discussions.microsoft.com>wrote:>the query criteria is as follows:>>([forms]![frmSearchByMultipleFunctions]![ThisValue5] >or([forms]![frmSearchByMultipleFunctions]![ThisVa...

Using complex formula in Diagram,AutoShape,WordArt,Organization Ch
Download and free try AddinTools Create from http://www.addintools.com Make you using formulas and cell reference in Excel objects: diagram, autoshape, wordart, and organization chart. To get thousands of Diagrams, AutoShapes, WordArts, Organization Charts, Charts, Reports, Tabs, Cards, and Forms that contain different values in several minutes! The quantity of results that you can produce depends on the volume of data that saved in worksheets. Fully Utilize The Data In Workbooks. The data in results may source from the same worksheet, and may also source from one or more workbooks and...

Duplicate Records in Query
I have five names David Joe Smith Hank Jim I want the query to return. David David Joe Joe Smith Smith Hank Hank Jim Jim Thanks, David I don't understand. While I do see that there are two of each, the order of the names is different between starting condition and ending condition... and the ending names are not sorted alphabetically. How did you come up with the sort order? Will you always only want 2 of each? Could it be three? Regards Jeff Boyce Microsoft Office/Access MVP "gumby" <david.isaacks@va.gov> wrote in message news:1175729567.219968.146140@q75g2...

Update Query
Hello, For every record in a table I need to update one of four Yes/No check box fields based upon date span. If the "In Date" is in the same month as "Serve D" or "Return D" field place a check in the "DispodWithinSameMonth" yes/no field. If the "In Date" is within 30 days of the "Serve D" or "Return D" field place a check in the "DispodWithin30Days" yes/no field. If the "In Date" is within 60 days of the "Serve D" or "Return D" field place a check in the "Dis...

Strange permission problem
We are doing a mailbox mirgration from e2k to e2k3. everyone migrated is working fine with the exception of one individual. His mail was fine until we moved his mailbox to the e2k3 which is a member of the e2k (first exchange server). Whenever he tries to send he gets a message from the sys admin stating that he does not have the permission to send to this recipient. He can not send internally nor external. No one else is experiencing this issue except for this one user. Everyone is using the same system, XP/SP1 Outlook 03. Do you have any restrictions on sending in your SMTP Con...

External web query
I am not a programmer and a new excel user. I have an external web query that I am using tracking external links from a web site. I need to create an additional column with the web page info that relates to where the link is coming from. I tried but when I refresh the data it inserts cells with the any new links listed but then that throws off my additional column info. I looked in Data Range Properties and checked the middle option but that doesn't work the way I thought it would. Suggestions? -- Message posted via http://www.officekb.com ...

Query based distribution list problem
Running exchange 2003. Our query based lists work fine except the sender gets an error message for accounts that have expired. Is there a way to get around this? TIA...Bob Did you mean accounts that are disabled? Accounts with expired passwords shouldn't have an issue receiving mail, afaik. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message news:Xns974A7C1C06982bobarcabca@207.46.248.16... > Running exchange 2003. Our query based lists work fine ...

Windows Calender Problem.
Windows Calendar does not notify me of events. When I load task scheduler it says "Task image is corrupt or has been tampered with mcupdate" and "Task image is corrupt or has been tampered with Reminders-name" Vista Home Premium Need help to fix. Hoot Either your taskplanner is corrupt or you suffer from pre-sp2 issues : Vistas Windows Calendar has had reminder problems in its first incarnation.. SP1 / SP2 fixes the issues. mcupdate ist the Mediacenters tv guide download which is scheduled to run in the background. Also a lot of Mediacenter issues a...

Problem working offline, 2 GB limit.
Hi. I,ve got a problem working offline from my Exchange 2000 server. The problem is that I�ve got more than 2 GB of email and my offline store cant be bigger than that. Is there something I can do to be able to take my entire mailbox with offline?, or is it a limit in outlook? My outlook has been upgraded from 97->2000->xp->2003, but I have removew the ..ost and created a new one with Outlook 2003. /Erik Erik, I do not believe that you can go over 2GB for offline storage (.ost). More importantly I would not suggest you do it that way even if you could. That is a lot of d...

need help..Simple, compound, complex, compound-complex examples
Need to write a paragraph on the four sentence types...Just need to have an example of each type....please someone help!! Thanks On 30/05/2010 4:18 PM, Peggylynne wrote: > Need to write a paragraph on the four sentence types...Just need to have an > example of each type....please someone help!! Thanks See http://www.eslbee.com/sentences.htm and some of the other sites that appear when you google for the subject of your post. -- Hope this helps, Doug Robbins - Word MVP Please reply to the newsgroup unless you want to obtain my services on a professional basis. ...