Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ Advice On Data Table Structure

Posted by: ntambomvu Feb 26 2020, 03:45 AM


Hello everyone,

I would welcome suggestions on how to structure a program to help with the production
planning and record keeping in my small factory.

My exposure to MS access is limited - I had experience in programming in dbase 4.2 ( dos )
and this is the language that i used to write the programs that I am currently using.
I am converting to access to upgrade to windows 10.

I will be working with the following main data tables
CUSTOMERS ( has affiliated tables for physical address, postal address, customer details
such as payment history, type of business, harmonized code number , etc)
INVENTORY ( affiliated tables are product detailed description, previous customers, supplier,
factory cad drawing numbers, stores item number, store shelf position etc)
JOBNUMBERS ( affiliated tables are job costing , machinist hours , tooling used, price ,
delivery date expected ,customer and customer order number Etc)
OLD JOBS ( a list of previously done jobs listed by jobnumber product description ,customer,
date and price)
MOULD LIST ( a list of all moulds in stock, store shelf position, drawing numbers, original customer )

As can be seen there are going to be many individual tables ( probably about 40 or 50 maybe more ) and each
interacting with others .

would I be OK to have one access instance with all these tables as .accdb tables within ? which seems
to be a nightmare to keep track of everything . the navigation panel will have all these tables each with its
own set of queries , forms and reports.
I am sure there must be a logical method of keeping all these files together in their own "basket"
Please offer suggestions about how this is done by other companies
Best regards

Fred Evans



Posted by: nvogel Feb 26 2020, 08:11 AM

You can add new categories and groups in the navigation panel and then put your objects into groups. Right-click on the navigation panel and then choose Navigation Options.

Have you looked for a ready-made software solution for this? There may exist some relevant software for your type of industry.

I suggest you prioritise and pick the right bits to get working first, especially if you are going to develop this alone. Creating 50 tables could be easy but creating all the forms and reports to support them is likely to take much longer. Start with just the most valuable things rather than take on too much at once.

Be aware that your Access application will be a desktop application only. If you want to use it from a browser or use a mobile device on the factory floor then Access isn't the right choice. I mention this only because it's a topic that people often ask about in these forums.

Posted by: projecttoday Feb 26 2020, 11:25 AM

I would say that you really needn't worry about keeping track of forms, tables, and reports. A properly-designed application doesn't really have that many of them measured against its function and you can find what you need during development okay. The finished product should not require the user even have a navigation pane, remember.

Posted by: Bullschmidt Mar 14 2020, 09:20 PM

QUOTE
would I be OK to have one access instance with all these tables as .accdb tables within ? which seems
to be a nightmare to keep track of everything . the navigation panel will have all these tables each with its
own set of queries , forms and reports.
I am sure there must be a logical method of keeping all these files together in their own "basket"


I would suggest something like this:
- Start up a new Microsoft Access database .accdb file and then import all those tables.
- Then recreate queries, forms, and reports as needed.

It does sound like a lot of tables. Don't know if the structure should be changed or not. But I would just try to perhaps get the current functionality going before trying to possibly change the structure.

Posted by: FrankRuperto Mar 14 2020, 09:33 PM

Perhaps many of those tables are lookup tables, or you need to use sub-typing in one table instead of creating multiple tables for the same kinds of data. Please read up on how to properly design a normalized database.

I also want to make sure you understand the concept that you need to separate the tables in to a separate backend accdb, and put the forms, reports, queries, etc. into a separate frontend accdb that links to the backend. This neeeds to be done since more than one user can be sharing the backend tables, andif you make any changes to the frontend forms, reports, queries you wont overwrite the data. Each user needs to have their own copy of the frontend accdb that links to the same backend, or all kinds of problems will happen.

Posted by: tina t Mar 15 2020, 02:35 PM

QUOTE
It does sound like a lot of tables. Don't know if the structure should be changed or not. But I would just try to perhaps get the current functionality going before trying to possibly change the structure.

i'd have to disagree with this. i can see no point in starting with a flawed structure and hoping to fix it later.

i'd recommend taking advantage of the fact that you are looking to migrate your process from its' current setup (DOS?) to Access. start by learning the principles of relational design. then perform a process analysis to ensure that you've fully identified, and are prepared to support, your business needs. then design your tables and relationships according to relational design principles. enter some test data, and make sure you can query out the data subsets that you need, for forms, for reports, for analysis.

once you're satisfied that the tables/relationships structure is correctly designed to support current demands and future expansion appropriately, meeting relational design principles, then start working on the forms and reports you need for user interaction with the data. after testing those out, and determining that the database will support your current business process, you can delete all the test data, and migrate your existing business data into the new database.

