Data displayed in a matrix? 1

[Grounded]

Another in the continuous stream of problems.

I have a table with three fields: Oil_distribution, oil_width, Oil_character. There are 5 predefined values for distribution and 4 for width. This means that there are 20 possible combinations of the two and each combination is assigned a character (heavy, medium, light). These assignments change depending on the site so i need the user to enter them easily - Preferably in a grid with width across the top, distribution on the side and character in the grid. Also, i need to access them easily for further calculations.

i have NO clue how to start to build a form that allows the user to edit all 20 records at the same time. I thought of putting each value in it's own field but that will make it difficult to query later.

What say the collective?

iz

 

[MajP]

Here is one way to do that. Make a table, I call it a working grid table.

tblWorkingGrid
strDistributionLabel
strWidthOne
strWidthTwo
strWidthThree
strWidthFour
strWidthFive

Now put your distribution labels in the field strDistributionLabel.

Now you have your grid if you build a tabular form bound to this table. Disable the strDistributionLabel textbox.

You then have a control on this form that allows you to select a site. When you change a site you use recordsets to read the values in this table and then save the records to your data table, and then populate this grid table with the existing records for the selected site.

It is a combo bound-unbound form. I have an example I will post.

 

[Grounded]

This looks interesting -
I look forward to your example so i can really dig into it.

Thanks,

IZ

 

[MajP]

I looked at my examples and they are a little more complex than this one so they may cloud the issue.
1. What is your above table's name?
2. I would think your table also needs a site ID. What is the name of that field? If you do not have one explain how this works. My assumption is that you have many sites contained in a sites table.
3. If your interested in this approach see if you can make the working grid table and the grid form. If you put your five distribution labels in "strDistributionLabel" you will have five records. You could use combo boxes on your width fields with values of "heavy, medium, and light". If you put that together I will demo some code to read from and write to the grid.

 

[Grounded]

Ok,
I am working up the example.
First - there are NO sites. A blank version of this db will be brought to each new incident/site. This is an oil spill response tool so a new spill gets a new copy of the database to track all the info for that spill. Assuming the db arrives with the standard values for initial cover, the responders will need to edit these values for the given circumstances.

I three tables that are relevant now:
tblIntialCoverWorkingGrid - just as you suggested - distribution_labeo, wide, medium, narrow, very_narrow

tblIntialCoverValues - as i orignially discussed with three fields - distribution, width, initial_cover

tblDropDowns - contains all the dropdowns for the database - including the field init_surf_oil_cover wtih the values Heavy, Moderate, Light, Very Light. tblIntialCoverWorkingGrid uses these as choices - just like you suggested (i was 5 minutes ahead of you....)

Synopsis: someone goes to a beach and records oil width, distribution and thickness. Decision makers fill out the grid that sets intial cover values for each combination of width and distribution. The inital cover is determined for an observation using the width and dist. THEN the cover is put together with the third measurement - thickness - to come up with a final Oil Categorization for the beach. A little round about but hopefully you get the idea.

 

[MajP]

A little confused. I will have to read this a few more times to see if the approach makes sense. I think it does, but I am trying to figure out how the recorder is entering information and how the decision makers need to see this information. Can you describe a "Distribution"? Are there set values? Also is the recorder only taking 20 measurements (dist1_Width1 ... to dist5_width4)?
Since you are using a new database for each site, it almost sounds like this would be a better Excel application.

 

[Grounded]

Ok, so the guys in the field are collecting a TON of related data not just these three things. This is just one small piece of the puzzle. We’re using Trimble Recon GPS enabled pocket pcs. When there is a spill there are many beach segments that each get surveyed many times. There are also many individual samples and other waypoints that are associated with each survey. We track members of each survey team, tides, sediment, wildlife, weather, oil, beach access etc, etc, etc. Definitely database – right now it’s all contained in about 20 tables and a couple hundred fields.

For each survey we collect the Distribution (% of beach covered by oil), thickness (of oil on beach) and how wide the bands are (width). The matrix is used to turn Width and Distribution measurements into an Initial Cover Classification (Heavy, Moderate, Light, Very Light). This initial cover class is then paired with Thickness to come up with a final Oiling categorization – Heavy Moderate Light. It’s really a two step way to get into what is really a 3D matrix where thickness is the third dimension. Obviously a beach that has 4’ bands that cover half the beach – with 1mm of oil on it is less oiled than the same with 6” of oil. In the end we link the db to GIS and color code the shoreline on a map based on final categorization so that decision makers can decide where to send cleanup crews, equipment etc. For a spill last year in AK we had 300+ beaches all of which were surveyed several times over 6 months.

The matrix that we’re talking about though (that translates Distribution and Width to Initial Cover) stays the same for the duration of the response. It will get adjusted when there is a different spill somewhere else when we’re collecting all new data. So it really only has to go one way. From tblIntialCoverWorkingGrid to tblIntialCover.

