Joining tables in report

[emozley]

Hi,

I have one table called Users which has columns UserID, FirstName, Surname, Category, SubCategory.

Columns Category and SubCategory both hold numbers which correspond to other tables:

Category
--------
CategoryID (Autonumber)
Category (Text)

and

SubCategory
-----------

SubCategoryID (Autonumber)
SubCategory (Text)

I am trying to build a report that shows their first name and surname and their subcategory. I want to have them grouped by their main category though. This is basically working but instead of showing the text that is in either the category or the subcategory table it is just showing the number stored in the Users table.

Is there any way round this

Thanks very much

 

[emozley]

Hi,

I've had a bit of a play around and have used the Relationships to link the tables and then gone through the wizard again to select relevant data from the different tables.

In the report wizard I have got it to group by Category. Is there any way of displaying the total for each category next to each heading and also the total number of records at the top of the report?

Thanks

 

[emozley]

Hi,

I've figured out how to do the above - just one more question... because the Category is displayed is a variable length it means I have to put the =count(*) quite a long way to the right of it to make sure there is no overlap.

Is there anyway of modifying the field in the report so that at the end of the text it puts a ( then the total then a ). ie so this number in brackets is always hard up agains the end of the word regardless of how long it is.

Thanks very much

 

[SeeThru]

You will have to calculate the field.

Create a new field - MAKE SURE THE NAME IS NOT THE SAME AS ANY FIELDS FROM THE TABLES!

set it's data source to

=[Category] & " (" & count(*) & ")"

Done