Results 1 to 7 of 7
  1. #1
    warlock916 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    5

    DB Design starting first model , with Matrix data from Excel

    Hello everybody,



    I'm willing to move a usefull file excel in to access in order to have report and everything else.

    As you can see the excel file is based on two sheets (figure 1.jpg and 2.jpg).

    The sheet2(foglio2) has all the main data, but the data on column "F" are calculated by this formula "=CERCA.VERT(D2;Foglio1!$A$2:$D$25;CONFRONTA(E2;Fo glio1!$A$2:$D$2;0);0)" (sorry is in italian excel) based on the values that are on sheet1 (foglio1). Like in a matrix system , if you have 2 values the file returns the 3°.

    I have create my tables and my relation figure 4.jpg but my first query already is ambiugous and doesn't run.

    So i think my normalization needs same help.

    Thanks in advice.

    Alessandro
    Attached Thumbnails Attached Thumbnails 4.jpg   2.jpg   1.jpg  

  2. #2
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    The query that is giving you trouble, what is supposed to do, what is it actually doing (any error messages, or pulling wrong data, etc.), and can you post the SQL of the query.

  3. #3
    warlock916 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    5
    Hello Nick, thanks for the reply.

    You are right I missed to specify.

    The query should create a table as is in excel.

    The Code:
    SELECT TblAbnormalities.IDAnomaly, TblAbnormalities.Site_FK, TblAbnormalities.DocumentID, TblAbnormalities.DateOpen, TblAbnormalities.Process_FK, TblAbnormalities.Norm_FK, TblDotNorm.NomePtoNorma, TblAbnormalities.DateofClose, TblAbnormalities.Status
    FROM TblSite INNER JOIN (((TblNorm RIGHT JOIN TblAbnormalities ON TblNorm.IDNorma = TblAbnormalities.Norm_FK) INNER JOIN TblProcess ON (TblProcess.IDProcesso = TblAbnormalities.Process_FK) AND (TblNorm.IDNorma = TblProcess.IDNorma_FK)) INNER JOIN (TblDotNorm INNER JOIN TblJoinProcessDotNorm ON TblDotNorm.IDPtoNorma = TblJoinProcessDotNorm.IDPtoNorma_FK) ON TblProcess.IDProcesso = TblJoinProcessDotNorm.IDProcesso_FK) ON TblSite.IDSiti = TblAbnormalities.Site_FK;

    but Access replies that there are ambigous inner join.

    Thank you.

  4. #4
    warlock916 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    5
    I have modify one relation as in picture 11.jpg and now the query works but the result is missing some entries (12.jpg) respect of the excel file.

    The Query code is :
    SELECT TblAbnormalities.Site_FK, TblAbnormalities.DocumentID, TblAbnormalities.DateOpen, TblAbnormalities.Process_FK, TblAbnormalities.Norm_FK, TblDotNorm.NomePtoNorma, TblAbnormalities.Status
    FROM TblSite INNER JOIN (TblDotNorm INNER JOIN (((TblNorm INNER JOIN TblProcess ON TblNorm.IDNorma = TblProcess.IDNorma_FK) INNER JOIN TblAbnormalities ON (TblProcess.IDProcesso = TblAbnormalities.Process_FK) AND (TblNorm.IDNorma = TblAbnormalities.Norm_FK)) INNER JOIN TblJoinProcessDotNorm ON TblProcess.IDProcesso = TblJoinProcessDotNorm.IDProcesso_FK) ON TblDotNorm.IDPtoNorma = TblJoinProcessDotNorm.IDPtoNorma_FK) ON TblSite.IDSiti = TblAbnormalities.Site_FK;

    Alex
    Attached Thumbnails Attached Thumbnails 12.jpg   11.jpg  

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    TblNorm should not link to both TblAbnormalities and TblProcess.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    I don't think you need a Norm_FK field in your tblAbnormalities if you relate tblAbnormalities -> tblProcess (by process ID). And tblProcess is related to tblNorm (by norm ID), then tblAbnormalities is related to tblNorm by transitivity. The sql looks fine to me (not an sql expert by any means), if you fix this structural error see if your problem persists.

  7. #7
    warlock916 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    5

    Solved

    Quote Originally Posted by nick404 View Post
    I don't think you need a Norm_FK field in your tblAbnormalities if you relate tblAbnormalities -> tblProcess (by process ID). And tblProcess is related to tblNorm (by norm ID), then tblAbnormalities is related to tblNorm by transitivity. The sql looks fine to me (not an sql expert by any means), if you fix this structural error see if your problem persists.
    Many Thanks Nic

    It works fine!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing excel into access data model
    By aisling21 in forum Access
    Replies: 2
    Last Post: 05-12-2015, 07:03 AM
  2. excel model that outgrew itself
    By canuck86 in forum Access
    Replies: 1
    Last Post: 04-10-2013, 11:54 AM
  3. Formatting form in matrix style Excel format
    By Harley Guy in forum Forms
    Replies: 6
    Last Post: 03-20-2013, 01:56 PM
  4. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 AM
  5. Create Report in a Matrix Format Like Excel
    By ortley77 in forum Reports
    Replies: 1
    Last Post: 08-24-2010, 09:56 AM

Posting Permissions

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