Creating report similar to a spreadsheet (1 Viewer)

[Packy427]

Hi all,

I'm working on a project where I'm supposed to take an excel spreadsheet and replace it's function with Access. So far I have created the form, table, and query, now I just need the report which (according to my boss) needs to mimic the existing spreadsheet.

I know this is probably not going to be fun, but hopefully somewhere out there can give me a few pointers?

Attached is an example of what the spreadsheet looks like (Capture1) and what I currently have in my report (Capture 2).

Any help/pointers/links would be greatly appreciated.

Thanks,
Pat

 

[Ranman256]

dont build a report (to look like a spreasheet), its a waste of effort.
just export the table/query to excel via

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, Qry, File, True

but if you need those graphics , then yeah, report is the way to go.

 

[jdraw]

Tell us about the "business issue/opportunity" --that is what does the spreadsheet represent? I'd be surprised that it represents 1 table.
Why move this to Access?
Since Excel and Access are based on different object models, they are not the same and may not have similar results/outputs.

 

[plog]

What you are trying is complicated. It's not just 1 report, its a report with many sub-reports, which themselves will be based on queries and possibly sub-queries. Getting the data for the report is one set of skills (which is not beginner level stuff in itself) and putting all that data together in the format you want is a different set of skills (which is even more difficult).

Not trying to be an A-Hole, just trying to tell you that what you want is complicated: The fact that you don't have a specific question makes me think you don't know where to begin and thus, probably don't have what it takes to tackle this.

I don't know your data, but looking at your report I am certain its going to take several sub-reports to duplicate. My advice is to identify those parts, then start building queries to generate the data for each part. Once you are able to generate the data for each part, then you move onto creating reports based on those queries and laying everything out.

Just because I'm a sadist, can you post your table structure?

 

[CJ_London]

struggling to match data although I can see WGN/SDN and relate 18 etc to P18 etc

But cannot see values for unit, 1st 3rd qtr etc.

To get an exact match you'll need a mixture of queries. It looks like the excel sheet has hidden rows and columns so you'll need to look at these to determine how the values were calculated.

So, look at crosstab queries and possibly union queries and on your report you will need to use subreports

 

[Packy427]

First off, thanks for all of your replies.

Second, I knew coming in that this would be opening a can of worms, and I was fully expecting the first couple of responses to be "Why the hell would you do that?". I was hoping (foolishly) that someone had been able to do this using a crosstab query or some clever VBA. I figured it would come down to a whole lot of sub-reports sub-queries.

Purpose of the spreadsheet:
It is for recording quality control data on a production line. Twice a day (1st qtr/3rd qtr) they measure five units for each location listed on the spreadsheet (18,19,31, etc...). This data is emailed to the production line so they can make adjustments if necessary.

Purpose of switch:
Two excel sheets are saved a day, and they have been doing this for about two years, resulting in over 1,500 individual files. There is also an existing Access DB with lots of other quality control data. Boss wants to stop creating excel files and have a central location for QC data.

Problem:
Boss wants it to look exactly as the existing solution does.

@Plog Yeah..while I was waiting for a replies I began creating sub-queries and sub-reports. I was trying to be broad to hopefully catch something I hadn't thought of yet. I also attached my table structure for your pleasure.

 

[plog]

You probably don't really need the Qtr field (you can calculate that with the RecDate field). And all those P values should be in a new table. That table would have this structure:

PointValues
PV_ID, autonumber, primary key
ID_Measurement, number, foreign key to ID field of existing table
PV_Point, number, this will hold value of all points you measure (e.g. 18, 19, 31)
PV_Value, number, this will hold current value of all those fields


That would make it a little easier to achieve the report you want.