Caclulated Fields 2.0 ;)

[SinnaminGrrl]

I searched the forum and saw several posts to this effect, but nothing quite had the detail or scenario I am faced with.

I have a data dump that I get daily in excel.

There are several columns I need to create a new report out of but I also am finding myself needing to do the same manual calculations in Excel every day. I thought this could be solved nice n easy with access to do the "calculatin'" for me.

The problem is I don't know how to do all these multiple queries because some of the work I need to do is based on the result of a calculated field/query.

Here's the details:

I get the data dump as such:

Issue	ComputerName	Domain	ComputerType	State	Countrycode
K222222	12345678XX123	xyz.com	=mid(A2,8,2)	=vlookup(based on computer type)	=vlookup(based on domain)
K333333	87654321YY321	zyx.com	=mid(A2,8,2)

<tbody>
</tbody>

<strike></strike>
The calculations I do in excel are first a "mid" to extract XX and YY (for example) from the computer name. That gives me a "Computer Type", based on computer type I do another lookup to get the state of the machine. Then further I need a separate lookukp on the domain to get a country code.

So, I started in Access by creating a query to get my "computer type".. .. now how do I query on the results of that query?...I hope this makes sense.

 

[ranman256]

in the query, Q1:
mid([ComputerName],8,2)

(use fieldnames instead of cell names)

then in Q2, you can join the 2 char state code to the tState table to get the full state name.

 

[Joe4]

Yes, you would use Queries to do all of this.

Access has many of the same (or similar) functions as Excel. It has a MID function, and it has a DLOOKUP function (instead of VLOOKUP).
See this for details on DLOOKUP: https://www.techonthenet.com/access/functions/domain/dlookup.php

With each calculated field, you assign it an "Alias" (variable name). Then you can use that "Alias" in other calculations in the same query.

Sometimes, if the queries start to get a little too crazy, you can break it up into multiple queries.
For example, let's say that you want to do a bunch of calculations, and then some totals by Domain. You could do the calculations in one query, and then create a new query that uses the first query as its source, and do your totals on that query.

 

[SinnaminGrrl]

Thank you both...

So, still confused tho...

I have a query called "ComputerType" that is just used to get the 2 character Computer Type.

CompType: Mid([ComputerName],9,2)

Now, I cant seem to get the Dlookup to work, I don't think im joining properly....Error after error...

In my new query "StateQuery", I have the "ComputerType Query" and the "StateLookup table" open. I joined the 2 fields that are the 2 character computer type so that it returns all from the "ComputerType" query and only those records from State lookup where the joined fields are equal? ... is that the right Join?

Next I tried to do a dlookup in a new Alias'd field "FinalState:dlookup([state],"statequery",[comptype]" ???? I mean the "where" statement is really throwing me off...

 

[Micron]

dlookup([state],"statequery",[comptype]"

this is what you actually are using? You're missing the ending parenthesis, 3 double quotes and the value to use as criteria.
Try dlookup("state","statequery","comptype= 'string'") assuming you have the actual text value for the criteria as opposed to a variable.

 

[SinnaminGrrl]

OOOK... Thanks for the snide comment and the attempt to help me at the same time. Ill just go back to excel with my tail between my legs cause clearly, this is not easy to explain and I am not just taking advice but watching vids, reading articles and its just apparently overly complicated. On top of that, now I FEEL like an idiot for asking for help. So thanks again for that. ../not surprised ON.

 

[Micron]

Seriously?? I've re-read my post and see nothing of the sort. Too bad reading text doesn't import the inflection intended by the writer. Then people with thin skins wouldn't take things the wrong way. Maybe trying to help others over 1,200 times in this forum and 3,000 elsewhere tends to make me cut to the chase. I asked that question because you'd be surprised how many times people post an expression or piece of code that has been mis-typed or improperly copied and pasted.
If you prefer, I'll try hard to remember not to intrude on your issues from now on.

 

[xenou]

So this is the correct format:

dlookup("state","statequery","comptype= 'string'")

Two things to remember:
1) the final argument in the DLOOKUP (the where part) is really exactly like a SQL WHERE statement, without the WHERE keyword.
2) You can use single quotes inside the double quotes. String literal values in SQL should be in quotes - normally in Access people use double quotes but single quotes work too, and in this case single quotes work nicely since they can be nested inside the double quotes.
3) The last part is tricky because you are building a STRING (often using concatenation) - you should be able to print it out and have it look exactly like a written WHERE clause without the WHERE keyword.

Note from Post 1:

So, I started in Access by creating a query to get my "computer type".. .. now how do I query on the results of that query?...I hope this makes sense.

You can in fact simply query your queries, if you want to do so and it works.