Relationship Issue (1 Viewer)

[Mark Nash]

Good morning all, from an absolute noob.

I am butchering the downloadable Task Manager available as standard with Acces 2016.

I have created a table with a list of clients (named IFA), and populated the table with the names of the IFA's, and created a field in the task table, so that I can later populate the table with the names of the IFA for each task, via forms & subforms, as well as a button or text box to enter the name of a new IFA. I am trying to create a relationship between the IFA table and the Task table. However when I try to create the relationship, i get the following. Can someone point me in the right direction?

Thank you

Mark

 

[Mark Nash]

This is probably a better visual

 

[Gasman]

As it states, you need an index. I generally use an autonumber in all my tables?. That is always indexed and unique.

 

[Mark Nash]

Thanks, yes, I tried to associate the autonumber field from the IFA table, to the IFA field on the task table, on a one to many basis, but when I do this I get a message saying that "Relationship must be on the same number of fields with the same data types". I know there is a simple answer somewhere, but I'm stuck

 

[Gasman]

Normally you would have a PK for your IFA. As mentioned I always used an autonumber field.
That then becomes a foreign key in your Tasks table.
The tasks table would have it's own autonumber primary key.?

Might be quicker to upload your DB?, however you still need to understand the links and how it works?

 

[Mark Nash]

Thanks Gasman, I will have to strip the data already on the DB before I can post it on here. However, from the relationship that already exists between the Contacts (ID) autonumber field on the Contacts table to the Assigned To Text field in the Task table, this works fine. I am just confused why a similar relationship cannot be setup from the IFA table? The attributes are exactly the same in each relationship

 

[Gasman]

So post a picture of the relationships.?

You can just copy the tables and not the data?
Do not touch your current DB, make a copy and amend that.

 

[Mark Nash]

This is how it currently looks:

 

[Gasman]

So I would be using the ID in IFA table (calling it IFAID) and link to the IFA in tasks, BUT that would be a Long field and I would call that IFAFK at the very least, possible TaskIFAFK if the IFAFK is likely to be in other tables?

 

[mike60smart]

Also the Join Option would be Option 1

 

[mike60smart]

Also, as Gasman has stated the Autonumber in Contacts should be named IFAID then in the related Tasks table
there should be a related Foreign Key named IFAID - Number Data Type - Long Interger

 

[jdraw]

In general, many will advise you to avoid using names with embedded spaces.
Suggest: FeeCodes or Fee_Codes, LastName...

Since you are a self-confessed "noob", I recommend you work thru one of the tutorials from RogersAccessLibrary
listed in this link. There are several articles related to Database Planning and Design and Access at that link.
Good luck.

 

[Pat Hartman]

When you use "ID" as the name of every PK, it is impossible to look at your tables and figure out which fields are supposed to match. How about ContactID --> ContactID or AssignedTo_ContactID if you want the FK to have a more specific name.

Relationships are ALWAYS between a FK and a data field which may or may not be a field of the PK in the child table. When the PK is an autonumber, the FK MUST be defined as Long Integer.

If you are using lookups on the table, you will be totally confused regarding data types among other things.