Create a control which contains text on a report?

[croby]

I have 2 tables: Business and System.
System contains System ID and System_Name
Business contains Business ID and System_No
They have 1 to many relationship joined by System ID and System _No

Now I want to create a Report; this report should display all System_Names from the Business table (remember, only IDs on this table)

I can use the following 2 queries to display the System_Names, but don’t know how to create a textbox control to display the Updated, then Selected Result.

(1)(UPDATE System INNER JOIN Business ON System_ID = System_No SET Business.System_Name = System.System_Name
WHERE (((Business.System_No)=[System]![System_ID])); ( I have to add a third column to the Business table)

(2)Select System_Name from Business.

This is an urgent task. Your help will be greatly appreciated. Chris

 

[WayneRyan]

Chris,

Why do you have System_Name in both tables? It should
be stored a Systems table with PK = SystemID.

Either way after your update query runs, the name will be
in both your tables. Simply add the field name to the
query that is the record source for your report.

Wayne

 

[croby]

Why I use only IDs in the main table ....

Thanks to Wayne's reply. Actually my production data contains not just a System field. It has 6 fields with field length of 35 to 50 Chars. And other long fields (memos) too. Using names in the Business table will signficantly increase the record size. Also it violates the Normalization rule -- 6 long fields are duplicated in two tables. I am using Switchboard to bring up the reports. It seems no place for me to put the query (convert IDs to Names, of course, I don't know). I think there must be a way to deal with this kind of situation. Could any one give me further help? Thanks. Chris

 

[WayneRyan]

Chris,

If you can, compact/repair, ZIP it, and post it. I'm sure
many people will assist. Especially with design issues.

Wayne

 

[Pat Hartman]

When you have the ID and text description in one table but you are trying to show the text description when your recordsource is a table that contains only the ID (this is the standard normalized relational tables scenerio), the solution is to join the two tables in a query and select the appropriate columns from each table. Then use the query as the recordsource for your form or report.