Financial Form

[Luke]

My boss wants me to create a financial table in access that includes Revenue, EBITDA, and Gross Margin. He wants it to look something like this:




Company Name

2010 2011 2012 2013

Revenue $$$ $$$ $$$ $$$
Gross Margin
EBITDA

I have tried for weeks to do this but the problem is my primary key. The only way I'm able to make my form look like this is if I have duplicate values for my company name and don't have a primary key. I need to have a primary key so that I can add/edit records at any time so that it is saved in all tables, queries, forms, reports, etc. Is there any way I can make a financial statement like this using access? Thanks.

-Luke

[June7]

Did you try crosstab query? Use the Access query wizard.

Show sample of raw data.

[Luke]

Did you try crosstab query? Use the Access query wizard.Show sample of raw data.

Thanks for the tip, a crosstab query would probably be the solution. However, after fooling around with the data I'm still stuck on how to make this crosstab query appear the way I want it to. Do you have any suggestions as to what information I should put as row headings and which info I should put as column headings? Thanks a lot.

-Luke

[June7]

Select the date field as Column headings and should be offered options for weekly, monthly, quarterly, yearly. Revenue, Gross Margin
EBITDA are fields in table or values of one field? Need a field to perform statistical calcs on.

Show the source table data structure.

[Luke]

Select the date field as Column headings and should be offered options for weekly, monthly, quarterly, yearly. Revenue, Gross Margin
EBITDA are fields in table or values of one field? Need a field to perform statistical calcs on.

Show the source table data structure.

Ok thanks a lot. Revenue, Gross Margin, and EBITDA are all currently fields in the table but do you suggest I make them values of one field? Thanks again.

[June7]

Yes, for a crosstab, structure needs to be:
Category DateEnter Amount

Can redesign tables or use UNION query on your current table to get this structure:
SELECT "Revenue" As Category, [Date], Revenue As Amount FROM tablename
UNION SELECT "GrossMargin", [Date], GrossMargin FROM tablename
UNION SELECT "EBITDA", [Date], EBITDA FROM tablename;

Then do crosstab on this UNION.

There is no designer or wizard for UNION, must type into the SQL View editor of query designer.

[Luke]

Thanks a lot June7, it's somewhat starting to work. I typed in the following in SQL View:

SELECT "Revenue" As Category, [2009],[2010],[2011],[2012], Revenue As Amount FROM [Financial Info]
UNION SELECT "GrossMargin", [2009],[2010],[2011],[2012], GrossMargin
FROM [Financial Info]
UNION SELECT "EBITDA", [2009],[2010],[2011],[2012], EBITDA
FROM [Financial Info];

However, my parameter values are "Revenue, Gross Margin, and EBITDA". I was wondering how I could add the name of each company to the query and have the company name be my parameter value instead. Thanks again.

-Luke

[June7]

Not making sense to me. Why are the years in the UNION query? I need sample of your source table structure and data.

What do you mean by 'parameter values'?

[Luke]

My boss wants the years 2010-2013 (roughly) for the revenue, EBITDA, and Gross Margin, not the exact dates. Access won't let me type in just the year so I have fields with each year. I couldn't think of any other way to do it, do you have any suggestions? Would you like to see an attachment of my database? And when I say "parameter values" I mean when the pop up box appears and says "enter paramter value" for revenue, gross margin, and EBITDA when I open up the query. Thanks a lot.
-Luke

[Luke]

When I type this in the SQL for the Union query: SELECT "Revenue" As Category, [Date], Revenue As Amount FROM tablename
UNION SELECT "GrossMargin", [Date], GrossMargin FROM tablename
UNION SELECT "EBITDA", [Date], EBITDA FROM tablename;

This is how it looks when I run it:

Category Date Amount
EBITDA
Gross Margin
Revenue


The Date and Amount columns are blank and I want to have the entity names for 2010-2013. Are you saying that I should run a crosstab query with this Union query? And if so, how should I go about doing this without creating duplicate values if the entity is my primary key? Thank you.

[June7]

Am going in circles here, I need sample of source data table to clarify the setup. A few lines in post or attaching the project would do, or at least just the necessary table, or a spreadsheet.

[Luke]

My database was too large to attach so I made a new database and copy/pasted the table and union query. I don't have exact numbers yet for the dates revenue, ebitda, and gross margin so I've just been typing in random ones to play around with it. I just want to have a form that has each entity name and it's revenue, ebitda, and gross margin from 2010-2013:

Company Name

2010 2011 2012 2013

Revenue $$$ $$$ $$$ $$$
Gross Margin
EBITDA

[June7]

The Date field needs to be a Date/Time datatype, not Number.

The field Gross Margin has a space so needs to be [Gross Margin] in the Union query.

Then do a crosstab query:
TRANSFORM Sum(qryFinancialInfo.Amount) AS SumOfAmount
SELECT qryFinancialInfo.Category, Sum(qryFinancialInfo.Amount) AS [TotalOfAmount]
FROM qryFinancialInfo
GROUP BY qryFinancialInfo.Category
PIVOT Format([Date],"yyyy");

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names. Also, don't use reserved words as names, instead of Date use DateEnter, DateOrder, DateOut, etc. If you do, must enclose in [].

[Luke]

Thanks for your advice, I'm gonna try that. I made a new database this afternoon and It's been working a little more efficient than my last one. 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???

[June7]

Back up. Follow my advice. One Union query, one crosstab query, build report.

Works best if there are no empty cells.

Problem with building reports on crosstab query creating fields based on date value is the dataset is 'moving' and will have to modify the report as data is added. So when you add 2014 data will have to add textboxes to show that year.

Wait, you need breakdown by Entity? Let me look at that some more.

Okay, still only 2 queries.

Union query:
SELECT Entity, "Revenue" As Category, [Date], Revenue As Amount
FROM [Financial Info]
UNION SELECT Entity, "GrossMargin", [Date], [Gross Margin]
FROM [Financial Info]
UNION SELECT Entity, "EBITDA", [Date], EBITDA
FROM [Financial Info];

Crosstab query:
TRANSFORM Sum(qryFinancialInfo.[Amount]) AS SumOfAmount
SELECT qryFinancialInfo.[Entity], qryFinancialInfo.[Category], Sum(qryFinancialInfo.[Amount]) AS [Total Of Amount]
FROM qryFinancialInfo
GROUP BY qryFinancialInfo.[Entity], qryFinancialInfo.[Category]
PIVOT Format([Date],"yyyy");