Is there a way to calculate % in a query?

[michelle copper]

I have two queries. One is answer my question as to the number of calls and the other one answer to my question number of visits. Both of these queries give me a TOTAL column of the number of calls and visits with respect to the Fiscal Quarters.


Now my question is: Is it possible to create a new query with %? Taking the results from those two queries?

[Rabbit]

Yes. You can join the two queries by the fiscal year and then divide one number by the other for whatever percent you're trying to calculate.

[michelle copper]

Thanks Rabbit.
I can't seem to join two queries because the query wizard only me to choose One query when creating a new one.

[NeoPa]

Remember, percentages are stored as simple fractions, so be careful you understand that 50% == 0.5 and 50% =/= 50. The format of the number displayed has no bearing on the actual value itself.

[NeoPa]

Michelle Copper:
I can't seem to join two queries because the query wizard only me to choose One query when creating a new one.

In the Query menu there is an option to 'Show Tables', from which you can select queries as well as tables.

[michelle copper]

so you mean I choose the two queries using 'show tables' and add the field that I want to take the average? I tried that but it did not work. Do I simply choose the field I want and add a new column for %?


How do I create % column?

[NeoPa]

If you want the percentage that field 1 of query A is of field 2 of query B then you can add a field in the new query that is a formula :

=IIf([Query 2].[Field B] = 0, 0, [Query 1].[Field A] / [Query 2].[Field B])

NB. the IIf() call is there to ensure you never divide by zero.

Also note that this will only show as a percentage if you set the format of the field appropriately. Otherwise it will simply show as a decimal value that is 100th of the value of a percentage (EG. If the result is 34% it will show simply as 0.34 unless the format property is set).

[michelle copper]

Thanks NeoPa.

Access is giving me error and says it does not recognize the field as a valid field name or expression.

This is what I put under FIELD in new query:

=IIf([qry3].[TotalOfMetricsID] = 0, 0, [qry4].[ TotalOfMetricsID] / [qry3].[TotalOfMetricsID]) 

From the two queries:

qry3
FY
Quarter
TotalOfMetricsID
Call
Visit



qry4
FY
Quarter
TotalOfMetricsID
Yes

[NeoPa]

Can you post exactly what it says please Michelle.

Also post the SQL of the query if you can (See below for instructions on how to get this from your query).

To extract the SQL from a QueryDef (saved Access query) it is only necessary to view the query in SQL View. This shows the underlying SQL for the QueryDef and is text (See Access QueryDefs Mis-save Subquery SQL for problems with SubQueries). When a QueryDef is open (either in Design View or Datasheet View) it is possible to switch to the SQL View simply by selecting View \ SQL View from the menu.

From here it is simple to Copy & Paste it to wherever you need it.

[michelle copper]

sure.

qry3 SQL View says

TRANSFORM Count(qryExportMetrics.MetricsID) AS CountOfMetricsID
SELECT qryExportMetrics.FY, qryExportMetrics.Quarter, Count(qryExportMetrics.MetricsID) AS TotalOfMetricsID
FROM qryExportMetrics
WHERE (((qryExportMetrics.Activity)="Call" Or (qryExportMetrics.Activity)="Visit"))
GROUP BY qryExportMetrics.FY, qryExportMetrics.Quarter
ORDER BY qryExportMetrics.FY, qryExportMetrics.Quarter
PIVOT qryExportMetrics.Activity;
 

qry4 SQL View says

TRANSFORM Count(qryExportMetrics.MetricsID) AS CountOfMetricsID
SELECT qryExportMetrics.FY, qryExportMetrics.Quarter, Count(qryExportMetrics.MetricsID) AS TotalOfMetricsID
FROM qryExportMetrics
WHERE (((qryExportMetrics.[Would have called 911?])="Yes"))
GROUP BY qryExportMetrics.FY, qryExportMetrics.Quarter
ORDER BY qryExportMetrics.FY, qryExportMetrics.Quarter
PIVOT qryExportMetrics.[Would have called 911?];
 

and as for the error message. It says "The Microsoft Office Access database engine does not recognize '[qry4].[TotalOfMetricsID]' as a valid field name or expression"

[NeoPa]

I was asking about the SQL for the query that gave you the error message, but by a happy coincidence, the problem is illustrated perfectly by the SQL for [qry4].

You refer to [qry4].[TotalOfMetricsID] (from the error message) yet I suspect [qry4] has no such field. Run qry4 and see what field names are available. I don't use crosstab queries much myself and can't easily test this for myself, so let me know if [TotalOfMetricsID] turns up. I suspect you may need to use [CountOfMetricsID] instead.

[michelle copper]

I do have TotalOfMetricsID in my qry4

I have attached an image of how my qry 4 looks. qry3 looks similar to that.


