No Unique Index Found for the Referenced Field of the Primary Table

[PhaerieTail]

Hello all,



I'm sure you've seen this question a thousand times, or maybe it's just because I've been googling this for the last 2 hours.

I'm new to Access and have no other ideas of even what to search to fix this, so please help!!

I have two tables - Customers and Jobs. I have assigned each customer a Customer ID and this serves as the primary key for that table. Customer ID is also a foreign key in my Jobs table.

I've successfully created a relationship with referential integrity and cascading updates with the Customer ID in both tables. I would like to do the same for the Customer field in both tables, but I get the error message "No unique index found for the referenced field of the primary table." The goal is obviously to be able to change the customer information in the Jobs table by changing it in the Customers table. It works for Customer ID, but I need it to work for Customer.

Please let me know if I'm missing any integral information - I'm a fast learner but Access has always thrown me for a loop.

[orange]

Customer info is in the Customer Table. Jobs Info is in Jobs Table. The CustomerID represents the relationship PK-FK between the Tables.
Please tell us in plain English the process(es) you are trying to support.

[ssanfu]

I'll tell you how I would design the tables.

The goal is obviously to be able to change the customer information in the Jobs table by

Terrible idea. There should be NO customer info in the Jobs table.


I would have:
tblCustomers
--------------
CustomerID_PK (primary key - Autonumber)
CustID (Text)
CustFirstName (Text)
CustLasrName (Text)
CustAddress (Text)
CustCity (Text)
CustState (Text)
CustZip (Text)


tblJobs
--------------
JobID_PK (primary key - Autonumber)
CustomerID_FK (Number - Long Integer - foreign key)
JobNumber (Text)
JobName (Text)
JobLocation (Text)
JobCity (Text)
JobDesc ( Text or Memo)


CustomerID_PK ---> CustomerID_FK -- referential integrity set, NO cascading updates/deletes

[PhaerieTail]

Customer info is in the Customer Table. Jobs Info is in Jobs Table. The CustomerID represents the relationship PK-FK between the Tables.
Please tell us in plain English the process(es) you are trying to support.

I'd like to have my boss be able to click any job and view the job information in a form, as well as a snapshot of customer information - contact person, email for invoices, etc. If a contact person changes, I would like for it to be edited in the customer table and then display the new information in the job form. I can currently edit the customer ID number and have cascading updates, but I can't sort how to make it update the customer name, contact info, etc without updating each field individually.

[PhaerieTail]

I'll tell you how I would design the tables.


Terrible idea. There should be NO customer info in the Jobs table.


I would have:
tblCustomers
--------------
CustomerID_PK (primary key - Autonumber)
CustID (Text)
CustFirstName (Text)
CustLasrName (Text)
CustAddress (Text)
CustCity (Text)
CustState (Text)
CustZip (Text)


tblJobs
--------------
JobID_PK (primary key - Autonumber)
CustomerID_FK (Number - Long Integer - foreign key)
JobNumber (Text)
JobName (Text)
JobLocation (Text)
JobCity (Text)
JobDesc ( Text or Memo)


CustomerID_PK ---> CustomerID_FK -- referential integrity set, NO cascading updates/deletes

Like I said, Access breaks my brain for reasons I'll never understand.

So remove the customer field from the jobs table and leave just the customer ID, then refit the split form I have to pull the customer information from the customer table based on the customer ID in the jobs table, correct? Clever, thank you!

[mike60smart]

There is NO need to display the Customer information in the Jobs table.

The idea is that you enter the Customer details ONCE only in the Customer table

That Customer will have Many Jobs

The Many Jobs are displayed in the related Jobs Table

[orange]

Describe to us a typical day at your office. We need to know what constitutes a Job. We need to know something about each Customer. How does Contact relate to Job and/or Customer. Where does Invoice fit? Seems there would be an Invoice if someone contracted/purchased/ordered a service or product for some amount of money and you would bill/invoice that someone(Customer).
Would be helpful to readers and you if you put the pieces together as they fit within your "Business".

