Convert "ID" back to full value in Report

[Romper]

Hi all,
I have just normalized my database. In my main table "TBL_Main" I have a column "CategoryID" which gets it's information via a form combo box. The combo box looks at another table "TBL_Categories" which has fields "CategoryID" & "CategoryName".

So for example, the first record in TBL_Categories is "CategoryID" = 1 and "CategoryName" = Commonwealth. So via my combo box on my form I select "Commonwealth" from the drop down and it returns it's ID value back to the "CategoryID" column in the record source "TBL_Main.

If I now create a report on TBL_Main though it displays the "CategoryID" and what I need it to display is the "CategoryName" in this case 'Commonwealth'.

What do I have to do to rectify this so that ID's are converted back on the report to the value that they identify? E.g. (Commonwealth).

Please help.

 

[RainLover]

ComboName.Column(1)

0 is the first. 1 is the second etc.

If it does not work place it in your module select Column and hit F1 for help.

 

[Romper]

Sorry you might have to explain that in a little more detail. I used the Report Wizard to create the report. If I open the report in design view, what should I now be doing?

 

[Alansidman]

Cross Posted at: http://forums.aspfree.com/microsoft-access-help-18/convert-id-back-full-value-report-537078.html

 

[Romper]

Thanks Alan,

I'll try to play around with a query and see if I can get it to work.

Is this site linked to ASP?

 

[John Big Booty]

...

Is this site linked to ASP?

No.

Getting caught cross posting can be a little like getting caught in bed with your best mate's wife. Best done sparingly and as a last resort. Many people contribute to multiple forums, and it often gets spotted.

 

[Romper]

Ooops!

Sorry, didn't realise it was an issue. I originally joined ASP but have had many issues with the site since it changed. Alan has been very helpful, but I think I'll stay here.

Do you know of any resources I can study to try and implement what Alan suggested about creating a query to run my reports off so that the true values show?

 

[John Big Booty]

Have a look at the query I've added.

Also have a look at some of the video tutorials on YouTube

 

[Romper]

Surpisingly enough I managed to create the query and get a successful report to run from it. However, when I added the relevant tables to the query design, there was already a join between "CategoryID" in both "TBL_Main" & "TBL_Categories". However no join for "CatalogueID" for tables "TBL_Main" and "TBL_Catalogues". So I clicked on the one existing join, selected add new, and created the "CatalogueID" join and all is good.

These joins however, are not reflected in the relationships window. Why is that? If needed, what would I select in the query design window to create a join, If I hadn't of had an existing join to manipulate and create a new one from.

Thanks John

 

[RainLover]

Joins and Relationships, although they look the same are different.

You set up Relationships as permanent. This is where you can enforce Intregity. This is so you can't create a record in a Foregin Table unless it has a matching Primary Key in the Primary Table.

A join in a query can be created to show different results.

e.g. Matching Records.
All records from one table and only those records in the other table that match.

Do a google on the subject as I am sure it has been explained by others better than I have.

Further to my first post you place that code in the Control Source of the Combo Box.

 

[Romper]

Thanks so much John & Rain,

Have a great weekend.