Data Presentation Problem using two tables & relationship.is correct

[mhansler]

2 Tables

Funeral Homes
Cases

Funeral Homes primary key is "Vendor"
Cases primary key is "Case Number"

Funeral Homes contains two fields where one represents "Funeral Home Names" and the other is "Vendor" which is a short version of Funeral Home Names.

Cases has a field for "Funeral Home".

The relationship defined is:

"Vendor" has a one to many relationship with "Funeral Home".

The Funeral Home table has two fields for Funeral Home Names because there will be cases where the Funeral Home name is the same but in two different states. Therefore, two different owners. "Vendor" was created to compensate for this thereby allowing only one instance of a name. It forces the user to make the the name different for each location. The relationship works and all goals have been successful.

Now the problem:

When displaying "Funeral Home Name" from the Funeral Homes table on reports, mailing labels, forms or data access pages, it displays the "Vendor" instead of the "Funeral Home Name" even though we select the field "Funeral Home Name". I understand why this happening and everything is working as it should.

How can I display the real "Funeral Home Name" as displayed in the table onto my reports. It's in the table correctly so what am I missing that I'm not able to get it to display everywhere else correctly.

Thank you in advance.

mhansler2000@yahoo.com

 

[RickSpr]

Well, I don't understand why you're getting the Vendor if the field on the report is bound to Funeral Home. It's not what should be happening. If you can tell me why that's happening, maybe I can figure out how to undo it.

What field is the report control that's showing Vendor bound to, and what is the SQL statement of the report's Record Source? Rick Sprague

 

[RickSpr]

Oh! I read it again and now I understand. Your report is based on "Funeral Home" in the Cases table, which actually contains the Vendor foreign key.

Change your report's Record Source to a query that joins the two tables. Then change the control's Control Source to [Funeral Homes].[Funeral Home]. Rick Sprague

 

[mhansler]

I'm sorry it's taken so long to respond back to this. Been working on other fires.

The control source for the field on the report is Funeral Home Name. However, Vendor is showing up. Not Funeral Home Name. Vendor is the primary key in the funeral homes table. Fields for the report from both tables are stored in a query. The main table (Cases) has a field "Funeral Home" and the table shown below (Funeral Homes) has a field Vendor. "Vendor" and "Funeral Home" link to each other to form a one to many relationship. No matter where I place Funeral Home Name field, I get the data from Vendor field. However, it is the query that is pulling the data this way. Whenever I use fields from both tables these are the results I get. I've killed the relationship and rebuilt it with no luck. Stumped.

Funeral Homes:

Vendor Funeral Home Name
AAMAN / STERLING AAMAN, INC
ACCORD ACCORD FUNERAL SERVICE
ADAMS & SONS ADAMS & SONS MORTUARY
AGEE AGEE BROTHERS FUNERAL HOME


Cases:

Funeral Home
AAMAN / STERLING
ACCORD
AGEE
AGEE
ADAMS & SONS
ACCORD
ACCORD
AAMAN/STERLING
AGEE