Can a Customer have 1 or many Jobs? Is a Contact for the Customer or the Job or both? Your post seems to indicate a contact could change--how would that happen and what would you need to record?

My first guess is that you do not have enough tables to represent your business processes.
You may get some insight from these attached jpgs (generic data models of different requirements). They are meant to show how tables may relate. I'm aware your business is different. These are just some samples I have from different older posts.

[PhaerieTail]

I cannot thank you enough - this is the sort of information I've been desperately needing.

I just started, but we do service work. Invoices for service - electrical, plumbing, HVAC, that kind of thing.

This is how I'm working with my current setup:

Job comes in and is assigned a job number. I make an entry on the table (in the future, I'd like to be able to add new jobs via a form, and I just haven't had a minute to find a tutorial for an Add New button yet, but I have the form set up.) I find the customer ID number in my customer table(I'd like to avoid having to keep it open as a reference to the customer number, but I'm not sure how to make that any easier for myself than literally just finding their name and putting in the number beside it.) The job invoice has been added.

If I have to add a customer, same deal - autonumber PK customer ID, but I'll eventually be making an Add New button.

Job is finished and ready to bill. I make sure the information is complete (don't always know how much the job will cost at the time of entry) and create the invoice. (I do this currently in Word, but I'd like to find a way to automate this information based on information in the DB, so I can make a 'Create Invoice' button and it exports information to a PDF.) I changed the job status to Billed.

We receive the payment for the job. I add an entry in the Payments table(also working on getting a form for adding new). I mark this off in the notebook my boss has been doing all this tracking in for the past few years. The information shows up in my Balance query, but it's weird - say the job was $1,000, and customer paid out in 2 checks, $500 each. I have a line for each payment entry, rather than it consolidating and marking it as $1,000 paid toward the invoice. It's really frustrating because it also doesn't keep a running total - it's $500 toward a $1,000 balance, with $500 remaining to be paid... twice. So it never 0's out. I'm still working on this.

Creating more information in the customer's table and using a customer ID has solved this problem for me temporarily - keep an eye out as I continue to ask more questions with really obvious answers!

Really quickly though, I've never had an opportunity to look over a functional Access DB before - do they all have so many tables for such little information per table? I feel like I'm brute forcing it with the 4 tables I currently have - Jobs, Customers, Payments, Expenses - and trying to get information from more than 1 table represented on a form. For context, I'm an Excel master - I understand that Access is extremely different, but is it a case of trying to retrain my brain and just getting hung up on how I would do XYZ in Excel, and it not translating to Access?

[orange]

I don't want to dissuade you and depress your interest, BUT Access (database) and Excel(spreadsheet) are quite different. Most Access developers will tell you that the difficult part for you will be to "unlearn" your excel skills. Database requires a different mindset. The key to database is to get the tables and their relationships designed and tested/vetted to ensure the structure meets your business requirement(s). Some will overlook the creation of a data model, sample data and sample transactions to vet the model. But it's much like building a house- working to get a design that matches your requirement. Very easy to adjust the blueprint or model, but once you're into physical database (or a poor design) it is extremely difficult to make changes.

There are several articles and links to tutorials in the Database Design link in my signature. I recommend the tutorials from RogersAccessLibrary in those links. Each of those tutorials will take ~45 minutes to an hour to work through. I suggest you try 2 or 3 to become familiar with the process. You will learn and what you learn can be used with any database.

Good luck.

[Micron]

I just haven't had a minute to find a tutorial for an Add New button yet,

Research GoTo NewRecord
https://docs.microsoft.com/en-us/off...cmd.gotorecord
or
how to open a form in any mode (look for read only, edit, data entry)
e.g. posts 2 & 3
https://www.access-programmers.co.uk...d-mode.310955/

I find the customer ID number in my customer table(I'd like to avoid having to keep it open as a reference to the customer number, but I'm not sure how to make that any easier for myself than literally just finding their name and putting in the number beside it.)

