Relationships with a Linked Excel Workbook and Access Table

[jamiers]

Hey All,



I have a online data management system that outputs a canned excel workbook with:

• Member_Number
• Account_Name
• Address Info
• etc.

My other table in Access holds (for now):

• MemberShortName
• MemberNumber

I have a separate database that I need to maintain because the online data management system does not allow me to monitor incidents for a member... to make thing easier -- I want to send someone out on a job... I need all of the volunteer's basic info (above) and link it to their job. A volunteer can have multiple "jobs."

When I export out the table from the Volunteer Management System, it creates an XLS file that dumps almost all columns as "general." I have no jurisdiction over this field output...

When I link the workbook into Access (because I will export again from the Volunteer Management System and then save it into the same file), the linkedtable.MemberNumber will not do a one-to-many relationship. When creating a relationship, it comes up as Intederminate.

When I create a query with design viwe:
AccessTable.MemberShortName
AccessTable.MemberNumber
LinkedTable.account_name
LinkedTable.cell_phone
LinkedTable.member_number


If I put data into the "Access Table," it shows up in the query. I cannot put enter data into the query.

What could be my issue?

[June7]

What fields are you linking on?

Are you trying to set a relationship in Relationship Builder? Can still set the Join Type.

Should be able to enter data into the native table but not the fields from the linked worksheet. A linked worksheet cannot be edited.

[jamiers]

I am trying to link the Member Numbers. I did the join, like I think you said, but I cannot edit the fields from the Deployments table (which is my table I created in Access).a

[June7]

I did some testing and linking had to be on PK/FK fields. Are you saving the ID as foreign key in other tables?

[jamiers]

The Primary Key in the deployments table is a unique identifier because each record is different.
I don't think you can set a foreign key on a linked excel sheet?

So... Then I guess it can't be done?

Basically, I want take the member number in the deployment table, look-up the contact info, and pass it back in a query. The member number may show up multiple times in the deployments table.

[June7]

The PK has to be in the table. The FK field in the worksheet.

You are not linking the worksheet FK to a PK in the table.

As I said, it works for me if the FK in worksheet is linked to PK in table.

Yes, ID is a unique identifier and it should be the PK if it is also saved as FK in other dependent 'child' tables. Is MemberNumber also a unique value in this table? If you are saving MemberNumber as FK in other tables then it should be designated as the PK. Then the link to worksheet would work.

[jamiers]

The PK has to be in the table. The FK field in the worksheet.

You are not linking the worksheet FK to a PK in the table.

I think this is where my knowledge of Access programming is getting in the way. AccessTable.MemberNumber is the one in the table that I want to match to the LinkedTable.member_number in the worksheet.

THe Worksheet has all of the contact info I want to pull from. There is 1 member number in the worksheet for every volunteer. In the Access Table, I want it to match the multiple
member numbers to the one member number in the worksheet.

This sounds backwards for what you want me to do.

I am also still unclear how you set keys for a linked worksheet. When I go into edit the linked worksheet, I can't edit the details there because it is a linked worksheet.

I'm sorry I'm being so dense... I'm really trying!

[June7]

I do not set key for the worksheet. Only a PK can be 'set'. The FK is simply a field with the corresponding value.

Yes, if MemberNumber is not unique in the table, cannot do what you want. However, that does not make sense to me. Why is MemberNumber not unique in the table? What is nature of this data?