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
> Advice On Data Table Structure, Access 2016    
 
   
ntambomvu
post Feb 26 2020, 03:45 AM
Post#1



Posts: 16
Joined: 20-November 16




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


Go to the top of the page
 
nvogel
post Feb 26 2020, 08:11 AM
Post#2



Posts: 1,069
Joined: 26-January 14
From: London, UK


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.
Go to the top of the page
 
projecttoday
post Feb 26 2020, 11:25 AM
Post#3


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
Bullschmidt
post Mar 14 2020, 09:20 PM
Post#4



Posts: 29
Joined: 8-May 19
From: USA


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.

--------------------
J. Paul Schmidt - Freelance Web and Database Developer
Sample Database on the Web
Sample Access Database

Go to the top of the page
 
FrankRuperto
post Mar 14 2020, 09:33 PM
Post#5



Posts: 783
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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.
This post has been edited by FrankRuperto: Mar 14 2020, 09:41 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
tina t
post Mar 15 2020, 02:35 PM
Post#6



Posts: 6,435
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 10:01 AM