Crosstab query calculations

[DHIGHTO]

Hey guys,

I have two crosstab queries that I'm trying to calculate from. The first query, called setups, fields are AR#, setup month, count of tfile_setup. The AR# is row heading, the setup month is column heading, and count of tfile_setup is the value.

The query looks like this

AR#	1/1/2014	2/1/2014	3/1/2014	4/1/2014
430	1	2	3	1
434	1	1	1	4
435	1	4	2	3
437	2	1	5	4
438	3	6	1	2

The other query called funded has fields named AR#, setup date, and count of tfile_funded. The AR# is row heading, the setup month is column heading, and count of tfile_funded is the value.

The query looks like this

AR#	1/1/2014	2/1/2014	3/1/2014	4/1/2014
430	4	6	5	3
434	9	9	8	3
435	5	8	8	4
447	10	12	11	7
438	5	9	4	4

I'm trying to write an expression that takes the data from the second query, funded, and divide it by the second query, setups. For example, for AR# 430 in 1/1/2014, I want to divide 1 by 4, and do this for all the records. Is this possible?

Thanks!

[June7]

Your last descriptive statement has me confused about which query is which and what divides what (they can't both be the 'second' query).

Did you try building another query that joins the two CROSSTABS? Then you will have to constuct expressions that calculate the two fields. 12 months?

I expect this will not work well because the field names are dynamic. Will have to rebuild the query at lease once a year.

Building stable reports to run perpetually based on CROSSTAB is not easy. Review http://allenbrowne.com/ser-67.html

[DHIGHTO]

I meant to say the second query is divided by the first query. My bad. I tried linking the queries together and writing the query as [funded].[countoftfile_funded]/[setups].[countoftfile_setups]. This generated a lot of results and what ended up happening was that [setup month] from [funded] was divided by every month [setup].[setup month]. So I had 4 values for 1/1/2014 instead of 1. The only was I got the right values was by limiting the criteria by month (1/1/2014) for all queries. Unfortunetly, this is time consuming, and there are easier ways around it.

[June7]

I don't understand the query and expressions you describe.

The query joined the two CROSSTABS on the AR# field?

The field names are the dates so the expressions would be like:

[funded].[1/1/2014] / [setup].[1/1/2014]

[DHIGHTO]

I took off the cross tabs to make it easier. I'll fix the format later. Right now this is the expression I'm using [funded].[countoftfile_funded]/[setups].[countoftfile_setups].

My results are coming from 1/1/2014 (4) from funded being divided by 1/1/2014 (1), 2/1/2014 (2), 3/1/2014 (3), and 4/1/2014 (1) from setups.

AR# EXP1 SETUP MONTH
430 4 1/1/2014
430 2 1/1/2014
430 1.33 1/1/2014
430 4 1/1/2014

I'm not sure how to correct this.

[DHIGHTO]

Well, I think my problem is my queries are build on other queries. The only way I can get some values is to limit the setup month on fund, setup, and the newest query (fund/setup) to one month.

Anyway, I'm giving up.

[June7]

Would have to do two aggregate queries first that summarize the Exp1 data grouped by AR# and date. Then join the two aggregate queries to a master dataset of all combinations of AR#'s and dates by compound join on the AR# and date fields. Creating the master dataset would be a Cartesian query - a query without JOIN clause.

BTW, advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be AR_Num or ARNum.

[DHIGHTO]

I figure it out. I created two relationships between the two queries. One for AR# and one for setup month. I'm not sure why this worked. Can you explain? Anyway, thanks for the help and tips.

[June7]

I think you describe a compound join. The combination of AR# and month defines unique record in both queries and produces a 1-to-1 relationship. What I was trying to describe in my previous post.