Access report with rows with different formats

[olseni]

Hi all
I hope someone can help me with this. I am sure there is a solution, but I cannot seem to find it....

I need to make a report with the following format:

In the columns I have 5 samples, so sample 1, sample 2 etc.
In the rows I have different informationtypes for each sample, and the rows have different formats, e.g. Start date (date format), Weight (number with 1 decimal), and other information using different formats like %, number with 4 decimals (even if the decimals are 0) and similar.

And of course I am talking about hundreds of different informationtypes that need to go into the rows for each event..

I have tried several things.
1.
Creating the table much like I want the report to look like and then using a text format and add the value or text into the text field for the sample in the wanted format, but then I cant seem to get the missing decimals in there in case it must be a 4 decimal number, and the actual number is eg. 1,0000. if you have a solution to that issue, that would be the easiest solution I think...

2.
I tried to see if I could make a kind of conditional formatting, so if I set a field value to e.g 2 decimals, then the field in the report would display 2 decimals for that row, however I never managed to find out how to do that, it seems that I can only set things like colors. If there is a way to do this, that could also be an option.

3.
In my desperation, I went into a really complex (for me) "solution" where I tried using different columns for the different formats, so one column for number with no decimals, one for number with 1 decimal, one for text, one for dates etc. but that turned out to be far too difficult when creating the report.... and I did try and work with subreports, and hiding them when there is no data so basically trying to show the rignt number below the right sample, even if it was in a subreport.

However that is proving to be way beyond my skills, so I am hoping you can help me with any of the simpler solutions described under 1 and 2, og suggest a 4th alternative, on how I can get this report done....

Thanks in advance
Best regards
Ingrid

[Stewart Ross]

Hi Ingrid. I think that the formatting issue is masking a deeper problem: the data in your tables is not currently being structured for storage in a relational database. This is very clear when you mention that there are 5 samples in your columns; the first principle of relational database design is to remove any repeating groups of data into separate discrete tables. The data in any row of a relational database must be uniquely determined by the value of the primary key and nothing else - clearly not true for a repeated group.

If the design is not thought through then the presentation issues will simply multiply as you try to cope with tabular but otherwise poorly-structured data which does not lend itself to what the database is good at doing.

In a fully-normalised relational database the table structure reflects an abstraction of the real-world objects being modelled. For example, if sampling data is to be analysed a table called Sample could be created. It would have a primary key such as SampleNumber that uniquely identifies each sample, and there would be a number of definable characteristics of that object such as SampleDate, SampleWeight and so on which together represent the data to be recorded for each unique sample being modelled.

The fields are represented by properties which are chosen from a range of available data types, and these in turn can be presented in a number of different formats. The format chosen does not change how the field is represented internally in the database, only how it is presented to the user.

I suggest you consider what it is you are really asking the database to do. Like another contributor to this forum you may have unwittingly been influenced in designing your tables by the free-form structure of an Excel-type worksheet.

There is an excellent introduction to table design and normalisation in the HowTo section of this forum, at http://www.thescripts.com/forum/thread585228.html. I and other contributors would be glad to help you, but I am sure this runs a lot deeper than the variable formatting issues you mention.

Regards

Stewart

[olseni]

Hi Stewart
Again, thanks for your prompt reply.
However I might have been unclear about my table structure, since the rest of it actually is normalized, so I guess using a report table is not correct, but....

Anyway, to give a bit of more background, the dataI will be comming from around 50 different tables related to individual tests. Each table is structured with samples in rows, and then the relevant information (for that individual test) in the columns - some of them will be entered by the user, and others calculated - and these columns will have different formats. After the data entry, I then need to make a report combining several og the tests, depending on what the customer has ordered.
So basically I need to combine the different information for the samples for the ordered tests into one report, and the report needs to be structured with samples in the columns, displaying 5 at a time, and then the tests and their information types in the rows.

I am thinking I need to have a seperate report table, so I keep "work" data and reportdata apart, and I would then push the reporting data from each of the around 50 test data tables to the total report table.

For the report table I have then tried different options, the one I described, the xls looking table looking like the format of the report basically using text columns, and it would be just to make the report.
The otehr variant I worked with, which is probably more correct is a table structured more like my test data tables, and then pushing the sample id into one column, and the information "title" into another column, and then the individual information values into other columns solely based on their format (column 3 = 0 decimals, column 4 = 1 decimal, column 5 = date etc).

However my actual problem is how to convert that into the wanted report format without the informationvalues from the test data tables looses their original format.

I hope this clarifies.... :-)
Best regards
Ingrid

[Stewart Ross]

Many thanks for your clarification, Ingrid. I am so glad that you really have a normalised set of tables - at least all we are left with is the technical challenge of presenting the data in the format you require, and not the much tougher one of contending with unstructured tables.

Could you provide a snapshot or sample of the data in the formats you want to present it (suitably anonymised of course)? It would really assist with visualising what you need to achieve. (Attachments can be added to a reply by using the Edit button after sending the reply.) This is where Excel can come in very handy as a means of prototyping the output you require (although the xls format is not one you can attach to your reply).

Regards

Stewart

[olseni]

Hi Stewart
Sure, I am adding a pdf sample of a report page. This example contains only 1 test for 5 samples. however I have added a line with Test 2, just to show where it would need to start. And also there might be more than 5 samples on the report, in which case there would need to be a second section with samples 6-10 and so on untill all samples are covered.
I hope you can understand the text, I translated the important parts to english, as I presume your danish is not so good....:-)
And again thanks a lot for your help, I appreciate it a lot !
Best regards
Ingrid

ok, the file has become quite small, since either it was too big filesize or to wide or whatever....hope you can still get an idea of the layout.

[Stewart Ross]

Hi Bridget. Unfortunately, the PNG file is too compressed to make out any detail. Perhaps a plain-text or PDF copy of the contents would work instead?

Regards

Stewart

[olseni]

Hi Bridget. Unfortunately, the PNG file is too compressed to make out any detail. Perhaps a plain-text or PDF copy of the contents would work instead?

Regards

Stewart

Hi Stewart,
Sorry for not responding untill now, I have been busy elsewhere, but am now back and still have the issue, so I hope you can/will still help me. I will try and add another file....
Best regards
Ingrid

Image added, sorry but the limitations of uploads are very limited, so I had to reduce size considerably and flip it 90 degrees, hope it makes sense anyway...
Best regards
Ingrid

[Stewart Ross]

Hi Olseni. I have sent a private message (PM) to you in which I have provided my e-mail address so you can attach a copy of your graphic without the size limits for posts in the forum. Unfortunately the thumbnail graphic posted is still too lacking in detail to see what format you require...

-Stewart