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
> Datadase Design For Written Comments, Access 2016    
 
   
jthomas
post Sep 24 2019, 06:54 AM
Post#1



Posts: 10
Joined: 16-June 16



This is a long text and I apologize in advance. I’m struggling with how to design a database to fit my needs. It’s a database where we analyze negative written comments about our guests’ experience and indicate what the specific issues are and what department the issues are related to.
The original data is in Excel and has location, source, customer information as well as one comment field. This data can’t be changed, how ever I can add columns/fields if necessary to make the Access design work. The customer information is not relevant and will not be used in any way. Our focus in on the written text. I have to read each comment and decide what departments it references (don’t know if that is the right word) and what specific issues are mentioned.

No department or issue will be repeated in a single record.
Each issue is related to only 1 department.
Each department can be related to many issues

Each record can reference up to 4 different departments from a list of 8 possible departments. Each record can also reference up to 4 different issues from a list of 152 possible issues.
I think I need three tables. One for the original data (tblComments), another for the 8 departments (tblDepartment) and one more for the 152 issues (tblIssues).
Two problems I can’t solve.

1) How do I store the department and issue information for each record when the number for each varies from record to record?
2) There is no common field between the tblComments and the other 2 tables. There is a one to many relationship between the tblDepartment and the tblIssues. How can I create a relationship between the tblComments and the other 2 tables?
Go to the top of the page
 
arnelgp
post Sep 24 2019, 07:07 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


use tblComments to store all comments of each dept.

fields:
id (autonumber)
deptID (long) FK to tblDepartment
issueID (long) FK to tblIssues
Comment (short/long text)

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 24 2019, 08:56 AM
Post#3


UA Admin
Posts: 36,182
Joined: 20-June 02
From: Newcastle, WA


Lots of questions arise.

How do you collect the data in the first place? On a piece of paper or postcard? Via email or an online feedback form? Or?

Controlling the data input at that point probably will help you when it comes time to sort it out into tables.

Do you require guests specify which category or categories they want to comment on? If so, how does that work?
Do you require guests specify which department or departments they want to comment on? If so, how does that work?
Do you require guests specify the issue or issues they want to comment on? If so, how does that work?

You have three tables for Lookup values: Departments, Issues and Categories.

It looks like each comment can have multiple departments assigned to it, multiple issues assigned to it, or multiple categories assigned to it. And that means a Comment Table for the comment itself, and a CommentDetail table for those multiple attributes.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jthomas
post Sep 24 2019, 10:32 PM
Post#4



Posts: 10
Joined: 16-June 16



arnelgp--Unless I misunderstand your post that suggestion won't work as I need to reference multiple departments and multiple issues for each record. Any given record can have from 1-5 departments and from 1-4 issues. I pick the issues from a list of 152 possible. I think it's impossible to store all that data in one table.

GroverParkGeorge--I have no control over the original data. It comes as an excel spreadsheet which I will import/append to an Access table. It contains fields for customer information, location information and the all important comment text field. I will remove the customer information before importing. Based on what is written in the comment field, I decide what departments and issues need to be referenced for each record. You are correct that each comment can have multiple department and issues assigned. There are no categories. Where I'm lost is how to store the data for each record. I've attached a sample spreadsheet of the original data so you can see my starting point.

In the sample spreadsheet:
Line 2) This would be 2 Departments and 2 Issues. Ownership-No Dresser, Engineering--No hot water
Line 4) This would be 2 Departments and 4 issues. Engineering--No hot water, non-working remote and loud AC. Front Office--Staff didn't care

I need to know how to design my tables to store the data for Line 4.
Attached File(s)
Attached File  Sample.zip ( 25.14K )Number of downloads: 11
 
Go to the top of the page
 
arnelgp
post Sep 24 2019, 11:06 PM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you are not misundertooding me, youre getting my point.
you will record your issues one records per dept per issue.
CODE
recno    issueID      deptID
1          1               1
2          2               1
3          3               1
4          4               1

this is the "normal" table.

below is not "normal" table:
CODE
recno       deptID       issueID1          issueID2         issueID3
1             1               1                      2                      3

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
orange999
post Sep 25 2019, 08:10 AM
Post#6



Posts: 1,997
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


jthomas,

Have you "mocked up" some samples of your desired output? If you take a few examples of the comments, manipulate them and put these into your proposed final format as a test, you will identify trends or patterns (possibly) and you will get some ideas for the logic involved in the manipulation.
I would suggest doing this mock up before finalizing any design decisions on tables.

Some thoughts on your "business facts"
A rawComment contains 1 or many detailComments
A detailComment involves to 1 or many Departments
A detailComment relates to 1 or many Issues
An Issue can be associated with a Category/Broad Subject Area.

How do you plan on reviewing guest comments(raw) into more specific assignments for analysis? (as GPG has asked).
There seems to be a degree of subjectivity involved is such assignments, but that may be a very minor aspect of your review.
This post has been edited by orange999: Sep 25 2019, 08:11 AM

--------------------
Good luck with your project!
Go to the top of the page
 
