New DB advice

[Space Cowboy]

After recent frustration and deleting several queries in a temper tantrum i need to apply some kind of logic to my inadequate problem solving efforts.

these are my thoughts on suitable fields
Date
problem area - Form, report, query etc.
DB
Name - query name where problem exists
problem - I was thinking name of function I am attempting to work out SUM, Count, Dcount, sub query etc
Solution's source _ my idea, AW forum, web site name, etc
Solution Sql code
Error messages
Success box

All advice/ suggestions gratefully received. I would like to search on everything really and I am hoping that this project will help me understand what is going on a bit more.

PS. I have not made a DB before.

 

[GPGeorge]

Actually, I think you are making a very solid decision here. Learning anything new can be challenging, of course. Relational databases are among the more complicated things you can attempt, and doing it well even more so.

Two general thoughts come to mind.

The components of a relational database application, in a very general sense, are these:

• The data storage component.
• The Interface component.
• The logic component.

Data storage is the foundation. You must have a place to capture and store data. In a relational database application, that is the tables.

The user interface allows you (and others) to interact with the data. You must design and build forms and reports that make it as easy as possible to add new records, retrieve and modify existing records, and present summarized and aggregated data to users.

The logic supports automation of both the data storage and retrieval and of the interface forms and reports.

Everything starts with the data storage component. A properly designed group of related tables. Unless you get this part right from the beginning, nothing else will ever work as needed. Period. Get the tables right, the rest comes along.

The method, or process, through which we design and create tables is called Normalization. Don't even think about VBA or queries or forms. Get the tables built and validated.

I strongly recommend research on Normalization as the enabling study. Today, there are many good resources available, some better than others. Depending on your preferred learning style, books, videos or blogs might appeal most to you.

Here's one of the videos in a playlist of what I think are good introductory videos. https://youtu.be/GFQaEYEc8_8?si=Rl7Gb_1CcPdApyKU

One of the best books ever on Database relational table design: https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0321884493

 

[jdraw]

You might try one of the existing M$oft templates Bug Tracking, or Call Tracker or Customer Service. They would give you most of the basics and you can adjust/modify to suit your needs.

I agree with George's comments. There are links in my signature Database Planning and Design to many articles/videos.

 

[Gasman]

Here is a post that @Micron posts whe mentioning normalization.


Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html

 

[The_Doc_Man]

The "Old Programmer's Rules" come to mind.

#1: If you can't do it on paper, you can't do it in Access.

What this means in practical terms is that you need to come at the problem FIRST as though you were going to do a paper-and-pencil method and you have to lay out the steps required for each DB action you propose. This is a project analysis guideline. To keep things from getting "lost in the haze" you write down REASONS for why you chose a particular path or action. You document the steps of anything you discovered that you need to do during processing. You clarify the variables you need for computations including meaning and derivation. You keep track of objects, entities, or trackable actions you identify including name and purpose as well as detailed properties or attributes. You organize all of this into a "Project Bible" and you USE it at any time that you forgot why you did such a strange action that you now see before you through bleary, unfocused eyes... (maybe because it is too late at night and there isn't enough coffee in the WORLD to help you now). This Project Bible - on paper - will guide you through the programming steps later. But this is a key point: If you couldn't take that document and derive a manual method do the DB process on paper, you are not ready to implement it in Access.

#2: Access won't tell you anything you didn't explain to it first.

In practical terms, one of the items that should come out of your project bible is a list of desired outputs (with due attention to formats.) If you want to see a list of A as an output, you need to assure that your DB has an input that is or that can be computed into a list of A. If you need to know XYZ then you must assure that XYZ is input... or that X, Y, Z are input and the conversion formula is defined. Remember that Access is great at making tables, queries, forms, reports, macros, modules, relationships... and is great at causing violent hair loss by manually ripping it out of your scalp. When it comes to your business rules and properties, though, Access is dumber than a box of rusted garden tools. In order to assure that you have the right outputs, sometimes you might have to work backwards to assure the right inputs. You have to verify the computations. You have to pay attention to the details that somehow contribute to getting the job done. Because Access doesn't know what they are. All it does is build databases according to YOUR specifications.

#3. Never let the tail wag the dog.

This old phrase is derived from the simple idea that the dog wags the tail, not the other way around. Your app must parallel the actual flow of your business (except in the rare case where the business is in the process of changing at the moment.) When you design an input interface, take a look at what you were doing before. You don't have to exactly match those inputs - but your users will thank you for keeping it familiar. If your app says to do R, S, and T, those have to be actions that you would have done in a similar circumstance using paper. You need to remember that your app doesn't run the business. Your bosses do. (Or you do, assuming you're an entrepreneurial person.) You have to design an interface that recognizes and acknowledges that fact. Part of your process is therefore to assure that your outputs not only are present, but also they make sense. There is an old engineering and physics rule that says: When reality and your model disagree, reality is usually right.

 

[LarryE]

What types of activities are you wanting to keep track of? Are you wanting to track peoples activities (customers, clients, memberships, players on a team etc.)? Are you wanting to track activities (sales orders, purchase orders, investment income, sports scores etc.)? Are you wanting to track student grades for a school?

Write down what you want to keep track of. Then write down what the activity workflow looks like. Let's say you want to track investment income. We know within each Investment Organization (Banks and/or Brokerage Firms) you may have multiple accounts (Checking, Saving, CD, Investment). Within in account, you may have multiple individual investments (Stocks, Bonds, Mutual Funds). Within each investment, you may receive multiple income returns (interest, dividends etc.) during the month, quarter or year.

From this example, we know we will need at least:

1. An Investment Organization table (Banks, Brokerages etc.) OrgName, ContactName. StreetAddress, etc.
2. An Account table (AccountName, AccountType, TaxStatus, etc.) linked to the Organization table
3. An Investment table (InvestmentName, InvestmentType, IncomeType, IncomeFrequency etc.) linked to the Account table
4. A Transaction table to record each investment transaction (TransDate, TransType, Shares, Price, TransAmount) linked to the Investment table

This is an overly simplified example of how relationships work in ACCESS. It is a very different way of thinking about how to store information. Obviously, it cannot be compared to, or used like a spreadsheet. You are building a house, and the foundation needs to be properly formed first.

So do your research and view videos first. ACCESS is not something you want to learn by trial-and-error. You found that out. And so did I 24 years ago.

 

[Space Cowboy]

So much information presented there, thank you all,

I am starting to understand Normalization and Keys

 

[Lightwave]

One thing I wished I had learnt earlier on in my journey was how to show most recent or latest records of a child table..

This means rather than updating the parent table with say the latest owner of a valuable asset. You add all the owners to a child table and simply show the latest child value next to the asset name..

This is SUPER important for ownership of valuable assets and solves lots of problems. You maintain the audit trail of ownership can delete and edit old ownerships much much easier... it touches on the idea of IMMUTABILITY which is an important aspect of many systems design but is a pure design choice which may or may not be included in a schema design..

Here's a link to an example...MS Access - distinct list of child records

Here's a similar implementation for SQL Azure - SQL server example