there's no quick and painless way to do this. designing the tables and relationships, after a thorough process analysis, is the most important part of building any relational database. if you skimp on it now, you'll pay for it later - as everyone who goes that route finds out, eventually. many, many of the questions posted here at UA - and other technical websites - about problems with queries, forms, and reports, are found to be caused by poorly designed tables/relationships.

hth
tina

Posted by: ntambomvu Apr 10 2020, 01:22 PM

hello all,
since posting the original question I have spent much time on trying out various options and layouts.
I think that i will be using the original main "layout" or "structure"
which will be grouped as follows

CUSTOMERS
INVENTORY
JOBNUMBERS
OLDJOBS
MOULDLIST

Each of the above will contain be a set of tables specific to the main heading. (e.g. customers will be related
to addresses;telephone numbers;demographic detail; etc etc)(Inventory related to productType,Prices;
storeLocation; etc etc)

Do you think I should make a seperate module for each heading? Like a container or "bucket" in
which to keep related data in one place?
I havent been able to find a definition of what the purpose of having "modules" . Will I be correct in
using modules to facilitate the above groupings?
It is have these different modules can i still have relationships between tables in other modules?
thanks again
fred

Posted by: tina t Apr 10 2020, 03:57 PM

QUOTE
Do you think I should make a seperate module for each heading? Like a container or "bucket" in
which to keep related data in one place?
I havent been able to find a definition of what the purpose of having "modules" . Will I be correct in
using modules to facilitate the above groupings?
It is have these different modules can i still have relationships between tables in other modules?
thanks again

i don't think i understand how you're using the word "modules" in this context. in Access, a "module" refers to an object where VBA code is stored. a "container" or "bucket" for data is a table. that's the only place that data is stored in Access. you assign atomic data points to fields in specific tables, and relate tables to each other as appropriate, according to relational design principles.

are you concerned about keeping the objects (tables, queries, forms, reports, macros, moduels) in a database grouped according to their use? there's no real "best practice" for that, that i've seen. most developers use prefixes to identify specific objects, commonly:

tbl for tables (tblCustomers, tblMouldList, for example)
qry for queries
frm for forms
rpt for reports
mod or bas for standard modules

but you can use what you like. the most important thing you can do (for yourself, and for other developers who may review your db, or come after you to maintain it) is to be consistent in whatever naming conventions you choose to use for all your db objects.

if you really feel this is a big concern, you could "group" objects by number. i have a database that pulls together a lot of relatively small processes into one application - some of these processes share some common data, and some are really stand-alone. so i can more easily find the objects that support one process, i number the objects as i build them, as

tbl01Employees
tbl01EmpContactInfo
qry01ActiveEmployees
frm01Employees
rpt01ActiveEmployees

tbl02Inspections
tbl02InspectionDetails
qry02CompletedInspections
frm02AddInspections
rpt02MonthlyInspectionResults

but again, if you choose to "group" objects by name, the most important point is that you be consistent in the naming conventions you use, throughout the database.

if all of the above is useless because that's not what you were getting at, then sorry - pls explain your question in more detail if possible.

hth
tina

Posted by: MadPiet Apr 10 2020, 06:24 PM

Tina's advice is spot on. "if all of the above is useless because that's not what you were getting at, then sorry - pls explain your question in more detail if possible."

Right now, we know the "things" in your world, but not how they're related. Since we don't know your business, we have no idea how these things are related to each other:
CUSTOMERS
INVENTORY
JOBNUMBERS
OLDJOBS
MOULDLIST

A jobNumber isn't a thing - it's an attribute of a Job. What does a job belong to, a customer?
What does MouldList have to do with anything? We need the "story" that ties these things together in order to model your scenario, Without sentences that describe this, that's impossible.

In school, one of my professors started on a whiteboard - He'd find a verb and then the subject and then the object and draw it... Like "A customer has Invoices", "Each Invoice details Items purchased"... and then he would connect the nouns through their related verbs, and come up with a Entity-Relationship Diagram. If you have that, then you are ready to start building a useful database. Until then, I doubt it.

Posted by: ntambomvu Apr 11 2020, 11:23 AM

tina and madpiet- thank you for your explanations and I see that I have a completely wrong concept as to the purpose of a module

