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
> Database Relationship Window, Access 2013    
 
   
ecovindaloo
post Jul 31 2020, 03:19 PM
Post#1



Posts: 321
Joined: 1-November 08
From: New York Area, USA


I've been working on a new Access program for a few months now. When I first started I added my tables into the DB Relationship window so that I would be able to setup my one to many relationships and be able to delete records from many tables in one shot. This all works fine.

My issue now is I've added two new tables to the database. When I add them into the DB Relationship window I'm able to create the one to many relationship but will not be to do a cascade delete with this table. I've attached a snapshot. The table I'm adding is the tblProtocolCountryDrugCalculator table on the right side of the snapshot. On the left side the window is open showing the join. You can see in the snapshot that the one to many relationship is created but I'm not able to check any of the fields in that box.

Any ideas why this is happening and how to make this work?
Attached File(s)
Attached File  DB_RelationshipWindowSnapshot.JPG ( 130.96K )Number of downloads: 16
 
Go to the top of the page
 
theDBguy
post Jul 31 2020, 03:24 PM
Post#2


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Hi. Does it make a difference if you change the Join Type to an INNER JOIN?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jul 31 2020, 03:33 PM
Post#3


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


Access won't let you create referential integrity because there is a disparity in the values of the two linked fields.

You will need to determine which table has records missing from the other before RI can be imposed
Try using an unmatched query wizard to identify records in table A but not table B
Then repeat for those in table B not in table A

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ecovindaloo
post Jul 31 2020, 06:03 PM
Post#4



Posts: 321
Joined: 1-November 08
From: New York Area, USA


If I change to an inner join I'm still not able to do a cascading delete.
Go to the top of the page
 
ecovindaloo
post Jul 31 2020, 06:03 PM
Post#5



Posts: 321
Joined: 1-November 08
From: New York Area, USA


I'll try creating the unmatched queries and see what I come up.

Thanks for the help.
Go to the top of the page
 
kfield7
post Jul 31 2020, 11:29 PM
Post#6



Posts: 1,083
Joined: 12-November 03
From: Iowa Lot


You have other things going on that are red flags to me.
You have 7 tables with the field pair "ProtocolID" and "ProgramID". Makes me wonder if, at least for some tables, you shouldn't instead just be referencing the ID field from tblProgramProtocols, but I don't know the details of your design.
Also, IMHO it's not the best practice for every table to have the generic "ID" as the name of the primary key. For instance, in tblProgramProtocols I'd use something like "ProgProtID" so when you're working with a bunch of these in one query or report, you know immediately which ID you're working with. My personal preference is to use something like "ProgProtPK" so it's clear that's the primary key. Then the foreign keys would be named "___FK".


This post has been edited by kfield7: Jul 31 2020, 11:33 PM
Go to the top of the page
 
ecovindaloo
post Aug 1 2020, 09:07 AM
Post#7



Posts: 321
Joined: 1-November 08
From: New York Area, USA


The reason I was referencing multiple fields was because when the program was evolving we weren't sure of the business flow and what tables were going to be relating to each other down the line.

I worked at a place which had an Access program for hedge funds. At the time it was the biggest program in the world running Access. We could call Microsoft and get them on the phone and they would also call us. The guy that created the program (over 300 tables), used ID as the name for his primary key fields. So I've just gone with that over the years. I've seen it both ways though.

Go to the top of the page
 
tina t
post Aug 1 2020, 11:48 AM
Post#8



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


well, Microsoft used to have a helpline for technical assistance - whether they still do, i don't know. i called it a couple times, back in '99 or '00 - and probably got a callback, because i remember the person helping me needed to research an answer to my problem. and the size of a database is not a reliable indicator that best practices were implemented in building it.

i'm not belittling your past experiences/observations. nor am i trying to change your mind - how you design your dbs is up to you. this post is for the benefit of future readers of this thread, which many times will include newbies who are just learning the craft, and most of the experienced developers at UA try to emphasize best practices to help them get a good leg up.

so for those newbies: most experienced developers will offer the same advice as kfield7 - using the same name for the primary key field of multiple tables makes it harder to write and debug both queries and code; better to give the primary key field of each table a unique name. my personal naming convention makes all my fieldnames unique within a single database, by using a unique prefix for each table, and adding the prefix to each fieldname in that table.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post Aug 1 2020, 03:06 PM
Post#9


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


MS does still have a telephone helpline.
I was asked by MS to use it after reporting that the Windows 1803 update had broken the geolocation code.
To my amazement, the support was excellent and only 3 weeks later MS released a fix for the issue.
However the fix was only partly successful so I contacted them again by phone a couple of months later. Issue was resolved in the 1809 update.
Whilst that experience was good, I do wish MS were as prompt with all major bugs!

@Ecovindaloo
Have you sorted out the unmatched records yet and from that managed to apply referential integrity.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ecovindaloo
post Aug 3 2020, 02:31 PM
Post#10



Posts: 321
Joined: 1-November 08
From: New York Area, USA


I created the unmatched queries and fixed the data. But I'm still not able to get the relationship working so that I use referential integrity.

Any other ideas I can try?
Go to the top of the page
 
isladogs
post Aug 3 2020, 02:43 PM
Post#11


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


If there are no records in one table that are missing from the other, then it should be straightforward to apply R.I.
Recheck the following:
Attached File  Capture.PNG ( 14.89K )Number of downloads: 3


Not sure if I already suggested it, but have a look at the first part of my article: Relationships & Referential Integrity

Without seeing your data, I cannot suggest anything else to test at this point
So if none of the above help, can you upload a copy of the two tables, removing/modifying any confidential data

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ecovindaloo
post Aug 3 2020, 02:52 PM
Post#12



Posts: 321
Joined: 1-November 08
From: New York Area, USA


Thanks for sending that information. I'll take a look at your article.

It just gets frustrating because all of the other relationships between the tables work correctly with no issues. It's just been these two new tables that I've added.
Go to the top of the page
 
isladogs
post Aug 3 2020, 03:00 PM
Post#13


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


No problem. There will be a simple explanation.

BTW although the two fields you are trying to use for RI MUST be broadly the same datatype, they do not need to be the same size (as the screenshot states) as long as the data matches
As you will already know you can apply RI on an autonumber & a number field. Similarly for text(50) & text(100) as long as none of the matched records are longer than 50 characters

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:50 AM