the new query SQL view is

SELECT IIf([qry3].[TotalOfMetricsID]=0,0,[qry4].[ TotalOfMetricsID]/[qry3].[TotalOfMetricsID]) AS Expr1
FROM qry3, qry4;

[Rabbit]

You have a space in your [qry4].[ TotalOfMetricsID] qualification.

[NeoPa]

That suggests the error message posted should have been :

The Microsoft Office Access database engine does not recognize '[qry4].[ TotalOfMetricsID]' as a valid field name or expression

This would have given us the answer back then. Never mind. Remember though, that in coding, and database coding in particular, attention to detail is critical. It's no accident that the best coders are always highly fussy over details.

[michelle copper]

I just tried and its giving me some results now. What I don't understand is why is it showing 132 record instead of 12 record? :S

I have attached an image here and I hope someone can figure out where I went wrong.
Thanks

[NeoPa]

That one's easy from the previous SQL posted. You have the two queries included without any type of JOIN. See SQL JOINs. Pay particular attention to the part about Cartesian Products (which is what you have here).

[Rabbit]

Just a heads up, you seem to be missing a quarter in one of your queries. Which means you will have to use an outer join of some sort. And you will need to handle the null in your calculation.

[michelle copper]

I just tried using this

SELECT Quarter,TotalOfMetricsID,Quarter,TotalOfMetricsID
FROM qry4 LEFT JOIN qry3
  ON qry4.Quarter=qry3.Quarter 

but it is giving me an error (see insert image "errormsg" )

The reason being is because I have the same field in both the queries.
I cannot change the name as they are both from the same table.

I have attached another image of my two queries.

:S

** Edit **

[Rabbit]

In your SELECT clause, you have to tell it which of the two to use.

[NeoPa]

When any field has an ambiguous source (multiple sources contain the same named field) then the references must be qualified to include the source names :

SELECT qry3.Quarter
     , qry3.TotalOfMetricsID
     , qry4.Quarter
     , qry4.TotalOfMetricsID
FROM   qry4 LEFT JOIN qry3
  ON   qry4.Quarter=qry3.Quarter

[michelle copper]

Thanks Rabbit and NeoPa!

I used the codes that were in post#20 and I added Fiscal Year along with it and I added a new column at the end for calculating averages.

it does give the results but however, I would like it to have like to have all the QUARTER 1 to be grouped together to whichever year it belongs.

I have attached an image here to show the output. As you can see, it is very messy and confusing.

what should I do to have them group together?

** Edit **

[NeoPa]

Use a GROUP BY clause :

SELECT   qry3.Quarter
       , qry3.TotalOfMetricsID
       , qry4.TotalOfMetricsID
FROM     qry4 LEFT JOIN qry3
  ON     qry4.Quarter=qry3.Quarter
GROUP BY qry3.Quarter

[NeoPa]

Unless your explanation is somewhat misleading and you really want the records sorted by year then quarter (rather than grouped), which would be to use an ORDER BY clause instead :

SELECT   qry3.FY
       , qry3.Quarter
       , qry3.TotalOfMetricsID
       , qry4.TotalOfMetricsID
FROM     qry4 LEFT JOIN qry3
  ON     qry4.Quarter=qry3.Quarter
ORDER BY qry3.FY
       , qry3.Quarter

This can also be managed in Design View of the query by specifying which fields to sort on in the grid.

[michelle copper]

Thanks NeoPa !
Post #22 is giving me error. See attachment #post22

Post #23 - I have tried this code but it is still giving me the similar results to what I have done before.

If you see my qry3 and qry4 there is only ONE row with Quarter 1 of 2008 and so on. That is how I would like when I join qry3 and qry4 together.
Sorry for creating all these confusion.

** Edit **

[NeoPa]

You're right about post #22. It's rubbish. I must have sent it in a bit of a rush. Let's focus on post #23. I'll just fix your post to show the image (you can do this too you know) so I can work with it but I'm leaving shortly so I'll have to address it properly later.

[michelle copper]

Thanks NeoPa! I will wait for your reply

[NeoPa]

Michelle:
If you see my qry3 and qry4 there is only ONE row with Quarter 1 of 2008 and so on. That is how I would like when I join qry3 and qry4 together.

I'm looking at the results of your new query and I don't understand how the quoted statement makes sense (as there are clearly multiple records with the same [Quarter] and [FY] values).

If you want multiple records grouped together then you have to choose how those items that aren't part of the grouping (The two [TotalOfMetricsID] fields) should be shown or aggregated (Only grouped fields are guaranteed to be the same across all records within a particular group, so you must choose a way to show the other data. An example would be to show the Sum of their values. Another to show the Max of them).

[michelle copper]

Sorry NeoPa. I was actually referring to my original qry3 and qry 4 (before they were join together).

