• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 61
  • Last Modified:

Simple MS Access Table Structure Question

Dear Experts,

So want to make sure I get this very simple database set up correctly wanted to run it by you all for your comments and suggestions. I figure since I am paying monthly for membership on this site I should take advantage of some very knowledgable talent here.

So I am try to create a database that will allow me to generate the type of report you see below attached. Basically I have a table of students (and I'm not sure I set this up right so I have thick skin let me know what your thoughts are) and from the table need to generate the report. As you can see from the report the Page Header will contain the student name and seminar (homeroom) teacher but then in the detail section of the report I need to print the dates in the date field in sequential order first the under the dates will go the interventions.

But here's the thing..each student record has a field for each category and whether or not the student 'needs' the intervention. For example if the Numbers and Operations field contains a "yes" or "1" then I would need for the Numbers and Operations intervervention to display on the report. I figured I would use an exandable textbox and only show the textbox 'if' there is a 'yes' in the Numbers and Operations field. Then I need to print the next date in sequence and then check to see if a student has a "yes" or "no" in the next category, "Algebra Functions".

There may be a senario where the student ONLY has a 'yes' on one category ( in other words only need intervention in one area) like "Data, Statistics & Probability" and that would go under the first date of February 1 - 15.

Hope this makes sense. Please let me know if you have questions.

I've also created a sample database to show what I have done thus far. No relationships set up.
Math-Intervention-IDP-Sheets-sample.docx
table-structure.pdf
Math-Intervention-IDP---Copy.accdb
0
shogun5
Asked:
shogun5
  • 9
  • 8
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think you need to remove those checkboxes from the Student table, and put them in a separate table, then add a Join table to determine which STudent needs which Intervention:

tIntervention
InverventionID
InvetventionName

tStudent_Intervention
StudentID
InterventionID

You could then easily determine if a Student needs a particular Intervention by querying the Join table:

SELECT COUNT(*) FROM tStudent_Intervention WHERE StudentID=1 AND InterventionID=3

Do all interventions happen on the same date(s), for all Students who need that Intervention? Or could Derrick take the NumbersOp intervention on a different date than Adrian?

Referring to your report, where does the data in those boxes come from? I don't see anything in the database where you could generate those boxes.
1
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ditto what Scott said, but I would add "InterventionDate" to tblStudent_Intervention.   That might be an actual field (if they can be individual), or a FK (Foreign Key) to tbDates (didn't look at the db, so that could be a typo, but if not, change it to tblDates).

tblIntervention  will also need CategoryID
InverventionID
InvetventionName
CategoryID - FK to tblCategories

You would then group by date and category for the report.

I would also suggest renaming "ID" to CategoryID in tblCategories, and renaming tblDates to tblInterventionDates

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, on tblStudent_Intervention, it needs to look like this:

tblStudent_Intervention
StudentID  - Long - FK to tblStudents - PKa
InterventionID - Long - FK to tblInterventions - PKb

 "PKa" and "PKb" meaning "Primary Key", parts A and B, or in other words when combined, they are unique.  Also many would do it this way once it expands beyond just a linking table (which is just keys), or just for the sake of consistency:

tblStudent_Intervention
StudentInterventionID - Autonumber - PK
StudentID  - Long - FK to tblStudents - CK1a
InterventionID - Long - FK to tblInterventions - CK1b
InterventionDate

Here it's a single field "Primary Key", and StudentID and InterventionID form a candidate key (one that could be used for a primary key).

If you want to understand a little more about keys and what's going on in this example, read this:

https://www.experts-exchange.com/articles/2041/The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

 which will make that clear.

Jim.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
shogun5Author Commented:
Scott,

Thank you for the comments. Yes, so student A may only need the "Algebra and Functions" intervention and would start on Feb 1 - 15 for the student.  So all students needed 'any' type of intervention, 1 through 6, would start on Feb 1 - 15 . Some students with one intervention would only have one intervention listed Feb 1-15 even if the intervention was the last intervention category listed, which representation/reasoning. Other students who may have the need to for more interventions would of course start on Feb 1-15 but then have their next intervention on Feb 20- Mar 7 and so on. So the dates are listed on the report sequentially but only have data after the dates if there is another intervention needed.

So for example the dates are:

Feb 1-15
Feb 20-Mar 7
Mar 9-Mar 23
Mar 27-Apr 18
Apr 23-May 7
May 9-May 21

These dates should list on the report in sequential order. Now if Student A has the following interventions (2), (3), (5) and Student B has the following interventions (1), (3), (4), (6) then the report for each student should look like this:

Student A:
Feb 1-15  - (2)
Feb 20-Mar 7  - (3)
Mar 9-Mar 23 - (5)
Mar 27-Apr 18
Apr 23-May 7
May 9-May 21

Student B:

Feb 1-15 - (1)
Feb 20-Mar 7 - (3)
Mar 9-Mar 23 - (4)
Mar 27-Apr 18 - (6)
Apr 23-May 7
May 9-May 21

So I need to figure out a way to set up the tables and relations so I can simply run through the dates and if there are more intervention remaining for a student add them under the next available date. Hope this makes sense .

I haven't added data that will go in the intervention grids on the report. Not sure how I am going to do this yet but if I can set up the database to that I can run through each student and tack on required interventions to the set dates on the report I can then figure out how to fill in the intervention requirements.

So I am not completely getting the concept of your join table the querying.

If I have the following tables:

tblStudent
ID - PK
fname- TEXT
lname - TEXT
hrteacher - TEXT
grade - TEXT

tblStudent_Intervention
StudentID - FK
InterventionID - FK

tblIntervention
InverventionID - PK
InvterventionName - TEXT

I am not quite understanding how I would know from the database that Student A who needs the second intervention (Algebra and Functions) get that intervention listed under the first date in order.

I really want to understand this. Thank you for your time and any further guidance you can provide would be most appreciated.
0
 
shogun5Author Commented:
Jim,
Thank you for your comments and I read your article on primary key vs. surrogate or meaningless keys. Very good and thank you. This makes sense. Since I am not currently using the student ID as registered to the student I am just assigned a "meaningless" auto-number PK for now. In the sense that we are looking for a report and not concerned too much with identifying the student individually.

Without repeating myself above would you mind reviewing my comments to Scott above and providing further insights based on the questions noted?

Thank you again!

Mike
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You add a record to tblStudent_Intervention for each Intervention needed for a Student. So if StudentA has 3 Interventions, there would be 3 records in the Join table for that Student, and you could assign a date slot for each.

You may want to add another column to tblStudent_Invervention to relate the Date of the Intervention:

tblStudent_Intervention
StudentID - FK
InterventionID - FK
DateID - FK to your tblDates

When assigning a Student to an Intervention, you would also assign a Date to that Intervention. This is typically done in a Master/Detail form, where the Master would show data about the Student, and the Detail would show data from tblStudent_Intervention, with dropdowns to show the Intervention table and the Dates table. You could make all 3 fields a Unique Index, which would prevent duplicates on those three columns (so you can't set the same Date for two different Interventions).
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< would you mind reviewing my comments to Scott above >>
 
 Scott's already covered it.

Jim.
0
 
shogun5Author Commented:
Scott, Jim,

Please see attached database sample and relationship below:

 

I hope I am getting closer here.

Can you provide suggestions. I tried to follow your instructions. The only concern I have is this. So I know the required interventions for each student noted by the boolean values on the original table. How do I transfer these values over to the intervention table for each student without having to enter one by one?

Also when I tried to create a report from the student table using the report wizard the sample value that I put in the intervention table for a student did not show up as a related table on the report so I could not 'grab' it.

sorry guys...kind of new at this so wanted to make sure I am getting this right. Thank you so much for your time and insights.relationshipsMath-Intervention-IDP---Copy.accdb
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How do I transfer these values over to the intervention table for each student without having to enter one by one?>>

 Use an append query to insert records into tblStudent_Intervention.   tblStudent will be your input and it's simplest if you deal with one intervention at a time.  I haven't looked at your DB - if you do have a lot of data in there already, there is a faster way to do it, but dealing with them one at a time I think will be clearer.

 Use a fixed value for the InterventionID and DateID in the query to append to the fields.   Execute the query, then modify it and do the next one.

and of course, make sure you have a backup of the DB before moving the data into the new structure.

BTW, a tip here: since you are almost always dealing with the ID's in tables, personally I find it easier to use:

InterventionID

instead of

ID

 As the field names for keys.   Reason is that you are *always* then stuck with giving the table name.   ie.

tblIntervention.[ID]
tblDates.[ID]

 because 'ID' alone is not unique when working in the query designer or code.   It also makes it easier to know which ID you dealing with when your looking through something.   You only need to look at the field name.

<<Also when I tried to create a report from the student table using the report wizard the sample value that I put in the intervention table for a student did not show up as a related table on the report so I could not 'grab' it. >>

  You need to construct a query first, just as you have your relationship diagram, save it, then base the report on that using the query as a "table".   One point here; don't do any sorting in the query for the report.  

  For reports, sorting and grouping is specified in the report.  Any sorting in the query will be ignored.

  It's for this reason that often developers will create a separate query for each object, and then name the query so it's related to that object.  For example, if the report was  'rptStudentInterventionDetail", then the query might be something like   'qryrptStudentInterventionDetail"

  One mistake that often is made is trying to use one query for everything.   What happens is that you start to have it include more and more data so it can be used everywhere, but that means that it's also inefficent.

  If I only need three fields in a report, but ten for a form, then for the report, having the other seven fields in the query is a waste.

  So pretty much stick to using one query per object.  App will be a lot easier to maintain to.

Jim.
0
 
shogun5Author Commented:
Jim, okay...on the road today but I will look at this tomorrow. Thanks Jim! I really appreciate. Before I close this thread wanted to make sure I get the part you are talking about above. Thanks!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I was working on this and something that I need to get clear;   Are the interventions based on the category as a whole, or on each of the individual steps?

 For example, do I assign an intervention for "Number and Operations", or might I schedule interventions for HSN-RN.A.1 Rational exponents and radicals, along with HSN-Q.A.3 Measurement precision, and Convert Units (metrics) only?

 From the layout of your student table, I'm assuming it's the first, but that's not the design we've been discussing and that's probably why it's not clear on what you need to do.  The model we've been talking about doesn't quite fit.
 
 Let me know for sure and I'll get this started for you.

Jim.
0
 
shogun5Author Commented:
Hello Jim,

Here are the answers to your questions:

"Are the interventions based on the category as a whole, or on each of the individual steps?"
Ans: interventions.jpgThe Interventions are based on the Category as a whole. Yes you are correct, the report I need from the Database will print out all the interventions for that category. Would be nice to relate category, e.g., Numbers and Operations to the designated interventions noted in the subsequent table under each category. Then if need be we can update the interventions tied to that category. So HSR-RN.A.1 would be assigned to the "Numbers and Operations" category.

Thank you so much for assisting me with this. If you can assist with the underlying relationships I can certainly take it from there with the reports and forms.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, attached is a db and here are a few comments:

1. I'm not totally sure it's 100% yet as I'm not clear on exactly how interventions are handled and done.   For example, right now there is nothing to stop a student from having multiple interventions for a given date range.   There is also nothing stopping you from having overlapping date ranges.

2. You'll now see that there is an interventions table (which was categories before), and intervention tasks, which is the HSN-RN.A.1, HSN-RN.A.2, etc.    I may have missed the boat here a bit and you may need to re-introduce categories.   What I'm not clear on is if "Number and Operations" might have multiple interventions within it, composed of different sets of tasks (meaning it really is a category and the tasks when combined form the "intervention"), or if the set of tasks you have on the form is the defined intervention of "Number and Operations".

 The other thing that I may not be grasping here is if HSR-RN.A.1 could be assigned to more than one intervention.  If so, then the design is off.

 3. I've entered some of the data in and assigned to students, but not all for the sake of time.  But there is enough there so you can see how it's done.

4. I left the sub-data sheet feature on.  This is where you can open a table like tblInterventions, click the "+" in the row, and drill down and enter in the tasks.    Normally this is something I'd leave off, but since your pressed for time, it would allow you to enter data without forms for the moment.

5. Along with that, I did NOT define look-ups on the table fields.   You may want to do this.   What it does basically is give you a drop-down when your looking at a table for a related table.

  Right now, if you open tblStudents and click the plus, you'll see the related interventions.   But in the interventionID column, you'll see this:

Region-Capture.jpg
  which is the actual key value of the related record, Algebra and Functions:

Region-Capture2.jpg
  That's great for showing you how the records relate to one another, but if your going to use this for data entry, then it would be easier to choose the name.   You can do that by going into table design and defining the lookup:

Region-Capture3.jpg

 However when you do that, you no longer see the key values, so it can be confusing on what is actually stored in a field in a table.   Again, this is something I'd not normally turn on because I'd be doing forms, but you may want to if your going to do data entry in the tables for now.

6.  You'll find a new query, qryrptStudent, which "flattens" the data for your report.    For reports, this is quite typical as the report engine itself will do the sorting and the grouping.   It needs to do that so it can produce totals and do other things.

   When designing forms, you don't flatten things but will follow the relationships.   For example, you'll probably have a main form/subform combo for Students and their Interventions (think of the drill down you get when you open the students table).

   Note that you can do main/sub type setups in reports as well, but usually you don't get as much control as when you flatten the data and then do it all in a single report.  Their good though when all you want to do is list some records from a related table and nothing else.

7. Your report format will be difficult to do as you showed it.  While Access can do multiple columns, it's either down (the entire page), then across the columns, or across the columns, and then down the page.     To do a setup like you have where it's five items down, then five in the next column, then move down the page is difficult.   Not impossible, but it does take some effort.

  I left it this way because of that and also it's easier for you to see what's going.

8. Grouping for the interventions may be off in that I did it by ID and not based on the name.  So within a single date range, if there were multiple interventions, it would be by the ID and not alphabetic.

9.  I used a couple of different techniques in the report to show you how various things are done, like combining first and last name for the full student name, and formatting the start / end date.  One of the things you'll find about Access is that there are different ways to do things.  For example, I could have defined the full student name in the query just like I did it in the control on the report, and then referred to that field in the report rather than combining it there.

   and a note on dates; you always want to store a date not text.    Given a date, you can display it anyway you want and leaving it as a date, you can perform operations on it easily.   Same applies to numbers (don't store as text unless you have a very good reason to do so).

Think that's enough for now<g>.  Fill in some more data and see if it works and let me know how it works.  

Don't be afraid to holler with any questions on what I did.

Jim.
Math-Intervention-IDP_Updated.accdb
0
 
shogun5Author Commented:
Jim,

Thanks but I don't see an attachment...

Mike
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Site lost all my file uploads :(    First time that's ever happened.

I edited my last comment and added everything back in.

Jim.
0
 
shogun5Author Commented:
Jim,

Wonderful! This is exactly the set I need to get started. Thank you for helping me figure out relationships. The only thing that I'll need to change is the relationship with the interventions dates. In other words the dates are not related to the interventions is any way but rather windows of time when the interventions need to be completed. Somehow on the report I just need to write some code to display the dates in sequential order as the interventions are printing. So if a student has only two require interventions then just before the first group of interventions the Date of Feb 1 - 20 will print, then just before the second intervention is printed on the report the next date in sequential order will print February 20 – March 7 and so on. Not sure if you know of a simple way to do that in VBA otherwise I'll have to scan the net for a solution. Anyway, closing this question for now. As Pat mentioned you probably don't need the points but I just wanted to say thank you for your time and efforts. I really like this site and learn a lot from you guys.
0
 
shogun5Author Commented:
Oh, and thanks for the point about dates. I'll fix that.
0
 
shogun5Author Commented:
Here is the report rough draft as how I am going to use it. I'll clean it up later.  But I think will work. I just need to remember how to create a new report for each student and not have it all on one report.

Report Rough Draft
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Somehow on the report I just need to write some code to display the dates in sequential order as the interventions are printing. So if a student has only two require interventions then just before the first group of interventions the Date of Feb 1 - 20 will print, then just before the second intervention is printed on the report the next date in sequential order will print February 20 – March 7 and so on. >>

 That is the way it is written.  

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now