Accounting

[Andre Adams]

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[KARL DEWEY]

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

 

[Andre Adams]

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[KARL DEWEY]

I need to first of all create a break in the page to separate my Income
from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[Andre Adams]

Karl,

You will not believe how fantastic this report is designed! Thanks for all
of your help. There is one more thing though. You don't have to answer it,
I can post it to someone else on this forum. I don't want to monopolize your
time. But, I'll pose it to you just in case you have a kind heart to help a
fellow Access user in need.

There is this category called "firmwide" on my report. It's a column
header, but, the total number needs to be divided equally into the other 7
categories. So if "firmwide" had $100, every department would need to be
allocated an additional $14.28. How would that formula look in my query
mode? Or, can it be done in the Reports?

from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little


:

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[KARL DEWEY]

There is this category called "firmwide" on my report.
I am confused. I thought that categories were expenses and income. I
thought your columns were departments - derived from your Job ID.
--
KARL DEWEY
Build a little - Test a little

Karl,

You will not believe how fantastic this report is designed! Thanks for all
of your help. There is one more thing though. You don't have to answer it,
I can post it to someone else on this forum. I don't want to monopolize your
time. But, I'll pose it to you just in case you have a kind heart to help a
fellow Access user in need.

There is this category called "firmwide" on my report. It's a column
header, but, the total number needs to be divided equally into the other 7
categories. So if "firmwide" had $100, every department would need to be
allocated an additional $14.28. How would that formula look in my query
mode? Or, can it be done in the Reports?

I need to first of all create a break in the page to separate my Income

from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.

Second of all I need to have totals for my columns and rows.

In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.
:

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little


:

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[Andre Adams]

They are. My apologies, I must have confused them. One of the Column
headings is "Firmwide". I called it a category by mistake. But that
"Firmwide" Column Income and Expenses related to it that needs to be divided
among the other Column headings. Hope this helps!

I am confused. I thought that categories were expenses and income. I
thought your columns were departments - derived from your Job ID.
--
KARL DEWEY
Build a little - Test a little

Karl,

You will not believe how fantastic this report is designed! Thanks for all
of your help. There is one more thing though. You don't have to answer it,
I can post it to someone else on this forum. I don't want to monopolize your
time. But, I'll pose it to you just in case you have a kind heart to help a
fellow Access user in need.

There is this category called "firmwide" on my report. It's a column
header, but, the total number needs to be divided equally into the other 7
categories. So if "firmwide" had $100, every department would need to be
allocated an additional $14.28. How would that formula look in my query
mode? Or, can it be done in the Reports?

I need to first of all create a break in the page to separate my Income
from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.
Second of all I need to have totals for my columns and rows.
In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little


:

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.
:

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little


:

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[KARL DEWEY]

I do not think you can do it in the query but there is another way. In your
report put your department text boxes but not "Firmwide."

In the footer put another set of text boxes for each column and for source
use ---
=Sum([Firmwide])/6 that is if you have 6 departments. And again
change the Running Sum property to Yes.

--
KARL DEWEY
Build a little - Test a little

They are. My apologies, I must have confused them. One of the Column
headings is "Firmwide". I called it a category by mistake. But that
"Firmwide" Column Income and Expenses related to it that needs to be divided
among the other Column headings. Hope this helps!

There is this category called "firmwide" on my report.

I am confused. I thought that categories were expenses and income. I
thought your columns were departments - derived from your Job ID.
--
KARL DEWEY
Build a little - Test a little

Karl,

You will not believe how fantastic this report is designed! Thanks for all
of your help. There is one more thing though. You don't have to answer it,
I can post it to someone else on this forum. I don't want to monopolize your
time. But, I'll pose it to you just in case you have a kind heart to help a
fellow Access user in need.

There is this category called "firmwide" on my report. It's a column
header, but, the total number needs to be divided equally into the other 7
categories. So if "firmwide" had $100, every department would need to be
allocated an additional $14.28. How would that formula look in my query
mode? Or, can it be done in the Reports?

:

I need to first of all create a break in the page to separate my Income
from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.
Second of all I need to have totals for my columns and rows.
In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little


:

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.
:

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little


:

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?

 

[Andre Adams]

This has bee the most helpful discussion that I've been apart of thus far.
Thanks alot Karl, I really appreciate it!

Andre Adams

I do not think you can do it in the query but there is another way. In your
report put your department text boxes but not "Firmwide."

In the footer put another set of text boxes for each column and for source
use ---
=Sum([Firmwide])/6 that is if you have 6 departments. And again
change the Running Sum property to Yes.

--
KARL DEWEY
Build a little - Test a little

They are. My apologies, I must have confused them. One of the Column
headings is "Firmwide". I called it a category by mistake. But that
"Firmwide" Column Income and Expenses related to it that needs to be divided
among the other Column headings. Hope this helps!

There is this category called "firmwide" on my report.
I am confused. I thought that categories were expenses and income. I
thought your columns were departments - derived from your Job ID.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

You will not believe how fantastic this report is designed! Thanks for all
of your help. There is one more thing though. You don't have to answer it,
I can post it to someone else on this forum. I don't want to monopolize your
time. But, I'll pose it to you just in case you have a kind heart to help a
fellow Access user in need.

There is this category called "firmwide" on my report. It's a column
header, but, the total number needs to be divided equally into the other 7
categories. So if "firmwide" had $100, every department would need to be
allocated an additional $14.28. How would that formula look in my query
mode? Or, can it be done in the Reports?

:

I need to first of all create a break in the page to separate my Income
from my Expenses.
Create a report and in design view click on menu VIEW - Sorting and
Grouping. Type in Category in the window. Set Group Footer to Yes.
Click on VIEW - Properties. Click on the footer label and change Force New
Page from None to Before Group.
Second of all I need to have totals for my columns and rows.
In the footer put text boxes for each column and for source use ---
=Sum([YourColumnName])
Change the Running Sum property to Yes.
--
KARL DEWEY
Build a little - Test a little


:

This is great Karl. Absolutely fantastic!

I have one more question for you. Given the crosstab query, I need to first
of all create a break in the page to separate my Income from my Expenses.
Second of all I need to have totals for my columns and rows.

Can this be done?

Thanks again.
:

Use a crosstab query ---
TRANSFORM Sum(Accounting.[Transaction Amount]) AS [SumOfTransaction Amount]
SELECT Accounting.Category, Accounting.[Account Description],
Sum(Accounting.[Transaction Amount]) AS [Total Of Transaction Amount]
FROM Accounting
GROUP BY Accounting.Category, Accounting.[Account Description]
PIVOT Accounting.[Job ID];

--
KARL DEWEY
Build a little - Test a little


:

Hey guys,

I have a toughie here.

I'm creating a database for my Accounting Department. The reporting system
that they use now is all done manually and I would like to lighten the load
on them by incorporating Access into their everyday work. They've given me
to the first project that happens to be distributed weekly to all the
executive officers. This report includes and income and expense breakdown
for every department and totals each column and row. So, this is what they
want.
Department Department Department
Total:
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Income Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Expense Name 1.00 1.00 1.00
3.00
Total: 4.00 4.00 4.00
12.00

I don't know if Access can handle structure like this. My fields are:

Account Description (Income and Expense Name)
Transaction Amount (1.00)
Job ID (Department)
Category (Income/Expense)

If I recieve this file everyday, can I tell Access to show me the data just
like the above picture?