As for using Max , I tried and it shows what I wanted but only the my 4th column seems weird. I have attached an image here again to show what I mean. The whole column is now "299".
And this is my SQL code

SELECT qry3.FY, qry3.Quarter, Max(qry3.TotalOfMetricsID) AS MaxOfTotalOfMetricsID, Max(qry4.TotalOfMetricsID) AS MaxOfTotalOfMetricsID1
FROM qry3, qry4
GROUP BY qry3.FY, qry3.Quarter;

[michelle copper]

how do I make my image viewable?

[Rabbit]

In post 23, you forgot to join FY to FY.

[michelle copper]

Thanks Rabbit..
I just tried but it did not work.
Do I simply add another line to the code? Like this:

SELECT   qry3.FY
       , qry3.Quarter
       , qry3.TotalOfMetricsID
       , qry4.TotalOfMetricsID
FROM     qry4 LEFT JOIN qry3
  ON     qry4.Quarter=qry3.Quarter
  ON    qry4.FY=qry3.FY
ORDER BY qry3.FY
       , qry3.Quarter
 

the error message says "Syntax error (missing operator) in query expression 'qry.Quarter=qry3.Quarter' ON qry4.FY=qry3.F'

[michelle copper]

I tried this as well. Still not working

SELECT   qry3.FY
       , qry3.Quarter
       , qry3.TotalOfMetricsID
       , qry4.TotalOfMetricsID
FROM     qry4 LEFT JOIN qry3
  ON     qry4.Quarter=qry3.Quarter,
qry4.FY=qry3.FY
ORDER BY qry3.FY
       , qry3.Quarter
 
 

[Rabbit]

Change the second ON to an AND.

[michelle copper]

AWESOME!!! IT FINALLY WORKED!!!! PHEW!!

Thanks Rabbit and Thanks NeoPa! You guys are great!

[michelle copper]

Okay.. I am facing problem again. so I renamed my queries to the names that I have wanted following the codes from above and somehow, very strangely, it does not work :S

Now where did I go wrong? I simply replaced the name using the same codes

qry3 = casesReceiving
qry4 = casesSeen

SELECT   casesReceiving.FY
       , casesReceiving.Quarter
       , casesReceiving.TotalOfMetricsID
       , casesSeen.TotalOfMetricsID
 
FROM     casesSeen LEFT JOIN casesReceiving
  ON     casesSeen.Quarter=casesReceiving.Quarter,
and casesSeen.FY=casesReceiving.FY
ORDER BY casesReceiving.FY
       , casesReceiving.Quarter
 

The error message says "JOIN expression not supported"

[Rabbit]

You have a comma at the end of line 7.

[michelle copper]

Thanks.
I just removed the comma and changed them to

SELECT   casesReceiving.FY
       , casesReceiving.Quarter
       , casesReceiving.TotalOfMetricsID
       , casesSeen.TotalOfMetricsID
 
FROM     casesSeen LEFT JOIN casesReceiving
  ON     casesSeen.Quarter=casesReceiving.Quarter
and casesSeen.FY=casesReceiving.FY
ORDER BY casesReceiving.FY
       , casesReceiving.Quarter
 
 

now it is saying that "The microsoft office Access database engine does not recognize 'casesReceiving.TotalOfMetricsID' as a valid field and name or expression"

[michelle copper]

okay..Never mind.
I found the problem!
Sorry~~
Thanks!

[michelle copper]

Now I can SMILE again!

[Rabbit]

I'm glad you got it worked out. Good luck!

[NeoPa]

Michelle:
How do I make my image viewable?

Much to catch up on in this thread. You guys have been busy while I was away busy (as it were).

Use the [ IMGNOTHUMB ] tags. I've added spaces in there to illustrate (as it doesn't work when speces are included but it does show what I mean (See BB Code for more on available BB Code tags).

[ IMGNOTHUMB ]http://bytes.com/attachments/attachment/5378d1314881693/groupby.jpg[ /IMGNOTHUMB ]

Getting the link to enter is easy when the attachment is posted. Simply right-click on the link and select Copy Link Location.

[NeoPa]

It seems (now I've caught up with the thread fully) that you now have it working as an ordered list, but the grouping is no longer used. Is that because you decided it was unnecessary after all, or because it proved too difficult to manage?

I'm just curious. I can leave alone if you're happy, but if you wanted to progress it to handle that I'm sure we could help further.

[michelle copper]

I do not know myself either.:D I just kept following what Rabbit suggested and I was managed to get the right query.


This is how it looks now.

Thanks again NeoPa!

[michelle copper]

okay.. seems like I did not get the image viewable right *sigh*

[michelle copper]

Okay. I got the image up and working!

[NeoPa]

8-) I'm happy if you're happy.