Table Structure

Sounds like we are about 12 hours apart.

What I mean by exporting is that you set up a template file in excel and a query in Access. You copy the data from Access into your template file, refresh the report section of the spreadsheet and it updates itself based on the new data.

I have attached a bare bones example. There are 2 tabs--data which would hold the raw data from an Access query. The pivot tab looks at the data tab and is the final report. You would the pivot tab to look exactly like you want and then whenever you needed to generate a new report, you run your Access query, copy the data to the data tab of the spreadsheet and refresh the pivot.
 

Attachments

Seems like, I'm GMT+5.

My boss says that everything we're doing with Databases is to move away from Excel sheets and he doesn't want to use Excel at any stage. He says it is possible that you come across some nearest acceptable reporting format in Access, but we certainly don't want Excel.
He said everything should be within a database program, like commercial applications do.
I also see lot of graphs coming in next part of the task he's going to hand me.
But I'll learn about this Excel idea, for some other task in future.

I'm reading cross tabs and pivot in Access today. hope it helps me some.
I've a 12 hr working day, without a weekend. I'm sitting even after working hours... and I'm feeling like i've done nothing since I've started with this (
I'll write back after my next attempt.

Thanks
 
He says it is possible that you come across some nearest acceptable reporting format in Access, but we certainly don't want Excel.
Just my 2 cents worth - This is a bit like having a car and a van at work and insisting that all the deliveries are made in the car... Or picking up you visitors in the Van...

Excel is a excellent reporting tool - it's what it is designed to do.
What happens when you want to send a report summary to a customer or some data? You'll almost certainly send it as a spreadsheet so they can import or examine it.
Access can produce quite nice reports, but it's graphing and pivot table tools are much more restricted than those in Excel.
Use the strengths of both.
 
I'm with Minty on this one. Recreating Excel facilities in Access is pure masochism. Export the data to Excel and let Excel do what it's best at.
 
I'm with Minty on this one. Recreating Excel facilities in Access is pure masochism. Export the data to Excel and let Excel do what it's best at.
That is why i was requesting for alternative ways to represent these kind of reports so that I could use Access, but seems like there's nothing except Pivot tables that could help me around!!
 
Actually, you were asking for ways to represent those reports as you have them now. To do that (with steps as column headings) you would need a pivot table in Excel or a cross-tab in query.

If you are open to new formats then a standard Access Report would work. You would use groupings like so:

Project Name Here
-- Category 1 Here
-- -- Step 1 Here
-- -- Step 2 Here
-- -- Step 3 Here
-- -- More steps
-- Category 2 Here
-- -- Step 1 Here

etc.
 
By the way, i was trying to convince my boss full day that experts are advising to use Excel for these kind of reports and seems like he has started to understand a bit and was producing different sounds in agreement. Hope he doesn't change his mind in the morning.
 
Thanks plog, once again. I'll try that too, boss is getting flexible... but at later stages i need to build charts on these reports. Will I be able to do that with standard reports/queries in Access?
 
i need to build charts on these reports. Will I be able to do that with standard reports/queries in Access?

Technically yes; practically it will be a lot easier to build charts in Excel. You can add charts to Access, but working with Excel is a lot easier and more robust (more chart types, more options in adding things to charts, etc.)
 
Thanks plog, for your help on this query. Today i was able to complete the first part of big project and basic idea started from your hints )
 

Users who are viewing this thread

Back
Top Bottom