Access-like reports in Excel

[rpmrob]

I would like to create reports in Excel using query data. The reports are required in Excel format so our clients can then later add their data and do what calculations they need to - simply exporting the report will not suffice as I would like to "lock" the data we are providing and provide columns/headings for their data entry).

The problem I am having is with variable-length reports (eg: reporting transactions from an account - each account will have a different number of transactions to list). The Access reporting interface is perfect for this, but I need to be able to do something similar in Excel.

Thanks for your help.

rob

 

[SkipVought]

Hi,

Is your query returning multiple accounts?

What exactly does Access do that you cannot do in Excel?

Skip,
[sub]
[red][/red]
[/sub]

 

[rpmrob]

In Access, I may have a table of transactions, all of which include the account they were charged to. In Access, it is very simple to create a report grouping on account number and listing all the transactions for that account. I would like to be able to do the same in Excel.

Please keep in mind I have little-none VB experience in Excel (only in Access), so if the solution is an obvious VB thing then that's cool - I just need to get going in the right direction.

Thanks for the quick response!

rob

 

[SkipVought]

There's no VB.

Please post your query SQL and an example of the resultset.

I do queries in Excel to Access or Oracle often and report by groups.

Show me an example of what you want to see.

Skip,
[sub]
[red][/red]
[/sub]

 

[rpmrob]

ok - here is trimmed version:

Query to Access database ('BUDGET 2004-2007' Table in Budget.mdb) from Excel (using Import External Data):

SELECT `BUDGET 2004-2007`.DEPT, `BUDGET 2004-2007`.OBJECT, `BUDGET 2004-2007`.`BUDGET 04-05`
FROM `s:\budget\Access MDB'S\BUDGET`.`BUDGET 2004-2007` `BUDGET 2004-2007`

Results in the following spreadsheet (sorry for the lack of formatting):

DEPT OBJECT BUDGET 04-05
TEACHING Academic Administrative Leaves 11000
TEACHING Benefits 2654
MAINTENANCE Licenses 25600
MAINTENANCE Equipment - Software 124
MAINTENANCE Salaries - Full-time 250317
MAINTENANCE Salaries - Part-time 9175
MAINTENANCE Benefits 57451
MAINTENANCE Networking - External 8500
MAINTENANCE Maintenance 52100
MAINTENANCE Equipment 18000
MAINTENANCE Equipment - Microcomputing 48000
MAINTENANCE Expense 55780
MAINTENANCE Cost Recovery - Rental -1400
MAINTENANCE Cost Recovery - Other -12907
MAINTENANCE Appropriation - Benefits 1254
MAINTENANCE Appropriation - Salaries 5842
MAINTENANCE Appropriation 1287
CONFERENCES Benefits 69584
CONFERENCES Salaries - Part-time 2564
CONFERENCES Travel (major) 57754
CONFERENCES Visiting Speakers 12850
CONFERENCES Appropriation 12568

I would like to produce separate spreadsheet reports for each department (DEPT) that list the OBJECTS for each, and then provide an extra column for the recipient to punch in their own numbers for next years predictions etc.

Thanks again (hope this is what you were looking for).

rob

 

[SkipVought]

You could have a separate query on each sheet. I STRONGLY recommend against that. There's extra maintenance and storage overhead with that approch.

I recommend using ONE sheet and a Combobox to select the DEPT you want to return data for.

The Combobox could be a Data/Validation - ListBox. A change in that cell could cause the query to run wih the new CRITERIA

SELECT
  `BUDGET 2004-2007`.DEPT
, `BUDGET 2004-2007`.OBJECT
, `BUDGET 2004-2007`.`BUDGET 04-05`

FROM `s:\budget\Access MDB'S\BUDGET`.`BUDGET 2004-2007` `BUDGET 2004-2007`
[b]
WHERE DEPT = ?[/b]

the WHERE DEPT = ? requires a PARAMETER (a dept). When you File/Return data to Excel, you can select the parameters Window and there select the CELL that will contain your DEPT.

On a separate sheet insert another querytable to the same database/table the returns a list of DEPT

SELECT Distinct
  DEPT

FROM `s:\budget\Access MDB'S\BUDGET`.`BUDGET 2004-2007`

Skip,
[sub]
[red][/red]
[/sub]

 

[rpmrob]

Thanks Skip - I will give this a try.

Much appreciated.

rob

 

[SkipVought]

...and, naturally, If I have a DEPT selected in the Parameter Link Cell, I don't need the DEPT returned in the resultset.

Skip,
[sub]
[red][/red]
[/sub]