Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 06:56 AM   #1
jjake
Newly Registered User
 
Join Date: Oct 2015
Location: Texas, USA
Posts: 242
Thanks: 60
Thanked 0 Times in 0 Posts
jjake is on a distinguished road
Project Database Design

hello,

I have an existing database that i currently use to store detailed information on hundreds of machines, instruments and other equipment. This is stored in [tblEquipment]

I am building a new project management database for installations of new equipment/machines in order to assist me in keeping track of the project from start to finish, this includes budgeting, purchasing and data entry for new equipment.

My Idea.

I have a main navigation form which uses several tabbed forms/subforms for my project. One particular form/table i use to keep track of equipment i need to purchase to install. This is a continuous form [frmProjectPurchases] that lists all the equipment. This goes into [tblProjectPurchases]

I would like to add another form [frmEquipmentDataEntry] that allows me to pull all the equipment i purchased from [frmProjectPurchases] but will allow me to input all the details on said equipment into [tblEquipment]. I was thinking of scrapping [tblprojectpurchases] and putting all this into [tblequipment] but alot of purchases will not actually have any details saved for them since they are redundant parts.

What would be the correct approach?

jjake is offline   Reply With Quote
Old 12-07-2018, 07:10 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Project Database Design

I recommend seperating user interface from table structure, especially in your mindset. Your explanation merges the two. You need to first conquer the data (tables), then move to data extraction (reports/queries), then finally work on the user interface (forms).

Start this by opening a blank database and building tables to accomodate the data you will put into it. Then, open the Relationship tool, bring in all your tables, set up your relationships, expand the tables so you can see all the fields in them, take a screenshot and post it here so we can help you validate the structure.
plog is online now   Reply With Quote
Old 12-07-2018, 07:36 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,616
Thanks: 58
Thanked 1,886 Times in 1,836 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Project Database Design

jjake,

It seems you are working with physical database and user interface options. As plog has suggested, you really have to get your tables designed to meet your business needs.
Here is a link to Database Planning and Design info - I recommend you work through a few of the tutorials from RogersAccessLibrary mentioned in the link.

Many find that starting with an overview description of the business --the 30,000 ft level - then gradually add detail is great for analysis and clarity. Build a model (pencil and paper). some test data and scenarios--revise until it meets your needs. You'll have a blueprint for building your database. Designing as you go ("on the fly") is rarely productive.

Good luck with your project.

__________________

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 12-07-2018, 07:47 AM   #4
jjake
Newly Registered User
 
Join Date: Oct 2015
Location: Texas, USA
Posts: 242
Thanks: 60
Thanked 0 Times in 0 Posts
jjake is on a distinguished road
Re: Project Database Design

Quote:
Originally Posted by plog View Post
I recommend seperating user interface from table structure, especially in your mindset. Your explanation merges the two. You need to first conquer the data (tables), then move to data extraction (reports/queries), then finally work on the user interface (forms).

Start this by opening a blank database and building tables to accomodate the data you will put into it. Then, open the Relationship tool, bring in all your tables, set up your relationships, expand the tables so you can see all the fields in them, take a screenshot and post it here so we can help you validate the structure.
This is a very basic layout from my main database. See attached.
Attached Images
File Type: jpg Relationships.JPG (55.7 KB, 19 views)
jjake is offline   Reply With Quote
Old 12-07-2018, 08:22 AM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Project Database Design

First, no need for tblYesNo. There's an actual Yes/No (True/False) field type in Access. Use that for those field types in tblProject"Purchase instead of tblYesNo.

Second. Give me 2 explanations. For the first, pretend its career day at a local elementary school and you are telling the kids what it is your orgainization does. No database jargon, explain any industry jargon and keep it simple.

Then give me a high level explanation of your database--light on the jargon. Tell me what data you have, where it goes and what the point of the whole thing is.
plog is online now   Reply With Quote
Old 12-07-2018, 08:43 AM   #6
jjake
Newly Registered User
 
Join Date: Oct 2015
Location: Texas, USA
Posts: 242
Thanks: 60
Thanked 0 Times in 0 Posts
jjake is on a distinguished road
Re: Project Database Design

1. My company manufactures goods for the oilfield. We use numerous machines throughout the process. We also install new machines ourselves to further our capabilities.

2. My database is used for numerous reasons,

