Reporting

[didacticone]

I have a database that i use for hydrant inspections. There are about 15 different items per hydrant that need to be inspected. About 2000 total hydrants. I have every item that needs to be inspected as a separate field in my master table. My problem is, i need to compile a report for every hydrant. I would like some type of list view with like 20 hydrants per page but that is impossible with how many different fields there are. I really only need to show a field if there is a problem. Im kind of lost on how to handle this and was curious if anyone had any suggestions. One thought i had was if a field had a problem, it could send some custom text to a text box stating the problem. Then on my report i would only have to display that text box. But there are so many fields and more that one thing can be wrong with the same hydrant and i am not an advanced enough user to code that properly... sorry if this is confusing and if i need to clarify anything, just ask and i will.

[zmbd]

A long shot for me; however, have you tried a cross tab query/report? You might be able to fit the columns as the headers and the hydrants as the rows? Or are you looking for a different format?

[TheSmileyCoder]

It would make it alot easier for us to present help and for you to undersaid said help it we speak from the same context. Could you please show us your table layout (Field names and types) as well as a few example records?

[didacticone]

example records is challenging because they vary.... for the asset_id its obviously just a number, the other fields are mostly ok, not ok, or yes or no... pretty basic.

[zmbd]

Apparently, the firewalls here are blocking the image... I've tried to fix it so that is shows. Hopefully, TheSmileyCoder can see it :)

As for the records... just a simple CSV format would be good:
[field1],[field2],[field3],[field4]
1, some record info, Y, N
2, another record, N, N
3, YAR, Y, Y

Table format such as:
tbl_name
[field1] primary key, autonumber
[field2] text(50), required, no duplicates
[field3] boolean
[field4] boolean

-z

[didacticone]

Ok ive attached the csv

[didacticone]

http://i4.photobucket.com/albums/y14...olx/fields.jpg that is a link to the fields... perhaps you can see it then

[TheSmileyCoder]

Weird I could see the image earlier, and now I can't. Odd.

One approach could be to have code that checks each field, and if "negative" adds text to a combined string, and the report then shows said combined string.

[TheSmileyCoder]

Start by placing all the fields you want visible into your report. Then add in all the fields you want to combine onto your report and set their visibility to False. You can make them as small as you want and delete their labels too if required.

Finally add a textbox where you want the "summary" status to be printed on your report. In this case I callde it tb_Combined. Make sure it has its "CanGrow" property set to true.

Then in the reports Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim strC As String
 
   If Me.Drainage & "" <> "OK" Then
      strC = strC & "Drainage: " & Me.DrainageComments & vbNewLine
   End If
 
   If Me.Drip & "" <> "OK" Then
      strC = strC & "Drip: " & Me.DripComments & vbNewLine
   End If
 
   'Add more field checks in here if needed
   If strC <> "" Then
      Me.tb_Combined = Mid(strC, 1, Len(strC) - 2) 'The -2 is to remove the last vbNewline.
      Else
      Me.tb_Combined = "OK" 'Nothing wrong at all.
 
   End If
 
 
End Sub
 
 

[didacticone]

ok going to try this tomorrow... thanks for the help and i'll let you know how it goes. i really appreciate the help guys.

[didacticone]

tried it and it works awesome! i have on extra question though. for the drainage category i have 2 options, "OK", "Drains Slowly", "Doesn't Drain". I want if it says "ok" or "Drains slowly" for it to be considered "ok"... if your following... i want to know if it drains slowly but as far as the report goes i want it to be considered ok and am not quite sure how to manipulate the code to get the required results.

[TheSmileyCoder]

You have 2 possibilites either check that its different from both ok and Drains slowly, or check that it is equal to Doesn't drain.

'Example condition one
If Me.Drainage & "" <> "OK" AND me.Drainage & ""<>"Drains slowly" Then
'Example condition 2
IF ME.Drainage & ""="Doesn't Drain" Then 

With that said, do you control the entry of data so that user(s) cannot put "not draining" or "Drain not working" and such?

A good part of UI design is ensuring uniformity and ensuring that users cannot take wrong actions (cause they inevitable do, and when they do, they blame you the developer)

[didacticone]

i actually used your second example right after i posted the message and it worked perfectly. and yes users can only input the data a specific way. thanks so much for your help!

[TheSmileyCoder]

Thats nice to hear, and I am glad to hear you know to limit your users to pre-defined entry values.