Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26

New Relational Database design

  1. #16
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,319
    Posts 5 and 8 are pretty much saying the same thing with the exception that I'm breaking out comments into their own table. After reviewing your spreadsheet, I have to say that either suggestion seems pretty much applicable.
    The Departments table will have a 1 to many relationship with the Issues table
    This statement indicates you're not looking at it the same way as we are (if I can speak for June7 also) but from my perspective, tblDepts is for looking up departments when assigning a department via a combo box. It's not for relating a dept to a complaint or anything else. That this the purpose of the junction table. Same can be said for defects (if they are pre-defined like departments are) and customers. If defects are not pre-defined because anybody can characterize any thing as a defect, then you have options such as allowing the combo to add to the list. However, that has the potential to cause minor variations, spelling the same thing wrong, etc. as if it were an additional defect type.

    As for the data format being fixed, you will have to ensure it gets added to your tables in the proper fashion. That will be a bit of a challenge, but for now, I'd suggest you leave that for when you have something working when sample data is manually input. I'm not sure I can provide more guidance than that because as previously noted, the sheet headers are not very helpful.



    Your situation is a bit more complicated than what I suppose a lot of tutorial information deals with, so don't feel bad if you're struggling a bit. Review the suggestions from 5 and 8 and see if something clicks with what you've learned. Best thing would be to map it out with pencil and paper and see if you can make the connections.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,303
    JonathanT
    Did you work through any of the tutorials I recommended in Post#13 in late July?

  3. #18
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    161
    Hi Jonathan

    You say that the original Excel data cannot be changed.

    So please confirm that you want to Import the Excel data into Access and then be able to extract the following information:-

    Departments with specific Issues

  4. #19
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    46
    I want to thank everyone for their help. Unfortunately I still do not have handle on how to store my data and Im up against a deadline. I will use Excel for now.

  5. #20
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,449
    I'm curious - does each department have specific defects?
    Or do ALL defects apply to ALL departments?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #21
    Join Date
    Apr 2017
    Posts
    987
    An example where Excel file is linked to Access database (I imported it because link wouldn't work with uploaded database here, but you have to link it). AS there was not explained for what which columns in Excel table were meant, I simply randomly dedicated them as client, visit or comment info.

    You have to create a stored procedure, which runs all those append queries to refresh Access tables with info from linked Excel table. NB! Only new info is imported!

    You add new data into Excel table linked into database whenever you get it. (NB! You work in Excel when doing this!)

    You run procedure which calls append queries automatically, or manually, it's your choice. After you import new data, you can clear data from linked Excel table (NB! You work in Excel when doing this!), but you don't have to do this.
    Attached Files Attached Files

  7. #22
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    46
    Each Dept has specific defects

  8. #23
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,449
    Do you have a list of Dept - defects you could/would post?
    Or maybe 3 Depts with 5 defects for each dept?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #24
    Join Date
    Apr 2017
    Posts
    987
    Quote Originally Posted by JonathanT View Post
    Each Dept has specific defects
    This is moot. Important is, does every defect belong to one specific department only? When yes, then you need a table of defects with a field for department ID, and you don't have to read department info from Excel file at all - defect info will do.

  10. #25
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    going back to the original requirement

    Comment example: "I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around."

    This single comment references 3 different departments. Accounting, Food&Beverage and Valet with 5 different defects. 3 for Food&Beverage and 1 each for Accounting and Valet. 1)wrong bill, 2)wrong order, 3)food quality, 4)server attitude, 5)vehicle damage.
    it would be useful to know which words or phrases identifies a department/reason (appreciate some are obvious), however the principle below would still stand.

    Similar to Microns suggestion in post #8, I believe you need 5 tables plus your comments table.5

    tblDepartments
    DeptPK autonumber
    DeptName text

    tblAssPhrases (i.e. associated phrases)
    aPhrasePK autonumber
    aPhrase text

    tblDefects
    DefectPK autonumber
    DefectDesc text

    tblLinkDefAP (i.e. link defects to associated phrases)
    DefAPPK autonumber
    DefectFK number (link to defects)
    aPhraseFK number (link to phrases)

    tblLinkDeptAP (i.e. link departments to associated phrases)
    DeptAPPK autonumber
    DeptFK number (link to departments)
    aPhraseFK number (link to phrases)

    tblComments
    CommentPK autonumber
    Comment longtext/memo


    Using the example above you create a query. (query1)

    SELECT tblComments.Comment, tblAssPhrases.*
    FROM tblComments, tblAssPhrases
    WHERE tblComments.Comment like"*" & tblAssPhrases.aPrhase & "*"
    so if your phrases are: (I've added a few extra to illustrate you will need to build this list carefully)
    aPrhase
    bill*wrong
    chicken*tough
    server*rude
    scratch*car
    car*scratched
    car
    vehicle
    bill*incorrect
    not*ordered
    cleaner*rude
    receptionist*rude
    rude
    waiter*rude
    waitress*rude
    manager*rude
    valet*rude

    you get this list
    Comment aPhrasePK aPrhase
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 1 bill*wrong
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 2 chicken*tough
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 3 server*rude
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 4 scratch*car
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 6 car
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 9 not*ordered
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 12 rude
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. 15 manager*rude

    You can then apply the linking tables to determine defect and department using this query (query2)

    Code:
    SELECT DISTINCT Query1.Comment, tblDepartments.DeptName, tblDefects.DefectDesc
    FROM tblDefects RIGHT JOIN (tblDepartments RIGHT JOIN ((Query1 LEFT JOIN tblLinkDeptAP ON Query1.aPhrasePK = tblLinkDeptAP.aPhraseFK) LEFT JOIN tblLinkDefAP ON Query1.aPhrasePK = tblLinkDefAP.aPhraseFK) ON tblDepartments.DeptPK = tblLinkDeptAP.DeptFK) ON tblDefects.DefectPK = tblLinkDefAP.DefectFK
    WHERE (((tblDepartments.DeptName) Is Not Null) AND ((tblDefects.DefectDesc) Is Not Null))
    Comment DeptName DefectDesc
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. Accounting wrong bill
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. Food & Beverage food quality
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. Food & Beverage employee attitude
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. Valet vehicle damage
    I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around. Food & Beverage wrong order

    you will get mismatches - for example 'server*rude' will pick up 'the server was very pleasant but the manager was rude'. So you may want to consider investigate using pattern matching which is a much more sophisticated way of finding matches. You will also presumably have to cater for guest typos, abbreviations and grammar. Your food descriptions will be complex as well. Ffor example - 'chicken*tough' - what about beef, fish, cod, etc what about inedible, tasteless, chewy, etc? Maybe drop the 'chicken' and use tough,inedible, tasteless, chewy as separate phrases.

    I attach the db for you to play around with

    Attached Files Attached Files

  11. #26
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,449
    Here is my version of what I think you described:
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. Relational Database
    By Seb1128 in forum Database Design
    Replies: 1
    Last Post: 04-01-2015, 12:13 PM
  3. Relational Database
    By javier571 in forum Database Design
    Replies: 1
    Last Post: 04-13-2014, 07:05 AM
  4. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 PM
  5. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums