Reports

[mommom]

How can I create a report that has the organization set up that is not in alphabetical order but falls in a order that the users would like to have.

For example:

Secretary's Office
Assistant Secretary
Attorney's
Advisory Counsel
Management
Chief Financial Office
Chief Information Office

etc.....

They have selected the order of the report

How can I create the report to look how they would want?

 

[ZmrAbdulla]

In the table or Query you need to have a field that recognize the grade then sort it by that field.

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[mcalvanelli]

I'm sure there's a better way, but how I handle these types of situations is to assign a "SortNumber" field to the table where the items are located. I then use the SortNumber to order the items in the report.

Hope this helps.

Melissa

Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net

 

[SiJP]

Presuming that the above are headings for groupings, and are stored in a table, create an extra field next to each entry called Dept_SortOrder. Create a User Interface that allows the user to specify which to appear first, second, third etc, then base the groupings on the Dept_SortOrder field (But display the actual department itself as a label).

Hope this gives you some direction.

------------------------
Hit any User to continue

 

[mommom]

The above are part of the different organizations in the company. They want the report set up in a certain order as listed above. I do not have them situated by heading for groupings. Can you explain in an easier format. I am really confused about all this.

 

[ZmrAbdulla]

You must be having table called "tblOrganisation" or something like.
Add a field to the this table name it "FldGrade"
Enter the grade as per the organisation's rank.
[tt]
Secretary's Office =1
Assistant Secretary=2
Attorney's=3
Advisory Counsel=4
Management=5
Chief Financial Office=6
Chief Information Office=7
[/tt]
Bring the query, that creates the report, in the design view and add the "tblOrganisation" to the designer, add "FldGrade" to the query and do a sorting with that field.

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[mommom]

Thanks Abdulla, I will give it a try.

 

[mommom]

Good Abdulla,

I have did what you said. The problem that I am having is when it does the sorting, because I have a total of 45 Organizations it sort by 1 then 10, 11, ....2, 20.....

The Other thing when I pull up the report I do not want the numbers listed. How will I be able to create a report in the order needed without have the numbers pull up.

Thanks for all your help in this!

 

[ZmrAbdulla]

Your field's data type must be text. Change it to number. then it will sort like number.

When you create report do it sorted by rank number then drag the organization name text adjacent to the rank number textbox. make the ranknumber text visible = false.

If you are having trouble then see Grouping & Sorting option from the menu. you can sort there too. This sorting will override sorting done in query.

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[PaultheS]

Looks like your numbers are stored as text, not numbers. Either change the field data type or use Val([Organization]) instead of just [Organization].

Also, you don't need to show the numbers. Just have the report sort by them. They won't show up anywhere unless you place them on the actual report.

 

[PaultheS]

Uh, yeah. What he said...

 

[mommom]

Thanks! That works great. I just have one other problem with the report. They want all the managers listed first and to be in bold. Is there a way to make that happen.

Your help has been so wonderful.

 

[ZmrAbdulla]

Look for conditional formatting through vba
I have a sample code here may be useful to see

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.SubCategory.Value
Case "Soft Drinks"
    Me.SubCategory.FontBold = True
    Me.SubCategory.FontSize = 12
    Me.SubCategory.FontName = "Verdana"
    
Case "Chemical"
    Me.SubCategory.ForeColor = vbBlue
    Me.SubCategory.FontSize = 10
    Me.SubCategory.FontName = "Arial"
    
Case "General"
    Me.SubCategory.ForeColor = vbRed
    Me.SubCategory.FontSize = 11
    Me.SubCategory.FontName = "Times New Roman"
 
Case "Tea / Coffee"
    Me.SubCategory.ForeColor = vbGreen
    Me.SubCategory.FontSize = 8
    
Case Else
    Me.SubCategory.ForeColor = vbBlack
    Me.SubCategory.FontSize = 9
    Me.SubCategory.FontName = "Verdana"
    
End Select
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[mommom]

This one is really confusing to me. I went to the conditional formatting and in the expression I put Secretary=Bailey, Renee then Bold

But nothing happened.

Sorry I am having so much difficulty with all this. They are having me do these databases and it is not what I am use too.

 

[ZmrAbdulla]

My suggestion was not using Access's conditional formatting menu. There you can't set formatting to another control depending the value of a control. It is to use VBA.
Here is some code you can understand more easily.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Designation.Value
Case "Manager"
    Me.Designation.FontBold = True
    Me.FirstName.FontBold = True
    Me.LastName.FontBold = True
    
Case "Secretary"
    Me.Designation.ForeColor = vbBlue
    Me.FirstName.ForeColor = vbBlue
    Me.LastName.ForeColor = vbBlue
End Select
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.