Linked Excel Worksheet One-to-Many Relationship to Table (1 Viewer)

jamiers

New member
Local time
Yesterday, 18:46
Joined
Nov 11, 2016
Messages
3
Hi All,

I am trying to create a relationship and I am at a loss. I don't know VBA... so that isn't going to be a route unfortunately, if that is part of your solution. I send people out on "individual jobs" sort of like contractors! My volunteer management system outputs an Excel spreadsheet with all of my volunteers' contact information. This changes on an ongoing basis.

I want to link this "linked Excel Worksheet" by Member Number (this is the *one* connection) to my Jobs worksheet by member number (and this is the many).

I can't figure out how to get it done so that I can edit directly in a query (which in turn would be a form... where I type in the member number and it populates fields... and I can enter the job details in the same query... I can get it to pull up information in a query, but I can't edit my jobs information in the query....

Any ideas?? I attached some screenshots (ignore the relationship). I want to create a one-to-many relationship from linkedActiveMembers.member_number (one unique record) to the tblDeployments.MemberNumber (many member numbers). I had another forum say I had to do things with Primary Keys and Foreign Keys, but I can't figure out how to set keys on a linked Excel Worksheet....

:banghead:
 

Attachments

  • query.png
    query.png
    18.8 KB · Views: 58
  • relationships.PNG
    relationships.PNG
    15.9 KB · Views: 62

Ranman256

Well-known member
Local time
Yesterday, 21:46
Joined
Apr 9, 2015
Messages
4,339
You don't need to know VB.
Your active member list can be an external XL file, but I would rather import it as an internal table.
All relationships would run off the internal table.
The IDs would remain ,and the excel file would provide updates to the table.
 

Users who are viewing this thread

Top Bottom