Add queries to new crosstab every month

[Guest]

Each month I import data that includes the finances by account numbers for
200 stores.

STORE AcctID ACCT DESC Profit/Expenses
0001 1001 Sports Dept 50.00
0001 1001 Clothes Dept 10.00
0001 3001 Utilities 5.00

I then create a CrossTable by Acct Desc:

SPORTS DEPT CLOTHES DEPT UTILITIES

Store 001 50.00 10.00
5.00
Store 003 65.00 4.00
2.00
Store 004

Additional Calculations are inclued in the CrossTable query such as:
TOTAL SALES: (Sports+Clothes)

How can I attach the monthly calculations (that remain the same each month)
with the forever changing crosstab query? Store List may change.

 

[Duane Hookom]

Stores changing shouldn't make any difference since Store is a Row Heading.
Are you suggesting that you don't know how to handle different column
headings each month?

 

[Guest]

Duane,
There are about 12 existing calculations that are included in several
reports every month. How do I add the existing calculations to the new
Crosstab query every month. One option would be to create the calculations
using the crosstab columnn names in the report (TotProd: prod1+prod2, Avg:
TotProd/#Stores), however, I want the option to store the calculations in
the query so I can always access the calculations as new reports are produced.

 

[Duane Hookom]

You need to be more specific about you calcs. Row totals are easy to set as
Row Headings. You should be able to get a count of the number of stores
using a subquery that counts the number of unique stores.

 

[Guest]

Duane,
Sometimes I'm not very good at stating my question so I'll try rephasing the
question.

1. The imported data is a spreadsheet consisting of many records for each
store and the store's associated ACCOUNTS.
2. I turn this spreadsheet into a CrossTab query using the ACCOUNT
description as the Column heading.
3. This table is replaced at the end of every month and a new Crosstab
query is created every month.
4. The additional calculations, however, use the column headings in their
calculations and remain the same every month.
Account Column Head [Fuel] + Account Column Head [Utilities] AS TotExpenses.
Account Column Head [BECPG]- Column Head [BECPGnoExt] AS Diff.
5. SO... How do I include the recurring calculations with the New crosstab
data every month without retyping the calculations in the New Crosstab query?

6. Let's forget about the store count right now. Is my question making
sense yet?

 

[Duane Hookom]

I assume the column names will be the same each month but might change a
little over time. I would create a table of ACCOUNTS and add fields that
identify groupings:

tblAccounts
====================
AccountName
Expense (field with 1 for expense
accounts or 0 if not)

You can add this table to your crosstab and join the accounts fields. Add a
column to your crosstab like:

Field: Expenses: ([Profit/Expenses]) * [Expense]
Total: Sum
Crosstab: Row Heading

You can get as fancy as you want with these.

--
Duane Hookom
MS Access MVP
--

Duane,
Sometimes I'm not very good at stating my question so I'll try rephasing
the
question.

1. The imported data is a spreadsheet consisting of many records for each
store and the store's associated ACCOUNTS.
2. I turn this spreadsheet into a CrossTab query using the ACCOUNT
description as the Column heading.
3. This table is replaced at the end of every month and a new Crosstab
query is created every month.
4. The additional calculations, however, use the column headings in their
calculations and remain the same every month.
Account Column Head [Fuel] + Account Column Head [Utilities] AS
TotExpenses.
Account Column Head [BECPG]- Column Head [BECPGnoExt] AS Diff.
5. SO... How do I include the recurring calculations with the New
crosstab
data every month without retyping the calculations in the New Crosstab
query?

6. Let's forget about the store count right now. Is my question making
sense yet?

You need to be more specific about you calcs. Row totals are easy to set
as
Row Headings. You should be able to get a count of the number of stores
using a subquery that counts the number of unique stores.