Calculations with query or vba.

[Konai]

Hello, ::USING ACCESS 2007

I am trying to perform a calculation on the results from 2 queries.
Below those 2 queries are labled query_2 (derived from query_1) and query_3.
I would like to take the result stored as PT in query_2 and divide it by CCount and then store the result so that it can be used on a report.

I have looked around for an example of such a calculation and have had no luck yet. If I could get reference this data in VBA and output it that way, that would be wonderful.

So to be more precise,
1. How do I perform this calculation in a query?
2. Can I also do the exact same calculation in VBA?

query_1
SELECT CName, COUNT(CName) AS PC
FROM Contact
WHERE ContactType=1 And EventDate Between Start_Date And End_Date
GROUP BY Contact.[CName];

query_2
SELECT Count(query_1.[CName]) AS PT
FROM query_1;

query_3
SELECT COUNT(*) AS CCount
FROM Client;

Thanks you.

 

[pbaldy]

Since 2 and 3 each return a single record, the simplest way to get each value might be a DLookup. You could use that directly in your report, or in a VBA function.

 

[Konai]

I've been working on this quite a while, finally I seem to have found something that helps though.
It was not so obvious to me as to how to get the result I needed but now i have a working query that has a result that is close to perfect.

In design view I just did this.
test: ([query_2]![PT])/([query_3]![CCount])*100
In SQL view it looks like this.

SELECT ([query_2]![PT])/([CCount]![CCount])*100 AS test
FROM CCount, PT;

and got a result in the form of ##.#########

Now all I need to do is figure out how to round that answer to 2 decimal places.

The main thing that helped me was noticing that little :<- placed in design view that indicates an expression will follow.

I would still like to figure out how to do this in VBA though. I have not yet searched for that kind of information yet... If I can figure that part out I will post that information here as a reply.

Thank you very much!

EDIT

ok did a quick search on the round function .. no problem at all there
round(Expr, NumDecimalPlaces)
Ez as pie!

I'll worry bout duplicating it in VBA later. Thank you again for your help.

 

[pbaldy]

The very basics of creating a function:

http://www.baldyweb.com/Function.htm

Look at the Round() function to round your answer to 2 decimals.

 

[Konai]

Thank you again!! I'm better with making macros in VBA for excel than access so this stuff is kinda new to me.
This reference looks very helpful.

The very basics of creating a function:

http://www.baldyweb.com/Function.htm

Look at the Round() function to round your answer to 2 decimals.

 

[pbaldy]

This reference looks very helpful.

I'm certainly trying to make it helpful, so any thoughts on improvements would be appreciated. That's sort of a first draft.

 

[Konai]

I've always found that the most simplified examples help the most. If I had the time I would make such a site for myself (but available to all like you have done) and just put the most simplified example of each thing I needed to know how to do on there.

For calculated fields it was not difficult at all once I understood how to make one, but it was not very obvious to me how to do it until I noticed the colon used in someone else's post. Even on microsoft's own website it was not very clear how to make a calculated field .. though it could be that they are calling it something else.

For my own reference I'll just put something like this
EXAMPLE: CALCULATED FIELDS
In design view reference query fields like this.
Adding two fields.
VARNAME: [queryname]![queryfield] + [otherqueryname]![queryfield]

and then something similar for Visual Basic.

In this case though... It looks like the above method was the easier way to me.