Jeff B.
post Sep 25 2019, 09:00 AM
Post#7


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


"... it comes as an Excel spreadsheet which I will import/append to an Access table..."

Access is not Excel on steroids. Whatever you might be getting via Excel, you are not limited to only using that data 'as is'. In fact, Excel data is often not particularly well-normalized, but to get good use of Access' features and functions, you will want to feed it well-normalized data.

One option for Excel data is to import 'as is', then run a series of normalizing queries that load the appropriate pieces of data into their respective (well-normalized) Access tables.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
orange999
post Sep 25 2019, 05:19 PM
Post#8



Posts: 1,997
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Seems this post is related or the same issue?
This post has been edited by orange999: Sep 25 2019, 05:20 PM

--------------------
Good luck with your project!
Go to the top of the page
 
JonathanT
post Sep 26 2019, 04:35 PM
Post#9



Posts: 14
Joined: 30-October 11



arnelgp--I see that the sample table with Issue1, Issue2, etc is not the way to go. However in the sample table of normal code it looks like 1 department and 1 issues per record. Is that correct? If so that is not what I need. 1 record can relate to multiple departments and multiple issues

orange 999--Regarding your "thoughts on business facts" This would be true. A rawComment is related 1 or more departments and a rawComment is related to 1 or more Issues. Each Issue is only related to 1 department. There are no categories. I have not mocked up anything as I'm still unclear on how to store multiple departments and issues for each record.

orange 999--regarding "this post". That is me in another forum. Trying all options to get help.

Jeff B--I feel pretty confident I can normalize the data in Excel before importing. I still draw a blank on how to store the multiple departments and multiple issues per record in Access.
Go to the top of the page
 
orange999
post Sep 26 2019, 05:10 PM
Post#10



Posts: 1,997
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Here's a draft model based on posts. It may be helpful. Feel free to ignore, alter, use, adapt.. as you see fit.
Attached File(s)
Attached File  GuestComments_draftmodel.PNG ( 31.33K )Number of downloads: 11
 

--------------------
Good luck with your project!
Go to the top of the page
 
tina t
post Sep 26 2019, 08:57 PM
Post#11



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


QUOTE
I have to read each comment and decide what departments it references (don’t know if that is the right word) and what specific issues are mentioned

sorry if i'm being thick, but it's not clear to me: are you looking for a way to automate parsing of the data to determine issues and their departments?

or are you intending to continue to read each comment and make subjective decisions on what the issues and their departments are? and what you want help with is storing the resulting data relationally for aggregate analysis?

in the meantime, i'm going to assume you (or somebody(s)) will read each comment and choose the issues you want to record. below is a quote from one of your posts in the other forum, which was already referenced by orange999, which was clearer - to me - than anything else:

QUOTE
We concentrate on the comment field as it explains in detail the customer experience.

