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
> Constraining Values Of Multiple Fields Based On One Field Being Populated, Access 2016    
 
   
mal0454
post Oct 29 2019, 01:51 PM
Post#1



Posts: 3
Joined: 29-October 19



Background: I have a 'Promissory Note' table with a 'Promissory Note Semester' field as well as fields of each type of promissory note: 'Housing' 'Short Term Loan' etc. A student can have multiple promissory notes per semester, but they don't have to have all of them. I can't set each field to "required" because they aren't necessarily required as the record in the table only exists is one of the multiple types is uploaded. I just need to make sure that if the "Promissory Note Term" is populated, AT LEAST one of the promissory note type fields need to be populated.

I tried a few SQL things (but I am new at that and really don't think I was even on the right track) and I also originally tried to have each promissory note type as it's own table and linked to the main Promissory Note table where the term field is housed, but it was cumbersome when I got to the user interface portion of the database, so it is currently all in one table. If having separate tables is truly the best option, I suppose I can go back to that, but I was wondering if there was something I can do to just require that at least one type be populated (can be more than one as well) when a new record/term is created.


Go to the top of the page
 
GroverParkGeorge
post Oct 29 2019, 02:00 PM
Post#2


UA Admin
Posts: 36,747
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

The underlying problem is NOT about constraining values.

The underlying problem is an inappropriate table design.

You have what is frequently referred to as a "spreadsheet-style" table. That's okay in Excel, but it's not how a Relational Database like Access works.

Here are some excellent resources that will help you redesign this into a proper Relational Database Application.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mal0454
post Oct 29 2019, 02:26 PM
Post#3



Posts: 3
Joined: 29-October 19



Okay, I will go back to the way I had it previously with separate tables and just work some more towards making the user side more friendly than it was previously. Thanks.
Go to the top of the page
 
GroverParkGeorge
post Oct 29 2019, 02:49 PM
Post#4


UA Admin
Posts: 36,747
Joined: 20-June 02
From: Newcastle, WA


Wait. It may be multiple tables, but not just ANY multiple tables. It's a Relational Database Application and it has to follow basic principles for that type of application.

The process is called Normalization. Invest some time learning how to create a normalized, Relational Database Application before going back to individual tables which may, or may not, be any more appropriate than one giant "spreadsheet" table.

I know it's a lot more involved than we'd like, sometimes, but getting your feet under you at step one really does make the whole thing more reliable, more efficient, and easier to work with in the long run.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Oct 29 2019, 03:20 PM
Post#5


UtterAccess Moderator
Posts: 12,253
Joined: 6-December 03
From: Telegraph Hill


Hi,

You don't want a table for each promisory note type.

You probably need 3 tables, perhaps 4:
Students
NoteTypes
PromisoryNotes
Term

The PromisoryNotes table is the important one - it will be a junction table, with:
- a field for StudentID that joins to the student table,
- a field for NoteTypeID joined to the NoteTypes table,
- a field for TermID joined to Term
and also fields for the other details like amount, date of note, due date etc.

Each note is a separate record in that table.

You can then query a student's notes by looking at all the records with their ID, and per term by filtering on the TermID etc

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mal0454
post Oct 30 2019, 01:03 PM
Post#6



Posts: 3
Joined: 29-October 19



@GroverParkGeorge I am familiar with normalization and so far the rest of my database hasn't given me problems, but I think this is my first junction table, so it's throwing me for a loop! And I'll gladly put in work now to avoid future problems later.

@cheekybuddha I think I am following along with your logic. Instead of having a different table for each of the types, I have a Type table, with each record in the table being a different type of promissory note.

I have attached a screenshot of the relationships to make sure we are on the same page.

If I interpreted you correctly, then I am now in the same predicament as I was before. The user end is not matching up what I was envisioning and I'm not sure if it is possible to get it there. I can definitely put in the work to learn how to do it if you point me in the right direction, but I just want to make sure it is even possible first.

This is what I was trying to accomplish on the user end:
My database is student based. So when the user opens the database, they search by student. Once the student is selected, I have tabs set up for various things such as correspondence, billing, promissory notes, etc. That is all done already, I am now at the point where I am working on the "Promissory Note" tab. For the purposes of the promissory note tab, we need to see them grouped by semester. My vision for the user is once they are in the student's account, they go to the promissory note tab, and they are able to view a semester and drill down to see the dates of the promissory note types that are strictly dates as well as see attachments of those that are the actual promissory notes and also have the option for them to upload it/input it if it does not yet exist. Within each semester, the student needs to have at least one type of promissory note, but may have multiple types of promissory notes, of which I want all to be housed under the semester. Some of the promissory note types are just a short text field, but some of them are attachments.

You suggestions seemed to be an improvement on my two previous methods on the logic end (thanks), but now I'm having a hard time on the user end. Let me know if you need more clarification.
This post has been edited by mal0454: Oct 30 2019, 01:07 PM
Attached File(s)
Attached File  Relationships.png ( 16.52K )Number of downloads: 3
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2020 - 03:13 PM