Financial Query

[Luke]

Ok so my boss wants me to make a query that has Financial Info from basically any year. This includes Revenue, Gross Margin, and EBITDA. So far, I have created a Financial table with 6 fields:

Transaction# (primary key), Autonumber
EntityID, Number


Year, Number
Revenue, Currency
GrossMargin, Currency
EBITDA, Currency

"EntityID" is my primary key in my Entity Table, where I have a relationship with the Financial Table and "enforced referential integrity" so that I can edit info in either table.

I have also made 3 crosstab queries, "qryRevenue, qryGrossMargin, and qryEBITDA". Each query's design includes:

EntityID
EntityName, Total: Group By/ Crosstab:Row Heading/ Sort:Ascending
Year, Total: Group By/ Crosstab:Column Heading
Revenue (or GrossMargin/EBITDA), Total:Sum/ Crosstab:Value
Expression: Total Revenue, Total:Sum/ Crosstab:Row Heading

I want to link these 3 crosstab queries together so I can create a FORM that looks something like this:

Entity Name
2010 2011 2012 2013
Revenue: $$$$ $$$$ $$$$ $$$$
Gross Margin: $$$$ $$$$ $$$$ $$$$
EBITDA: $$$$ $$$$ $$$$ $$$$

I'm guessing that I have to make either another CROSSTAB query between the 3 queries or more likely a UNION query between them. My question is how the hell can I make a form that includes these 3 queries but only displays the entity name and the year once??? I've been trying to figure this out for weeks now but still can't. If anybody has any suggestions feel free to share, thanks a lot.

-Luke

[Luke]

I've created a union query in SQL View that contains this:

SELECT "Revenue" As Category, [Year], Revenue As Amount
FROM [tblFinancial]
UNION SELECT "GrossMargin", [Year], GrossMargin
FROM [tblFinancial]
UNION SELECT "EBITDA", [Year], EBITDA
FROM [tblFinancial];


The problem is how can I add the Entity Names into the SQL?

[TheShabz]

If each of the crosstabs returns 1 record per entity, and has fields for revenue type and the years you want, you can:

SELECT * FROM qryRevenue WHERE entityID = Forms!mainForm!cmbEntity
UNION
SELECT * FROM qryGrossMargin WHERE entityID = Forms!mainForm!cmbEntity
UNION
SELECT * FROM qryEBITDA WHERE entityID = Forms!mainForm!cmbEntity

[Luke]

I tried that but for some reason it didn't work. Each of my corsstabs returns 1 record per entity and has fields for the years I want. I don't know what you meant by fields for revenue type I want?

[TheShabz]

cmbEntity would be what I recall was the name of the combobox on the form I created. If it's something else, replace it. Make sure the form is open and an entity is selected. You dont need to display the entity name as it is already displayed in the combobox. This would simply by another subform on the main form or on another form.

[Luke]

Yea the combobox is still called cmbEntity. The problem I'm having now is that it's saying it doesn't recognize "entityID" as a valid field name or expression. I tried changing it to "EntityID" but the same pop-up box appeared.

[TheShabz]

What are the fields that the crosstab queries are returning?

[Luke]

Even though I included "EntityID" in the crosstab queries in design view, they're just returning "<>" in datasheet view for the field and not actually showing the EntityID field or its numbers.

[Luke]

It's returning the fields of EntityName, 2009,2010,2011,2012, and my Total Revenue/GM/EBITDA Expressions