Based on the text in the comment field we need to reference(I hope that's the right word) for each record the department(s) mentioned and the specific issue(s) mentioned. A single record needs to be able to reference from 1 to 5 different departments. That same record needs to reference from 1 to 4 issues from a list of 150. If I understand what I've been reading I need 3 tables. One with the Comment information, one for the Departments and 1 for the Issues.

The Departments table will have a 1 to many relationship with the Issues table.

okay, you're almost there, but not quite. you've already defined a relationship between departments and issues, which is fine: each department may have many issues, AND each issue belongs to only one department. as you said, classic one-to-many. in doing an analysis, that relationship will help pinpoint what departments have a hand in the poor guest experience.

but the focus of the data is on comment issues. one Comment record many refer to many issues, and each issue many be referred to in many Comment records. a many-to-many relationship. so you need a join (also called a linking) table. here's an example of tables as defined so far:

tblDepartments
deptID (primary key)
deptName

tblIssues
issueID (primary key)
issueName
deptID (foreign key from tblDepartments)

tblComments
commentID (primary key, Autonumber)
commentText
<whether or not you include guest and/or location data fields as the source of each comment record is your choice.>

tblCommentIssues
comissID (primary key, Autonumber)
commentID (foreign key from tblComments)
issueID (foreign key from tblIssues)

so each Comment record contains an instance of a guest's comment about his/her/their stay. from what i've read explaining your source data, sounds like you can do a direct data dump from Excel into tblComments.

then in the related child table tblCommentIssues, you enter one record for each issue you want to "assign" to the parent Comment record. if your business rules say no more than 4 issues assigned per comment, so be it. from a relational design standpoint, you can assign every single defined issue to a single Comment record - or none - or anywhere in between.

notice that departments are not involved directly in tblCommentIssues. there is no need, because departments are indirectly related, through the issue records. you identify the issues stated in each comment, and add a record for each one in tblCommentIssues. when you query out the tables, that is where you'll use the relationship between tblDepartments and tblIssues to slice 'n dice the data to get the information you need.

from a data entry standpoint, i'd create a mainform bound to tblComments. then a second form bound to tblCommentIssues; in this form, i'd create a combobox control bound to field issueID (the foreign key field), with its' RowSource as tblIssues. then i'd add a subform control in the mainform, and assign the second form as its' SourceObject.

the above is all standard mainform/subform design to support a relationship between a parent table (tblComments) and its' child table (tblCommentIssues) that serves as the linking table with another parent table (tblIssues).

hth
tina
This post has been edited by tina t: Sep 26 2019, 09:38 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jthomas
post Oct 4 2019, 09:32 PM
Post#12



Posts: 10
Joined: 16-June 16



Thanks tina t and orange999. I will review and get back to you
Go to the top of the page
 
dmhzx
post Oct 5 2019, 02:11 AM
Post#13



Posts: 7,115
Joined: 22-December 10
From: England


Apologies if someone else had mentioned this.
You say that there is a limit to any 4 from 8 somewhere.
I suspect that this is there simply because of Excel.

The designs offered will allow you to go up to 8 of 8 if you want, If there really is a maximum of four, then don't forget to stop at four.

I had a similar experience recently in upgrading a spreadsheet where it was trying to count the number of time people had failed to get something right. Excel was set to 3, and once I had moved it to Access we could see that some areas were failing a lot more than three times. - It also became a lot easier to produce right first time reports.
Go to the top of the page
 
JonathanT
post Dec 1 2019, 10:11 AM
Post#14



Posts: 14
Joined: 30-October 11



Tina,
Thanks for the info, Here is what my tables look like.

tbl Departments
ID Autonumber Primary
Department Short text
Description Short text

tbl Issues
ID Autonumber Primary
IssueCode Short text
Description Short text
Department Short text

tbl Comments
ID Autonumber Primary
Confirmation Short text
Comments Long text

If I understand you I need to create a linked table. You are suggesting I create a linked table that would look like this. Correct?
tbl CommentIssues
ID Autonumber
CommentID Autonumber
IssueID Autonumber

I would need to change the ID fields in my current tables to read deptID, issueID and commentID correct?
What I still don’t understand is how to store/reference/input what I glean from the Comments field of the Comments table. I don’t see a field in the CommentIssues table to enter data??
Go to the top of the page
 
tina t
post Dec 1 2019, 09:03 PM
Post#15



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


QUOTE
I would need to change the ID fields in my current tables to read deptID, issueID and commentID correct?

i would, yes. multiple tables with identical fieldnames is just a nightmare to try to work with.

QUOTE
What I still don’t understand is how to store/reference/input what I glean from the Comments field of the Comments table. I don’t see a field in the CommentIssues table to enter data??

well, my understanding of your goal was to identify specific defined issues from written comments (unstructured text). the setup i described will allow a user to do that, in a normalized structure, with infinite expandability. if you read a comment and decide that the issues are B, C, F, and L, from a list of defined issues, then that's what you enter in the linking table - one record for each issue. if you feel that you need a field for more unstructured text, then just add one.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
JonathanT
post Dec 4 2019, 09:04 AM
Post#16



Posts: 14
Joined: 30-October 11



Using the example in this thread, in the linked table I need another field in the tblCommenIssues table for the specific issues, correct?
Go to the top of the page
 
tina t
post Dec 4 2019, 03:25 PM
Post#17



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


QUOTE
Using the example in this thread, in the linked table I need another field in the tblCommenIssues table for the specific issues, correct?

no, not if the issues you want to identify and track are defined.

QUOTE
tblCommentIssues
comissID (primary key, Autonumber)
commentID (foreign key from tblComments)
issueID (foreign key from tblIssues)

so each record in tblComments contains an instance of a guest's comment about his/her/their stay. from what i've read explaining your source data, sounds like you can do a direct data dump from Excel into tblComments.

then in the related child table tblCommentIssues, you enter one record for each issue you want to "assign" to the parent Comment record. if your business rules say no more than 4 issues assigned per comment, so be it. from a relational design standpoint, you can assign every single defined issue to a single Comment record - or none - or anywhere in between.

so field issueID in tblCommentIssues records each specific issue related to a specific comment record. why do you think you need another field besides field issueID?

hth
tina
This post has been edited by tina t: Dec 4 2019, 03:26 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
JonathanT
post Dec 4 2019, 05:22 PM
Post#18



Posts: 14
Joined: 30-October 11



I had it confused with the ID field in the tblIssues.
Go to the top of the page
 
tina t
post Dec 4 2019, 05:40 PM
Post#19



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


yes, easy enough to do. my personal naming convention does not allow any duplicate fieldnames in a single database, for that reason. if i were building this one, my tables would read as

tblDepartments
dID (primary key)
dName

tblIssues
issID (primary key)
issName
iss_dID (foreign key from tblDepartments)

tblComments
cID (primary key, Autonumber)
cText

tblCommentIssues
cisID (primary key, Autonumber)
cis_cID (foreign key from tblComments)
cis_issID (foreign key from tblIssues)

but i don't usually use my own naming convention in posts. :)

hth
tina
This post has been edited by tina t: Dec 4 2019, 05:42 PM

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


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 10:05 AM