UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Keeping Editable With No Primary Key, Access 2016    
 
   
arpirnat
post Mar 6 2018, 01:21 PM
Post#1



Posts: 110
Joined: 25-June 16



I have 3 tables: Consults, Employees, and Alias

Consults has a primary key of ConsultID
Employees has a primary key of Employee ID
Alias has a primary key of ID

The employees table has which consultgroup the employee belongs to, for example HomeCare.
The Alias table lists all of the different consults we have and which consultgroup they are linked to... so example would be HHA belongs to HomeCare
The Consults table has all of the consults, this includes customer names and so on but also the consult name.
So in theory the Employee and Consult tables are linked via the Alias table (basically a junction table).

The issue I have is that none of the fields linking these are the PK's, so when I use this relationship in a query it becomes uneditable. Are there any quick ways around this?
Go to the top of the page
 
orange999
post Mar 6 2018, 02:10 PM
Post#2



Posts: 1,868
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Please tell us more about "why there is/are no primary keys".
Go to the top of the page
 
arpirnat
post Mar 6 2018, 02:25 PM
Post#3



Posts: 110
Joined: 25-June 16



There are PK's, they just are not the fields I am referring to. Admittedly this is my first dive in to a junction table so perhaps I am seeing it wrong.
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 02:37 PM
Post#4


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


"...The issue I have is that none of the fields linking these are the PK's,..."

Why not use the PKs as FKs in the junction tables. That it, basically, the way it is supposed to work.
Go to the top of the page
 
RJD
post Mar 6 2018, 02:45 PM
Post#5


UtterAccess VIP
Posts: 9,238
Joined: 25-October 10
From: Gulf South USA


Hi: We're a bit thin on information to work with, but it sounds like you need a form/subform arrangement, with two different queries to feed them - and I see two possibilities...

1. Employees on the main form (separate query) and Alias in the subform (again, separate query). These to be linked by the employee ID on the form. Then the consult can be chosen with a combobox in the subform that lists the consults.

or

2. Consults on the main form, and Alias in the subform. Same approach with the combobox and employees.

But this is just a sketchy outline, given that we don't have your db to look at and make specific suggestions. It may be, as George pointed out, that some tables/fields might need rearranging.

HTH
Joe
Go to the top of the page
 
arpirnat
post Mar 6 2018, 02:49 PM
Post#6



Posts: 110
Joined: 25-June 16



A couple of reasons. One this is already built and trying to avoid rebuilding it completely.
There are about 40 employees and 120 different consults. I was trying to avoid entering a couple thousand records to do this the proper way.
This would, and tell me if I'm wrong, also mean if a new employee came in who worked all consults they would need 120 new records created for the junction to continue to function properly, correct?

I am trying to have tblEmployees.ConsultGroup (4 options) link to tblAlias which lists all of the consult titles and which consultgroup they belong to which then links to the tblConsults to only pull the consulttitles that belong to the group listed on the tblEmployees table for that person. Of course none of these are unique fields so are not assigned as PK's.
This post has been edited by arpirnat: Mar 6 2018, 02:52 PM
Go to the top of the page
 
orange999
post Mar 6 2018, 03:10 PM
Post#7



Posts: 1,868
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I see these mentioned in the thread, but do not understand how they are related.
Consult
ConsultGroup
Employee
Alias
Customer
Go to the top of the page
 
arpirnat
post Mar 6 2018, 03:22 PM
Post#8



Posts: 110
Joined: 25-June 16



the tables are tblConsult, tblEmployee, and tblAlias.
The fields being referenced are ConsultGroup and ConsultTitle.

tblConsult has ConsultTitle
tblEmployee has ConsultGroup
tblAlias has both.
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 10:58 PM
Post#9


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


The problem, as I see it, is that we've had bits and pieces of various tables with no real look at the actual tables. Please give us enough information about all three tables to do a useful review.

The best would be a copy of the actual accdb, with dummy data, so that we can SEE what's going on. If you can't do that, a screenshot of the relationship diagram showing those tables would help.

Thanks.
Go to the top of the page
 
arpirnat
post Mar 7 2018, 11:18 AM
Post#10



Posts: 110
Joined: 25-June 16



Attached relationship diagram. The large issue that I know is a problem is that none of the fields that are needed are unique in any table and the PK's from each table do not cross over, currently, to other tables.
The data pull is fine, just need to get it editable.
Attached File(s)
Attached File  Capture.PNG ( 10.98K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Mar 7 2018, 04:49 PM
Post#11


UtterAccess VIP
Posts: 9,238
Joined: 25-October 10
From: Gulf South USA


Hi: If your query is linked as you show in the post, then I don't think you'll be able to update anything within it.

Going back to my post above, you might try having one query to pull the Consult records (just that table) and use that in a main form. Then have another query that pulls the tblConsultAlias records and put that in another form, and use that second form as a subform on the other form, linked by the ConsultTitle/[Service Name] to the main form. On the subform, use a combobox bound to AlertTeam using [Consult Group] in tblEmployees as the row source.

Now, the junction table should be update-able in the subform, modifying, deleting or adding junction records. If you want to know which employees are assigned to which consults, then this will need to be captured, and this way seems the most usual.

But your table design is a bit fuzzy to me, so I might not be fully aware of how your are operating.

You might consider/try this and see if it meets your need. If you have problems with this, you might consider posting the database (no sensitive data, zipped, 2MB max size) and someone might be able to guide you in this, or give you an example by modifying the db.

I think this was mentioned before, but you should consider matching the field names between tables and also using a PK/FK ID approach between tables.

HTH
Joe
Go to the top of the page
 
tina t
post Mar 7 2018, 07:39 PM
Post#12



Posts: 5,603
Joined: 11-November 10
From: SoCal, USA


can you explain to us the business model that you're trying to support? don't use database terms; use "real world" terms, like you would if you were explaining the model to a new employee. and i'm not talking about how to do data entry; how are these entities related to each other in the real world?

hth
tina
Go to the top of the page
 
John Vinson
post Mar 7 2018, 08:07 PM
Post#13


UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US


As my friends have said, normally one would join from the Primary Key of the "one" table to a Foreign Key field of the same datatype in the child table.

However... if the field in the parent table is a candidate key (unique in every record, never NULL, and having no duplicates) then you can use the Indexes tool to put a unique index on it; if you put a nonunique Index on the corresponding field in the child table, a Query joining the tables on those fields will be updateable.

I don't see much point to doing so; if your ConsultTitle is unique, stable, non-null and reasonably short you could make IT the primary key. The benefit of using a surrogate key such as an Autonumber is that it comes with all these attributes automatically.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 11:06 PM