Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2019, 12:57 PM   #1
Charlie G
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Charlie G is on a distinguished road
Design of table structures

Hi all,

working basically on a very large document library and am figuring out design and structure before i put anything into practice. Got to break away from flat table of Excel urgently as it gets too unwieldy and slow.

IF i need a lot of linked data regarding a document - Doc name etc in 1 table, source (list of customers and agencies) in table 2, type of document in a 3rd table, content category in a 4th etc, is this the best way of structuring the database?

WOuld i be right in then creating a form which covers all linked tables to then input new information, some of which will be drawn from content listed in other tables such as categories?

Huge thanks in advance!

Charlie G is offline   Reply With Quote
Old 11-10-2019, 01:15 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,462
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Design of table structures

No, would not use a single form that links all table. A form should do data entry/edit for only one table. You can use form/subform arrangements.

Have you studied an introductory tutorial for Access?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-10-2019, 03:10 PM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Design of table structures

Quote:
WOuld i be right in then creating a form which covers all linked tables to then input new information...
Tables then Reports/Queries then finally Forms. That's the progression of how you should build your database. No point making amazing input forms if you aren't sure you can retrieve the data in the manner you need.

Create your tables, throw some test data in there then build the queries and reports you need to make sure those tables are in fact properly structured to get the data you need out of your database. Then you build forms to interact with those tables.

plog is offline   Reply With Quote
Old 11-10-2019, 03:21 PM   #4
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 1,007
Thanks: 73
Thanked 52 Times in 45 Posts
MickJav will become famous soon enough
Re: Design of table structures

Quote:
Originally Posted by plog View Post
Tables then Reports/Queries then finally Forms. That's the progression of how you should build your database. No point making amazing input forms if you aren't sure you can retrieve the data in the manner you need.

Create your tables, throw some test data in there then build the queries and reports you need to make sure those tables are in fact properly structured to get the data you need out of your database. Then you build forms to interact with those tables.
Sorry dont really agree with that in my view the first thing you should do is write down what you think you'll need for tables, fields relationships then you can start playing with form and queries Etc
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 11-10-2019, 03:24 PM   #5
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Design of table structures

Quote:
... dont really agree...then you can start playing with form
That's fine, but why do you think that? I explained my thinking.
plog is offline   Reply With Quote
Old 11-10-2019, 04:59 PM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,278
Thanks: 96
Thanked 2,030 Times in 1,977 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Design of table structures

Charlie,

Spend about an hour working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in this link. You'll learn a process that you can use with any database.

If you don't get your tables and relationships designed and tested to meet your requirements, you can be in for a lot of pain and hurt with workaround after workaround to accommodate a design flaw.

There's lots of other reference material in that link that may be helpful. The "stump the model" link highlights an approach to confirm your design.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 11-10-2019, 08:49 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,713
Thanks: 93
Thanked 1,708 Times in 1,581 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Design of table structures

I'll avoid my usual stories and come up with a straight, relatively short answer.

Right now is THE MOST IMPORTANT PART of your project - the design phase. If you don't spend enough time here to fill in the design, I can assure you and bet the house, the dog, and the car that you WILL spend a ton of time later.

As a novice in this kind of planning, what you don't realize at the moment, but you WILL realize sooner or later, is that you are building a map for the territory that is your problem to be solved. You are making a computerized "model" of what you want to do physically. So take the time to assure that the map correctly corresponds to the territory.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-10-2019, 08:57 PM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,228
Thanks: 10
Thanked 230 Times in 218 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Design of table structures

...and forget what you know about Excel!

Amen.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-10-2019, 11:45 PM   #9
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 1,007
Thanks: 73
Thanked 52 Times in 45 Posts
MickJav will become famous soon enough
Re: Design of table structures

Quote:
Originally Posted by The_Doc_Man View Post
I'll avoid my usual stories and come up with a straight, relatively short answer.
But most of us enjoy ready you posts they are always full of goodness lol
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
The Following User Says Thank You to MickJav For This Useful Post:
The_Doc_Man (11-11-2019)
Old 11-11-2019, 06:20 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Design of table structures

Once you have broken the repeating data into separate tables, there are different ways to bring it back together for different purposes. For forms, you wouldn't use one massive query that denormalized the data and recreates the spreadsheet. That type of query would not be updateable. You would probably use a main form with several subforms. Depending on how much data you need to see and how many subforms you need, you might consider using a tabbed form so that you can see some data all the time and other types of data only when you click on a tab.
Here's a couple of samples. One shows only a single subform at a time. The other shows three because they are hierarchical. Pick an endorsement from the list, show details of the endorsement, show the data fields required to be filled when the endorsement document is printed.

Attached Images
File Type: jpg TabbedForm.jpg (100.5 KB, 39 views)
File Type: jpg ThreeSubforms.jpg (100.9 KB, 40 views)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-11-2019, 08:26 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,713
Thanks: 93
Thanked 1,708 Times in 1,581 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Design of table structures

Thanks for the vote of confidence, MickJay.

Charlie G.>> Be sure to take the time to write down a list of things you expect to get out from this app, whatever it turns out to be. I.e. be sure to know where you are going on this journey through Access, 'cause if you don't know your destination, how will you ever know you got there? This "where am I going with this" question is central and crucial to the design process. That is because Access is actually an app development tool, and apps have purpose. So be sure to identify the purpose(s).

Sometimes, once you have the list of purposes/goals, it is possible to work backwards from each of them to see how you will reach the goal from some given starting point.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-14-2019, 07:24 AM   #12
Charlie G
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Charlie G is on a distinguished road
Re: Design of table structures

Thank you everyone for your help and feedback on this! has been very useful indeed!

Charlie G is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table structures????? geoffishere Tables 9 03-20-2010 03:50 AM
Changing Table Structures from different MDB imakaratefiend Tables 1 04-05-2008 12:43 AM
Table Structures Dwight Tables 2 07-30-2006 03:01 AM
[SOLVED] Changing table structures JohnReid Tables 4 06-13-2006 10:19 PM
One-Too-Many table structures Cosmos75 Tables 3 07-02-2002 09:43 AM




All times are GMT -8. The time now is 10:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World