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
> Notebook Schema Design Help, Access 2016    
 
   
Oblio
post Jul 21 2019, 01:27 PM
Post#1



Posts: 264
Joined: 5-February 15



Hi,

I would like to design my own Notebook which I will use to keep track of various how to type articles and links.

The tables I plan are:

t_Notebooks


Notebooks_ID, Auto-number Primary Key
Note_Book_Name, short text
Created Date, short date

A One to many relationship between t_Notebooks and t_Sections:

t_Sections

Sections_ID, Auto-number, Primary Key
Notebooks_ID_FK, , Number format, Foreign Key to t_Notebooks
Ordinals_ID_FK, foreign Key to table Ordinals (contains numbers 1 to 50 to help in sorting Sections in a report)
Section_Name, short text

A one to Many between t_Sections and t_Pages

t_Pages

Pages_ID, Auto-number Primary Key
Sections_ID_FK, Number format, Foreign Key to t_Sections
Ordinal_Numbers_ID_FK
Page Title, short text
Page Contents
Page_Attachments

I am thinking I would create a main form based on t_Notebooks, add a combobox to display all of the Notebook Sections (single selection only allowed), and once a section has been selected, in a synchronized combobox, I would be able to select the page I need, which would then reveal the page contents.

Does anybody have a template that would be helpful, or recommendations on the best way to design this ?

Thank you for any help !

Bill
Go to the top of the page
 
mike60smart
post Jul 21 2019, 02:33 PM
Post#2


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bill

You would do this with a Main Form and Subforms


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Oblio
post Jul 21 2019, 03:30 PM
Post#3



Posts: 264
Joined: 5-February 15



Attached File  Relationships_for_BookSchema.pdf ( 36.62K )Number of downloads: 3

Attached File  BookSchema.zip ( 54.46K )Number of downloads: 1


I have created a Main form and related continuous form "Sections" ... it's how to make a more sophisticated display of the Pages and Page titles, with a viewer of that pages content that has me wondering.

I changed the tables to Books, Chapters and Pages to make this more general in case others are interested.

What I have so far is attached as a zip file.

I also attached a .pdf of the relationships.

Thanks very much for any help !

Bill
Go to the top of the page
 
mike60smart
post Jul 21 2019, 04:29 PM
Post#4


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bill

The attached is one way of laying out your Form

Select Book
Click on required Chapter
Enter Page Details

Attached File  BookSchema.zip ( 43.02K )Number of downloads: 5

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Oblio
post Jul 22 2019, 07:19 AM
Post#5



Posts: 264
Joined: 5-February 15



Wow ! Thank you so much... that works great !!!

So, you select the book from the unbound combobox placed in the header. On selection, the after Update event macro fires, and syncs the book as per the selection. The Master and Child Book IDs are linked in the Chapters subform.

Also, you created an expression called txtChapters_ID in the detail area above the Pages subform, and then, in the t_Pages subform, you linked the Chapters_ID to the Master txt_Chapters_ID expression, so that they synced.

Does that sound like I have figured it out ?

Thank you again SO, SO, MUCH !

Bill
Go to the top of the page
 
mike60smart
post Jul 22 2019, 08:13 AM
Post#6


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bill

Yes the Unbound Textbox just uses the Forms Reference to Obtain the Primary Key from Chapters


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Oblio
post Jul 22 2019, 08:48 AM
Post#7



Posts: 264
Joined: 5-February 15



Ok, thanks so much for your help, Mike !

I am still thinking about different ways to do the tables correctly, as I realized that the Title Page is not a Chapter, nor is the Cover, Foreward, Bibliography etc.

So, I created a table called "t_Book_Parts" and removed t_Chapters. I can still add a Book_Parts_Ordinal to help with sorting (for a report later), in case anyone is interested.

Thanks again so much for your help ! woohoo.gif

Bill
This post has been edited by Oblio: Jul 22 2019, 08:49 AM
Go to the top of the page
 
mike60smart
post Jul 22 2019, 08:50 AM
Post#8


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bill
Glad to help

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 02:42 AM