-1

I'm using this forced "down time" to finally take my business from Excel to Access. I am fairly accomplished at Excel VBA etc, and pretty much run the business on a handful of highly developed Excel sheets I’ve created over the years. They work well, but they are not very scalable, and I want to get over to a proper relational DB.

I've taken an Udemy course on Access which was fine, but I’ve already hit some issues which may be fundamental misunderstandings, or just inexperience.

My first issue is that my company has projects (commercial contracts) which often, but not always, involve two ‘customers’ - an End User and an Agent. Agents and End Users can be interchangeable though, i.e. an Agent on one project might be the End User on another, so my “Customer Table” is simply a list of ALL my end users and agents with a CustomerID.

In my “Project Table” I have a CustomerID field and an AgentID field, both of which I wanted to use to pull out a customer and then agent from the single “Customer Table”. I can’t find the way to set up the relationships to enable me to do that – I can get either one, but not both for each Project Table query. For a while I thought I needed a many-to-many relationship I needed, but I still don’t find how I can reference two entries from a single table in one record. Thanks for any help!

  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Apr 02 '20 at 18:53
  • I dont believe so, but I will read further. My question was answered by Applecore yesterday, and some further trial and error. Thanks for your help. –  Apr 03 '20 at 13:09
  • The link addresses modeling your subtyping, 2 types of customer, user & agent. This post is not so clear, whether "set up the relationships" means you are stuck in design and/or querying, or writing code vs using a GUI. For code questions give a [mre], Constraints are for integrity, they are not needed for querying. Your question is absolutely basic. (Like everyone,) Follow a published academic textbook on information modelling, the relational model & DB design. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) – philipxy Apr 03 '20 at 22:14
  • It's not clear exactly what your quesiton/problem is, but it's almost certainly (like the accepted answer) a frequent duplicate. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 03 '20 at 22:19

1 Answers1

1

You're almost there. What you need to do is to create a one-to-many join between tblCustomer and tblProject (based on tblCustomer!CustomerID=tblProject!CustomerID) and then another one-to-many join between tblProject and another instance of tblCustomer (based on tblCustomer!CustomerID=tblProject!AgentID). The relationship window should look like:

enter image description here

Regards,

Applecore
  • 3,934
  • 2
  • 9
  • 13
  • Thank you so much for the rapid response. I have acheived what you said, but I can't access the second instance (Customer Table_1) in the Query tool in order to use it for my query. I am only given Customer Table and Project Table again. I'll keep trying. –  Apr 02 '20 at 13:37
  • Use "Customer Table" again. Access will see that there is already something called "Customer Table", and automatically call it "Customer Table_1". – Applecore Apr 02 '20 at 13:43
  • OK, something strange happened, but it's good strange. Having strated again and now dragging a second instance of customer table into the query design area. I made the connections as before, but I was now given both Customer Table and Customer Table_1 in the drop-down when adding fields. So I was now able to select the second instance and I got the result I wanted! Not sure why that wasn’t happening earlier, but I really appreciate your pointer in the right direction. Many thanks for solving this issue for me. –  Apr 02 '20 at 15:18