Design of table structures (1 Viewer)

Charlie G

New member
Local time
Today, 14:03
Joined
Nov 10, 2019
Messages
5
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!
 

June7

AWF VIP
Local time
Today, 06:03
Joined
Mar 9, 2014
Messages
5,423
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?
 

plog

Banishment Pending
Local time
Today, 09:03
Joined
May 11, 2011
Messages
11,611
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.
 

Dreamweaver

Well-known member
Local time
Today, 14:03
Joined
Nov 28, 2005
Messages
2,466
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
 

plog

Banishment Pending
Local time
Today, 09:03
Joined
May 11, 2011
Messages
11,611
... dont really agree...then you can start playing with form

That's fine, but why do you think that? I explained my thinking.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:03
Joined
Jan 23, 2006
Messages
15,362
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 28, 2001
Messages
26,999
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.
 

Micron

AWF VIP
Local time
Today, 10:03
Joined
Oct 20, 2018
Messages
3,476
...and forget what you know about Excel!

Amen.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:03
Joined
Feb 19, 2002
Messages
42,970
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.

 

Attachments

  • TabbedForm.jpg
    TabbedForm.jpg
    100.5 KB · Views: 249
  • ThreeSubforms.jpg
    ThreeSubforms.jpg
    100.9 KB · Views: 240

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 28, 2001
Messages
26,999
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.
 

Charlie G

New member
Local time
Today, 14:03
Joined
Nov 10, 2019
Messages
5
Thank you everyone for your help and feedback on this! has been very useful indeed!
 

Users who are viewing this thread

Top Bottom