Need to do Query Similar to Vlookup in Excel

[gse14]

I am a very new user of Access 2000 so please bear with me. I am a financial planner and am trying to create a database that will handle invoicing our clients quarterly. We charge a percentage rate based on total assets at the end of each quarter. Unfortunately not all clients are billed according to the same rate schedule. There are two rate schedules "old" and "new". I have created a table that displays which rate schedule the client falls under ("old" or "new" in field). I also have created two separate tables - for example, one titled old and it looks like this:

Table RangeLow RangeHigh QuarterlyRate
Old $5,000,000 $15,000,000 0.1250%
Old $3,100,000 $4,999,999 0.2500%
Old $2,100,000 $3,099,999 0.2750%
Old $1,600,000 $2,099,999 0.2875%
Old $1,300,000 $1,599,999 0.3000%
Old $300,000 $1,299,999 0.3125%
Old $200,000 $299,999 0.3750%
Old $0 $199,999 0.5000%

I need a query that will lookup the correct rate schedule depending on the client and then identify which range the total assets fall under, and then have the quarterly rate be entered into a field. In other words if the Smith's have $1,000,000 in assets and fall under the "old" fee schedule I need the query to spit out the quarterly rate as 0.3000%. Obviously the asset values vary each quarter and this query would need to be run to generate an invoice.

I have read through several tutorials on SQL and I just can't figure out where to even start. I am able to do the job using Excel's VLOOKUP function - but I am not finding the same function in Access. Any help is greatly appreciated. I am simply going around and around in circles at this point.

 

[lupins46]

You need a DLookup function.

Something like:

Dlookup("QuarterlyRate","Oldtable","RangeLow< " & forms!myform!myvalue & " AND RangeHigh > " & forms!myform!Myvalue)

Use the afterupdate of the myvalue textbox to run the code.

I would be inclined to set the lower limit of one and the upper limit of the previous range to the same value to deal with any decimal amounts.

 

[mikey69]

GSE14 - You need to experiment so that you find out that Access is significantly different from Excel. You don't put the dLookUp function directly in a table, as you'd expect to in Excel. It has to go in the query grid, or in a Visual Basic module. the third place you can use it is in the Record Source property for a control on a form or a report. People just don't talk about these differences, but believe me they are fundamental and you need to understand them to get the best out of Access.

Post again if you need more info.

Access makes all things possible. It even makes them intelligible

 

[gse14]

Thank you both for your help. I have noticed many responses containing "myvalue" "myform" - does this just indicate whatever my particular form in my database is called? I apologize for the ignorance.

In addition, is Dlookup the best way for me to produce the result I am looking for in a form? It seems that some of the more common sql functions might work. Such as using some of the expressions as criterion. For example do I even need to have a specific rate table? I want the form to pull a number from a table if a value falls between 2 fields on a row.

Is there a tutorial some where on how to determine where to enter the SQL statements? I am referring to specifically the query view. Does SELECT always go in the field box? What is the difference between the WHERE statement and HAVING statement vs just entering specific criteria?

So many questions. At this point I have spent 100's of hours trying to teach myself this program and am panicking now because the more I learn the more I realize I don't know what I am doing. I truly appreciate the help.

 

[lespaul]

First make sure you have an understanding of relational databases ('The Fundamentals of Relational Database Design'). Once you're sure your table structure is sound, then you should design forms to gather and display the data in the tables.

You use queries to display specific information in forms. That's why you see the myform!myvalue, this is how Access knows where to get it's information from.

Let's say you want to see the personal information on the Smith's. You would have a process that prompts you for who to display. This process would open a simple form (frmSearchCriteria) that has a label: Who to search for?, an edit box (edLastName) that allows the user to enter a name, and a button that says 'Search'. When the button is pressed you will run a query:

SELECT * FROM tblClientInfo WHERE LASTNAME = frmSearchCriteria!edLastName

and another form is based on this query (frmClientInfo) and opens with Smith's information.

Hope that helps a little on that part. As far as your SQL questions. The WHERE clause is used to restrict the records returned in a query:

SELECT * FROM tblClinetInfo WHERE STATE = 'CA'

this will only return records where the state is CA.

The HAVING clause is used to eliminate records from a GROUP BY clause. The GROUP BY clause is used when performing an aggregate function on some records:

SELECT ClientID, SUM(Deposits)
FROM tblClientDeposits
WHERE STATE = 'CA'
GROUP BY ClientID
HAVING SUM(Deposits) > '50000'

Every field in the SELECT except the aggregate function must be included in the GROUP BY clause. The query above restricts the WHERE portion to only those clients in CA; the HAVING clause restricts it to clients who have more than 50000 in deposits.

Hope that helps a little!



Leslie

 

[mikey69]

GSE14 - Don't feel ignorant. We all started out like you and we all still have huge blind spots. The joy of these forums is that they are so embracing when you have a problem.

Regarding you question about Dlookup. It is a relatively slow way of doing things, so it's fine for the odd lookup, but if you are processing thousands of records, ach with multiple lookups you'll be drinking lots of coffee whilst you wait. In such situations, it's usually more efficient to use a correctly constructed query (read LesPaul's suggested article for some insight here) and access the data via the FindFirst method in Visual Basic. This is a fairly big subject - too big for a post such as this, but it is right at the heart of what decides whether your application is a star or a dog. the Microsoft knowledge base is a good first source of papers on relational database design, and Helen Feddema's web page is also useful. good luck.

Access makes all things possible. It even makes them intelligible

 

[gse14]

Thank you both for steering me in the right direction. Looks like I have some more reading to do. I have found that getting clarification on what it is I should be learning about to solve my problem is a huge time saver. Best Regards,
G