Place records into specific boxes in a grid - Please see attachment

[shaleen7]

Hi All,

I need some assistance. I have a table that contains the following information:


Name Rating
John High
Jill Medium
Kim High
Mary Distinguish
Clara Underperform
Ellen Medium

I want each name to be placed into specific box in a grid. If John has a high Rating then he goes in the box labeled high.

Please see the attachment.

Cheers!

Thanks for the advice

 

[dhookom]

I would create a form with text boxes named for each rating. You could then open a recordset based on the table. Loop through the records adding the names to the appropriate text boxes.

It isn't at all clear why Jill is in the middle medium and Ellen is in the right medium. Same goes for Clara. Are there rules or specifications you haven't shared?

Duane
Hook'D on Access
MS Access MVP

 

[shaleen7]

Hi Duane,

You are right! I should have clarified. There is an additional criteria for the box. If someone is considered a medium performer they are placed in the center box (i.e. Jill). If someone is considered a medium performer with a negative score they are placed in the center right box (i.e. Ellen).

I was hoping to get past getting the names of all the employees who are medium performance in the center box.

I think I did as you suggested. I inserted a textbox for each rating and selected the appropriate recordset. The textbox was placed into the detail section. However only one name appears. I have to click move to the next record to see the next name.

So for example for the high performers, there are two John and Kim.I can see John and then I have to select the next record so I can page to Kim.

Is there any way to have all the names that have a high performance in one form?
John
Kim



I also thought I maybe should use a report. What are your thoughts? Am moving in the right direction?

 

[shaleen7]

Eureka! I found the continuous form. Now my records appear together.

John
Kim

I'm curious...instead of having the names appear from the top down. Can I have a certain number have fields go across...like this
High Performers continuous form
John Kim Tim
Patty Lyn Alexis

Cheers!

 

[dhookom]

There are no "negative score" values in your sample data. Please provide the actual table and field names with the "score" column.

Do you need this in a form or would a report suffice? Here is an example of code using an unbound form with text boxes for each city in the table. I have named a text box for each city and removed any spaces since I don't allow spaces in object names.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT LastName, City from EMPLOYEES"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        .MoveFirst
        Do Until .EOF
[COLOR=#4E9A06]            'remove any spaces in the city name
            'assumes there is a text box for every city
            '   with a name like "txtSeattle" or "txtLosAngeles"[/color]            
            Me("txt" & Replace(!City, " ", "")) = Me("txt" & Replace(!City, " ", "")) & " " & !LastName & vbCrLf
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[shaleen7]

Hi Duane,

I've uploaded the access database. I'm trying to create a 9 box grid using the forms.

One issue that I can see ....is that if I have 20 employees in on box the box will stretch off the form. I wondering if I can show 2 or 3 column per box.

Meets Medium Box
Rachel Perez Gary Pitts
Cheri Ritch
Elmer Ritch
Thanks for the looking at this.

 

[dhookom]

Again, is a report acceptable?

Duane
Hook'D on Access
MS Access MVP

 

[shaleen7]

yes

 

[dhookom]

I would build a subreport that can be pasted into the main report as many times as you have Ratings. Use the Link Master/Link Child to filter each instance of the subreport to the appropriate rating.

Duane
Hook'D on Access
MS Access MVP

 

[shaleen7]

Thanks so much! That was the direction I was going.
The only thing I’m trying group the report by Department and Manager . I placed the 9 box subreports into the manager header but it keeps repeating and not grouping the report by Department and Manager. What am I doing wrong?

The data in the grid will be used to during a performance review so the database will be updated while the managers review the data in real time. Is there any way that someone can updated the access database and have the report data appear up to date in real time.

Should I post the grid to the web?

 

[dhookom]

Your main report would be something like:

SELECT Department, Manager
FROM SomeTable
GROUP BY DepartmentManager

You don't want to include the same level of detail that exists in your subreports. The subreports should then be in the detail section of the main report.

BTW: are we through adding specifications ;-)

Duane
Hook'D on Access
MS Access MVP