Purchasing of new equipment [tblPurchasing
Details of owned equipment [tblequipment]
Preventive maintenance of equipment [tblPM]
Repairs/Work orders of equipment [tblWorkOrder]
New investments in order to further business growth [tblProject]
Details of investments [tblprojectdetails]

I am trying to create a section of my database that will allow me to keep track of each investment/project from start to finish involving

Budgeting
Scheduling
Purchasing
Database Tracking This would involve the section i inquire about. I want to be able to create a list of equipment i need to purchase, Keep track of what is purchased and received then make sure the equipment i have received has had the details of it input to the [tblequipment] table.

Too many times will we receive new equipment, install it in the field and the data entry gets put on the back burner. Next thing you know no one has the details for it so they have to go and fetch it off the machine in the field (sometimes in a very difficult spot to reach), or the preventive maintenance gets left undone because it never got put into the database schedule.

This section will ensure that the following steps are followed,

List of purchased equipment has been completed and received.
Equipment details have been entered
Equipment Preventive Maintenance items have been input to [tblPM]

Only when these details have been entered will the project truly be 100% complete.
jjake is offline   Reply With Quote
Old 12-07-2018, 09:32 AM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Project Database Design

I don't think you need tblProjectPurchases. Using the image you posted as reference, you should be able to just put tblEquipment in tblProjectPurchases place, add to it the fields from tblProjectPurchases.

plog is online now   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jjake (12-07-2018)
Old 12-07-2018, 10:08 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,616
Thanks: 58
Thanked 1,886 Times in 1,836 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Project Database Design

jjake,

Here are a few questions that may help in scoping your project and designing the database.

Code:
What equip do we have currently
What equip do we need to purchase 
What equip have we purchased
When was the equip  purchased
From whom was it purchased
When was equip received
When was equip installed
Who installed equip
Where is the equip installed
What maintenance has been done to equip
What is preventive maintenance schedule for equip
What equip is planned for removal,replacement,disposal..


Part of the not knowing what was installed or when or where could be because your business processes are not matched/part of your proposed database/data entry. For years there was paper work (orders,deliveries, installations, maintenance...) done by different groups in different locations and each area tended to be a silo unto itself. In order for info to be current and accurate, there must be checks and balances within the business process(es) and related database activities to ensure data is captured and stored when necessary. Database to support the business and business processes are not independent.

Look at the bigger business picture --model it with a diagram however conceptual -- this will help keep the major functions and their requirements in focus. If you don't know some details, simply add a black box to your model and include some key point of what's in the box. Do it early so you don't forget or overlook that requirement. Temper your development based on priorities of management. Vet the model with the planners, purchasers, installers maintainers.....

A database, such as what you describe in your posts, requires planning and design with input from all the players. The more you model and communicate and refine your analysis and involve others who will be expected to use the database, the smoother will be the development and uptake.

Good luck with your project.
__________________

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
The Following User Says Thank You to jdraw For This Useful Post:
jjake (12-07-2018)
Old 12-07-2018, 10:34 AM   #9
jjake
Newly Registered User
 
Join Date: Oct 2015
Location: Texas, USA
Posts: 242
Thanks: 60
Thanked 0 Times in 0 Posts
jjake is on a distinguished road
Re: Project Database Design

Quote:
Originally Posted by plog View Post
I don't think you need tblProjectPurchases. Using the image you posted as reference, you should be able to just put tblEquipment in tblProjectPurchases place, add to it the fields from tblProjectPurchases.
Thanks Plog, this is what i had initially thought of but i wanted a second opinion

Quote:
I was thinking of scrapping [tblprojectpurchases] and putting all this into [tblequipment] but alot of purchases will not actually have any details saved for them since they are redundant parts.
jjake is offline   Reply With Quote
Old 12-07-2018, 10:35 AM   #10
jjake
Newly Registered User
 
Join Date: Oct 2015
Location: Texas, USA
Posts: 242
Thanks: 60
Thanked 0 Times in 0 Posts
jjake is on a distinguished road
Re: Project Database Design

Quote:
Originally Posted by jdraw View Post
jjake,

Here are a few questions that may help in scoping your project and designing the database.

Code:
What equip do we have currently
What equip do we need to purchase 
What equip have we purchased
When was the equip  purchased
From whom was it purchased
When was equip received
When was equip installed
Who installed equip
Where is the equip installed
What maintenance has been done to equip
What is preventive maintenance schedule for equip
What equip is planned for removal,replacement,disposal..


Part of the not knowing what was installed or when or where could be because your business processes are not matched/part of your proposed database/data entry. For years there was paper work (orders,deliveries, installations, maintenance...) done by different groups in different locations and each area tended to be a silo unto itself. In order for info to be current and accurate, there must be checks and balances within the business process(es) and related database activities to ensure data is captured and stored when necessary. Database to support the business and business processes are not independent.

Look at the bigger business picture --model it with a diagram however conceptual -- this will help keep the major functions and their requirements in focus. If you don't know some details, simply add a black box to your model and include some key point of what's in the box. Do it early so you don't forget or overlook that requirement. Temper your development based on priorities of management. Vet the model with the planners, purchasers, installers maintainers.....

A database, such as what you describe in your posts, requires planning and design with input from all the players. The more you model and communicate and refine your analysis and involve others who will be expected to use the database, the smoother will be the development and uptake.

Good luck with your project.
Jdraw, thank you for your input.
jjake is offline   Reply With Quote
Old 12-07-2018, 03:38 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,210 Times in 1,110 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: Project Database Design

You are in a design phase. I'm going to pull out my old stand-bye advice along with explanations.

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

Translation: You are building a data/procedural model of your business. You need to have explored the business well enough to be able to specify the model's content. If you do not have a good description of every entity you want to track AND how the different entities interact/interrelate, you are going to have trouble.

Most of the time, I give advice of building a "project bible" that is your reference not only to how your business works but how your model will track/simulation the process. This is a document that will be where you go when you need to know how to implement something program-wise. I think of it as a road-map and the analogy works like this: How will you ever know where you are going without a map? How will you ever know that you have arrived if you don't have a map?

The trick is to identify each entity. Now, what do I mean by entity? You talk about specific bits of equipment. You were talking about tblEquipment. Your equipment would be one entity.

You have a project. You want to associate a project with a piece of equipment purchased for that project (according to your questions about your diagram). The project would be an entity. Not quite as hard/physical as a piece of equipment, but easy enough to see.

Sometimes you make an association between two entities, which I think MIGHT apply in your case. If you allow the equipment to stay with the customer whose project paid for it, that is one model. If you keep the equipment and can reassign it to another project when the first one is finished, that is a different model. But in EITHER case, the assignment/association of equipment to a project is a logical entity.

When laying out things like purchase orders, vendors, shipping manifests, etc., each of those can potentially be an entity. When you can identify all of those entities and how they relate to your business, you are well on the path to solving your problems.

At the point where you can identify entities and how they relate and how you process each one at each step along the way (even if only in broad-brush terms), you have a design document worthy of consideration.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Translation: As part of #1, you probably defined forms and/or reports you wanted to see in the finished product. (Part of the "how will you know when you have reached your destination question.) For each displayed or printed item, for each result that you store, for each value you want to develop, you need to verify that you are in fact capturing the data needed so that Access CAN tell you what you want to know.

If item X is elementary, verify that you have an input of X. If item X is computed, verify that you have a data source that feeds the computation. If this sounds to you like you would sometimes be working backwards to be able to answer the question, you WERE paying attention and you got it right.

You must remember that Access is like a power tool for the mind. You are the architect. Access is not the designer. All it knows is how to make tables, queries, forms, and reports. You are the subject matter expert. That is why you must apply the rules to verify that what you are building WILL do what you want it to.

Do not despair. It seems like building that project bible will be a pain in the toches - and you know what? You are right! But once you get the hang of it, it starts moving along very nicely.

One MS Office tool that might be helpful is Word. It has an OUTLINE mode in which you can build a formal multi-layered outline to drill down on what you design. You can make it show you everything on a given layer. You can print the outline at will. And yet it is trivial to edit.

__________________
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
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jjake (12-10-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
!HELP! Database Design Final Project! Tables, Queries..Very Important! angelikamaj Theory and practice of database design 6 05-01-2014 02:14 PM
Project management database design help Sketchin Theory and practice of database design 7 01-21-2014 01:58 PM
New Database Project - Advice with Design hullstorage Theory and practice of database design 0 06-08-2010 07:21 AM
Question Please help! Project Design Question ViperDriver General 9 06-04-2009 05:09 AM
Question how should i design this project dougvcd General 2 07-03-2008 02:34 AM




All times are GMT -8. The time now is 06:25 AM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World