I am currently using the naming conventions mentioned ( tbl ; frm ;rpt ;qry "etc etc )

Tina - I have the same problem that you mention I quote

"if you really feel this is a big concern, you could "group" objects by number. i have a database that pulls together a lot of relatively small processes into one application "

The concern I have is that the navigation panel is going to be so cluttered that it will be a major task to identify which entity matches which table

To further elaborate on the question- I will explain my concern and give as an example using only two of the categories that I mentioned.

Lets use just "customers" which is a table of customer names and "inventory" which is a table of items in my store.

Ancilliary tables that will link up logically with "customers" are tables of postal and physical addresses , telephone numbers, postal codes and a plethora of other constituents of information specific to the customer. Now there will also customer queries, customer forms and customer reports ( there are likely to be many of each of these as well )
The only common denominator is that they all are related to the customer table.

Then we get to the inventory. Each item has an item type,item category and our item part number, bin number, as well as original equipment manufacturer part number, original equipment supplier name, item selling price, item cost price, minimum stock level , quantity in stock , Item supplier name . As I manufacture many of the components myself I will
also have item drawing number , Item material . Now each item will also have a supplier with the nessessary tables applying to a supplier I.E table of names, telephone numbers ,addresses, supplier prices, usual delivery lead time.

In this case these entities are specific to the inventory.

There will be links between customer and inventory like "which customer is using which parts" ( the inventory table will probably have a field called "Customer" which will enable the
selection of a customer with all the customer details. ( there will be many other interactions as well)

My concern concern is that the navigation panel will become so cluttered up that it is going to be many pages long.

That is why I am looking for a method of seperating the categories into shall we say "sub- folders"

I thought that I could do this with modules but I see that the modules are there for a different purpose.

What other ways are there of bringing order to a multiplicity of interrelated entities?

By the way "jobnumbers" is our reference to orders and mouldlist are the moulds that are used for manufacture. so a jobnumber is created-
a supply source is identified and if it is in-house manufacture then the moulds needed are identified in the moldlist.

Your input is sincerely appreciated

regards fred


Posted by: Jeff B. Apr 11 2020, 12:16 PM

<fred>

I will echo the sentiment re: relational design. MS Access is a relational database. Its features/functions are designed to work best with well-normalized data. If you need to brush up on "normalization" and "relational database design", there are many resources here at UA.

If YOU are the only person who will ever use the database/application, using Access may not be an issue. If you expect "users" to be able to use the application, do you want to require them to learn Access before they can use the application to get THEIR jobs done?

"Easy is HARD!" Making any database/application easy to use (for non-techie-users) is work. The end-users really don't need (or want) to learn relational database design and operation. They just want to get their job done.

JOPO (just one person's opinion)

Posted by: MadPiet Apr 11 2020, 12:21 PM

Fred,

The list of things in your "universe" that you're interested in are:
CUSTOMERS
INVENTORY
JOBNUMBERS
OLDJOBS
MOULDLIST


Could you explain how they're related? (Usually it's a paragraph of <subject><verb>{one|many}<object> type sentences).

What kinds of "Jobs" are these? What's an OLDJOB? I figure you use items in Inventory to perform a job for a customer... but what does MOULDLIST describe? (What kind of company is it?) Think of this part as looking at the really big picture. For the moment, we just want to know how the entities/things in your universe are related. The gory details about fields and all that comes later.

Thanks,
Pieter

Posted by: tina t Apr 11 2020, 04:21 PM

QUOTE
Tina - I have the same problem that you mention I quote

"if you really feel this is a big concern, you could "group" objects by number. i have a database that pulls together a lot of relatively small processes into one application "

The concern I have is that the navigation panel is going to be so cluttered that it will be a major task to identify which entity matches which table

okay, well, i explained how i handle it in my database, by using a common number in the object prefixes to identify what "app" an object belongs to. one note is that i built my databases in Access97, more than a dozen years ago, though i've been adding on to them periodically ever since. (my employer was using A97; i had no say in the matter.) anyway, i believe A2016 has some kind of grouping option for objects in the database. if i had been using the newer .accdb files all along, i might well have used that option, and not needed to assign numbers to my "apps" in order to make it easier to find objects.

hth
tina

Posted by: ntambomvu Apr 12 2020, 01:50 AM

Tina - your last letter has hit the nail on the head

I think that I may have found what I am looking for.

I came across a sample database which is well known because it comes from Microsoft. It is called
NORTHWIND trading.
In this db they have grouped according to "grouping" which looks as if it is just what I am trying to do.
So now I will research how "groups" work and i will probably go this route.
Many thanks for all the input - It is not wasted because I have learnt a lot about programming that
will probably never be found in tutorials etc.
Forums are essential for learners like myself ( as a supplement to tutorials) because there is nothing to
beat person to person interaction
regards

fred

Posted by: tina t Apr 12 2020, 10:01 AM

you're welcome, fred, we're all happy to help. good luck with your project! :) tina

Posted by: ntambomvu Apr 14 2020, 06:33 AM

sorry to be a bother -- But why has this massage got an orange icon in "My discussions" ?
all the others are blue?

regards

fred

Posted by: tina t Apr 14 2020, 01:47 PM

i think it has to do with how many replies are counted for the topic (thread).

hth
tina