Typical: 2 column (at least) combobox; rowsource is customer list via query/sql statement; col1 is custID (autonumber from table?); col2 is name; choice is saved to the WO. combo usually has no control source property unless you need that and understand that playing around (e.g. choosing a different customer on an existing record then doing anything to save that record will change the cust ID). If you don't know the user actions that can cause this, research that too. It is enough to move off the record, never mind consciously clicking a save button.

but I'll eventually be making an Add New button.

Not necessary if you're launching forms from a "switchboard" form (don't use the built in one) but OK if you're perusing existing customer job records and want to add a new job record. Same if you have a customer detail form. If not, research "Not In List" event for how to add customers into the customer combo if you have one.

automate this information based on information in the DB, so I can make a 'Create Invoice' button and it exports information to a PDF.

Fairly easy to do - need that as a db report first.

I have a line for each payment entry,

That is the way you should do it in a relational db. $ quoted (from quotations or perhaps just WO table) minus sum of receipts against a WO number

do they all have so many tables for such little information per table

Those numbers are solely dependent on the need. I suspected you were adept with Excel. Forget what you know about Excel for this task. You are already ahead of so many people who dive in and try to create relational db's using an Excel brain. I think it's common in db circles to cal that "Excelitis". If you read, understand and adhere to all of the following, I guarantee you will suffer far less than those people. Most important is to understand db normalization. If you don't, find source that are better for you. You must understand Entity (table) and attributes (fields) of the entity.

EDIT
If it helps, I had a long career in maintenance, reliability, condition monitoring, etc. and created many an Access query and or db to suit the need.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[PhaerieTail]

I don't want to dissuade you and depress your interest, BUT Access (database) and Excel(spreadsheet) are quite different. Most Access developers will tell you that the difficult part for you will be to "unlearn" your excel skills. Database requires a different mindset. The key to database is to get the tables and their relationships designed and tested/vetted to ensure the structure meets your business requirement(s). Some will overlook the creation of a data model, sample data and sample transactions to vet the model. But it's much like building a house- working to get a design that matches your requirement. Very easy to adjust the blueprint or model, but once you're into physical database (or a poor design) it is extremely difficult to make changes.

There are several articles and links to tutorials in the Database Design link in my signature. I recommend the tutorials from RogersAccessLibrary in those links. Each of those tutorials will take ~45 minutes to an hour to work through. I suggest you try 2 or 3 to become familiar with the process. You will learn and what you learn can be used with any database.

Good luck.

Not at all dissuaded - I knew that they were extremely different going in, though I certainly didn't know how different. I was hoping it might help even a little with expressions, but honestly it's like Access is so separated from Excel as to not even be in the same software pack. I'm struggling actively to unlearn, although everyone here is so helpful, so it definitely makes it easier! I'm going to go through those tutorials this week to learn more - I wanted to kind of get a proof of concept for my boss to justify me doing almost nothing while I figure out how to make something actually cool!

[PhaerieTail]

Thank so SO MUCH for these, I'm going to scour all the resources y'all have been giving me this week. It's just so jarring to go from being able to do almost anything to have to ask questions to find out there's a wizard for that in the first place. I appreciate y'all's patience with me and encouragement as I bang my head against the wall trying to learn this by force lol

[Micron]

It's just so jarring to go from being able to do almost anything to have to ask questions

I suspect we can all say we've been there. I help out a fair bit at Mr. Excel and have to say that every day I'm amazed to what lengths people want to go to make it work like a db, and just how much harder that really is. Even if you don't get the nod for work related purposes, I think that if you got your hands on an older used copy of Access, or even forked out 8 or 9 bucks per month for 365 and learned on your own time, one day you'll be glad you did.

I think my crowning achievement was building a db that in 4 minutes could create the paperwork that took 4 guys to do in a whole day on overtime. Best part is, no one complained because as salaried guys, we regularly accumulated unwanted OT and certainly didn't want to work Saturdays doing paperwork. I've yet to see anyone present a reasonable challenge that a properly designed db couldn't handle. Can't say that for Excel.