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
> Table Structure For Ease Of Updating, Access 2016    
 
   
Spikenaylor
post Apr 14 2017, 03:47 AM
Post#1



Posts: 63
Joined: 8-April 11



My database is for the end users to enter issues found on equipment in the process they are working on.

I have created the database draft but the entering of process Equipment Issues data into the database so the end users have a defined list to slect from is not efficent and leads to missing data for the smae equipment in multiple processes.

Could anyone help and point me in the right direction on how to structure my tables ready for the end users cascading combox selections

The way I was thinking is this:

tbl_Process (containing the all the Processes)
tbl_Equipment (Containing the list of all Equipment across all Processes)

tbl_Equipment 1 (containing all the defined issues for Equipment 1)
tbl_Equipment 2 (Containing all the defined issues for Equipment 2)
tbl_Equipment 3 (containing all the defined issues for Equipment 3)
etc


I am struggling to structure it for a manager to be able to manage the list of all the issues for each equipment for each process. enabling the end user to select:
What process it is
What Equipment from Process Selected the issue is with
What Issue from the defined list is causing the problem
Text field to describe the what the end user saw as being wrong.
(if it is not in the list we have listed Other- at the moment, we don't want the end user adding in issues to the list, managers will review the other entries and will add into the defined lists as required.)


What I need is probably a junction table to be created but I don't know how to link the separate Equipment tables into it.

for example

Process 1 can have Equipment 1 and Equipment 2 being used with all their defined issues
Process 2 can have Equipment 1 and Equipment 3 being used with all their defined issues
Process 3 can have Equipment 2 and Equipment 3 being used with all their defined issues

etc

any help would be gratefully received.

Many thanks for looking
This post has been edited by Spikenaylor: Apr 14 2017, 03:49 AM
Go to the top of the page
 
cheekybuddha
post Apr 14 2017, 04:44 AM
Post#2


UtterAccess VIP
Posts: 8,845
Joined: 6-December 03
From: Telegraph Hill


I'm not sure exactly how your workflow is set up, but I am 99.99% confident in saying that:

tbl_Equipment 1 (containing all the defined issues for Equipment 1)
tbl_Equipment 2 (Containing all the defined issues for Equipment 2)
tbl_Equipment 3 (containing all the defined issues for Equipment 3)

is NOT the way to go!

Equipment is equipment. Put it in an equipment table.

I don't know what Equipment 1, 2, 3 means, but this sounds like it should be a foreign key lookup in the equipment table if the equipment item can only belong to one of the groups, or part of a junction table if the relationship is many-to-many.

You mention you want to >> how to structure my tables ready for the end users cascading combox selections <<

Structure your database against its entities. Worry about cascading comboboxes later. (Tip: if you structure the db correctly first, the cascading combos will likely fall in to place anyway.)

If you need further help, please post a detailed description of the whole process and entities involved and I or others will help you get the structure down.

Remember, as a useful rule of thumb, structure first, then interface.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Spikenaylor
post Apr 14 2017, 05:00 AM
Post#3



Posts: 63
Joined: 8-April 11



that is what i am trying do, structure the DB first

so tbl_Process contains

Process 1
Process 2
Process 3
Process 4

tbl_equipment contains

Equipment 1
Equipment 2
Equipment 3
Equipment 4

tbl_Equipment 1 is the table containing all the defined issues for Equipment 1 identified in tl_Equipment, this contains

issue 1
issue 2
issue 3
issue 4

tbl_Equipment 2 is the table containing all the defined issues for Equipment 2 identified in tl_Equipment, these issues are different than the ones in tbl_Equipment 1.
this tbl_Equipment 2 contains

issue 1
issue 2
issue 3
issue 4

Now all I need to do is create a for example junction table containing (my thinking, but not necessarily the correct way)

tbl_junction

Process 1 - Equipment 1 - issue 1
Process 1 - Equipment 1 - issue 2
Process 1 - Equipment 1 - issue 3
Process 1 - Equipment 1 - issue 4
Process 1 - Equipment 2 - issue 1
Process 1 - Equipment 2 - issue 2
Process 1 - Equipment 2 - issue 3
Process 1 - Equipment 2 - issue 4
Process 2 - Equipment 1 - issue 1
Process 2 - Equipment 1 - issue 2
Process 2 - Equipment 1 - issue 3
Process 2 - Equipment 1 - issue 4
Process 2 - Equipment 2 - issue 1
Process 2 - Equipment 2 - issue 2
Process 2 - Equipment 2 - issue 3
Process 2 - Equipment 2 - issue 4
Process 3 - Equipment 3 - issue 1
Process 3 - Equipment 3 - issue 2
Process 3 - Equipment 3 - issue 3
Process 3 - Equipment 3 - issue 4
Process 3 - Equipment 1 - issue 1
Process 3 - Equipment 1 - issue 2
Process 3 - Equipment 1 - issue 3
Process 3 - Equipment 1 - issue 4

This is just my thoughts, If this is not the way to set it up then what is please
Going with the way I have described, I am not sure how to on a form to be able to use the separate tbl_Equipment to be able to create my junction table.



Go to the top of the page
 
cheekybuddha
post Apr 14 2017, 05:34 AM
Post#4


UtterAccess VIP
Posts: 8,845
Joined: 6-December 03
From: Telegraph Hill


Wouldn't you rather have an issues table, with a foreign key to the equipment it refers to?

From your description I imagine tables like:

tbl_process
ProcessID
ProcessName
ProcessDescription
etc ...

tbl_equipment
EquipmentID
EquipmentName
EquipmentDescription
etc ...

lnk_process_equipment (junction table m:m)
ID (useful but not necesssary
ProcessFK (linked to tbl_process.ProcessID)
EquipmentFK (linked to tbl_equipment.EquipmentID)

tbl_issues
IssueID
EquipmentFK (linked to tbl_equipment.EquipmentID - 1:m)
IssueName
IssueDescription
etc ...

All issues are in one table. The equipment it refers to is stored in the EQuipmentFK foreign key field.

If an issue can apply to more than one piece of equipment then you would need another junction table for the m:m relationship, and remove the EquipmentFK from this table.

If this seems sensible then we can work out the queries to feed your cascading combos.

--------------------


Regards,

David Marten
Go to the top of the page
 
ScottGem
post Apr 14 2017, 06:38 AM
Post#5


UtterAccess VIP / UA Clown
Posts: 31,968
Joined: 21-January 04
From: LI, NY


QUOTE
Table Structure For Ease Of Updating


Totally and completely the WRONG thought process. Access is a relational database. Tables should be designed according to the relational model. That model has rules about table design. And having separate tables for the issues of each piece of equipment is NOT within the rules. You design a user interface to suit the users and the table design. you do NOT design tables based on a perceived user interface.

Yes you have a table for Processes and a table for Equipment and a table for Issues. Based on your requirements,
QUOTE
enabling the end user to select:
What process it is
What Equipment from Process Selected the issue is with
What Issue from the defined list is causing the problem
Text field to describe the what the end user saw as being wrong.

the issues table should look like this:
tblIssues
IssueID (PK Autonumber)
ProcessID (FK)
EquipmentID (FK)
Comments

You might also want an EmployeeID FK to indicate who was entering the record and a timestamp of when the record was entered.

The user selects ProcessID from a combobox. This may be able to filter the equipment list so it only shows Equipment used for the selected process (it was unclear whether that's needed). Then select the Equipment and add their notes. The EmployeeID and timestamp can be entered automatically.


--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post Apr 14 2017, 09:47 AM
Post#6


UA Admin
Posts: 29,109
Joined: 20-June 02
From: Newcastle, WA


PMFJI.

Although the fact is that Access is a relational database tool, and working with Access is always much easier if you learn and apply the proper principles of table design, the rules of Normalization are not exactly what we might normally think of as "rules", as in "you must do this, or the Access polices will arrest you". Rather, you can, and should, learn and follow those rules because they result in the best possible design to protect the integrity of your data.

"Alternate" designs, i.e. a "spreadsheet" style table like you describe can be forced to do the job, but only at the cost of much more work, and more risk to your data. In that sense, then, you can choose any interface design you want, but do so knowing it comes at a higher price.

Both Scott and David have offered solid suggestions about how to correct the initial design flaws in these "spreadsheet" style tables. I urge you to step back from concerns over the interface until you figure out the data model you need.

We also have a good set of starter articles. Study them before going much further. Then redesign your tables, and only then come back to looking for an interface design for data entry.
This post has been edited by GroverParkGeorge: Apr 14 2017, 09:48 AM

--------------------
Go to the top of the page
 
Spikenaylor
post Apr 14 2017, 12:14 PM
Post#7



Posts: 63
Joined: 8-April 11



Thanks very much for all the help
I am aware of the database relationships and structure, but this setup was getting me stumped, the answers have prompted my brain to get in order, probably overthinking, I always seem to struggle with the initial table structure and design, this database sort of started the opposite way round, I was given a spreadsheet and was given a brief of how the end user form was to look, End user only are allowed through comboboxes to choose Process, Equipment and Issue for selected equipment along with other information fields, which is easy enough once the data is in the database, my strugggle is setting up the tables in such a way without any data in so Manager or other designated user can enter/edit process, Equipment and Issues for each equipment so that data is available for the end users to select from.



Regards
This post has been edited by Spikenaylor: Apr 14 2017, 12:21 PM
Go to the top of the page
 
ScottGem
post Apr 14 2017, 12:30 PM
Post#8


UtterAccess VIP / UA Clown
Posts: 31,968
Joined: 21-January 04
From: LI, NY


You need to have some dummy data to test with. You can always delete the dummy data and run a Compact and Repair before turning it over to the users.

Its not unusual for users to present a form or report and say make me a database to do this.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
Spikenaylor
post Apr 18 2017, 03:31 AM
Post#9



Posts: 63
Joined: 8-April 11



Thanks for the help

I set the tables up as per CheekyBuddha

tbl_process
ProcessID
ProcessName


tbl_equipment
EquipmentID
EquipmentName


lnk_process_equipment (junction table m:m)
ID (useful but not necesssary
ProcessFK (linked to tbl_process.ProcessID)
EquipmentFK (linked to tbl_equipment.EquipmentID)

tbl_issues
IssueID
IssueName

lnk_equipment_issue (junction table m:m)
ID (useful but not necesssary
IssueFK (linked to tbl_issues.IssueID)
EquipmentFK (linked to tbl_equipment.EquipmentID)


All works fine and end user queries for cascading comboboxes working correctly

My next question is how would you guys control the adding of new issues.

Designated user / Team leader is responsible for populating tbl_equipment and tbl_issues for initial set of data. they then assign issues to all the equipment. This is all done so we have an initial set of data for the end users to select from on go live day.


When the End User is populating a Reported Issue Form (source: tbl_reported_issue) and they want to report an issue that is not in the list of issues for that particular equipment, how would you guys control this?

our initial thought is to allow the end user to select other from the issue field and either fill in a revealed other field or populate the comment field with the new issue. Team leaders will then review this Other data and enter the issue for future reporting.

Is there an other way to allow the end user to update the issues and links whilst populating Reported Issue Form.




Go to the top of the page
 
ScottGem
post Apr 18 2017, 05:07 AM
Post#10


UtterAccess VIP / UA Clown
Posts: 31,968
Joined: 21-January 04
From: LI, NY


Research the NotInList event

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
gemmathehusky
post Apr 21 2017, 07:03 AM
Post#11


UtterAccess VIP
Posts: 4,395
Joined: 5-June 07
From: UK


just another observation.

data presentation and data storage are different things. The idea is to store the data in a relational manner, for ease of data management, updating and so on and so forth.
Data presentation is a different thing.

If you want to present the problems in a grid form, so that you can see something like this

Item No problem1 problem2 problem3 problem4 etc
1 YNNN
2 NYYN
3 NNNN
4 NYNN


etc,

Then it is relatively easy to go from a normalised structure to a grid presentation. But you do not want to store the data in this way, as it gives you a lot of practical issues. With a normalised structure you can add new problems easily. You can select just the items displaying individual problems easily. If the data is STORED in a grid, it's much harder and less flexible.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th April 2017 - 01:02 AM