Sorry for the headache – if you are overwhelmed jump ship and I’ll flounder around.

i

 

[MajP]

If I understand the problem correctly the following works.

Public Sub UpdateInitialCover()
  Dim rsCover As DAO.Recordset
  Dim rsGrid As DAO.Recordset
  Dim strDistribution As String
  Dim intCounter As Integer
  
  DoCmd.SetWarnings (False)
  'A query that deletes the records already in tblInitialCover
  DoCmd.OpenQuery ("qryDeleteInitialCover")
  DoCmd.SetWarnings (True)
  
  Set rsCover = CurrentDb.OpenRecordset("tblInitialCover", dbOpenDynaset)
  Set rsGrid = CurrentDb.OpenRecordset("tblIntialCoverWorkingGrid")
  Do While Not rsGrid.EOF
     strDistribution = rsGrid.Fields("distribution_label")
     For intCounter = 1 To (rsGrid.Fields.Count - 1)
       rsCover.AddNew
       rsCover.Fields("distribution") = strDistribution
       rsCover.Fields("width") = rsGrid.Fields(intCounter).Name
       rsCover.Fields("initial_cover") = rsGrid.Fields(intCounter).Value
       rsCover.Update
     Next intCounter
     rsGrid.MoveNext
   Loop
End Sub

This basically takes the values in the data grid table and puts them in a more normalized initial cover table.

 

[MajP]

There is another approach to do this. You could make the whole grid form unbounded. Put twenty combo boxes on the form. I would name them consistently; something like

cboD1W1 'Distribution 1 width 1
cboD1W2
...
cboD5W4 'Distribution 5 width 4

Use this code to save the values to the unbounded form

Private Sub cmdClose_Click()
  Dim D1W1 As String
  Dim D2W1 As String
  ' ....
  Dim D5W4 As String
  
  D1W1 = Nz(Me.cboD1W1, "")
  D2W1 = Nz(Me.cboD2W1, "")
  ' ...
  D5W4 = Nz(Me.cboD5W4, "")
  
  DoCmd.OpenForm Me.Name, acDesign
  With Forms("form1")
    .cboD1W1.DefaultValue = """" & D1W1 & """"
    .cboD2W1.DefaultValue = """" & D2W1 & """"
    '
    .cboD5W4.DefaultValue = """" & D5W4 & """"
  End With
  DoCmd.Close acForm, "form1", acSaveYes
End Sub

Now I would add a table and change the tblInitialCover. The table

refTblInitialCover
strInitialCoverID (PK)
strDistributionName
strWidthName

Looks something like

D1W1 0%-20% Wide
...
D5W4 80%-100% VeryNarrow

tblInitialCover
initialCoverID (FK)
initial_Cover

Looks like
D1W1 Heavy
...
D5W4 Light

Now on close you read the combo boxes from the controls collection, use the left 4 characters of the name as the initial cover ID to put in the tblInitialCover and the value initial_Cover comes out of the control. If you rather go this approach I can send you the code to read the form and populate tblInitialCover.

 

[MajP]

Here is the the sencond part of the code to read your unbound controls and put the values in the table.

Private Sub Command8_Click()
  Call updateInitialCover
  Call saveValuesToForm
End Sub

Private Sub saveValuesToForm()
  Dim D1W1 As String
  Dim D2W1 As String
  ' ....
  Dim D5W4 As String
  
  D1W1 = Nz(Me.cboD1W1, "")
  D2W1 = Nz(Me.cboD2W1, "")
  ' ...
  D5W4 = Nz(Me.cboD5W4, "")
  
  DoCmd.OpenForm Me.Name, acDesign
  With Forms("form1")
    .cboD1W1.DefaultValue = """" & D1W1 & """"
    .cboD2W1.DefaultValue = """" & D2W1 & """"
    '
    .cboD5W4.DefaultValue = """" & D5W4 & """"
  End With
  DoCmd.Close acForm, "form1", acSaveYes
End Sub


Private Sub updateInitialCover()
  Dim rsCover As DAO.Recordset
  Dim strDistributionID As String
  Dim intCounter As Integer
  Dim cboMatrix As Access.Control

   DoCmd.SetWarnings (False)
  'A query that deletes the records already in   tblInitialCover
  DoCmd.OpenQuery ("qryDeleteInitialCover")
  DoCmd.SetWarnings (True)

  Set rsCover = CurrentDb.OpenRecordset("tblInitialCover", dbOpenDynaset)
  For Each cboMatrix In Me.Controls
    If cboMatrix.ControlType = acComboBox Then
       strDistributionID = Mid(cboMatrix.Name, 4)
       rsCover.AddNew
       rsCover.Fields("strID") = strDistributionID
       rsCover.Fields("initial_cover") = cboMatrix.Value
       rsCover.Update
    End If
 Next cboMatrix
End Sub

Both ideas work, if I understand what you are doing.

 

[Grounded]

hey,
this was great. wound up doing almost exacltly this.
thanks for the help!