Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Problem with create relationships in my new model

    Hi Guys,

    I have 2 tables:

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	35 
Size:	12.7 KB 
ID:	29521

    In tbl_Braki there is ID as Primary Key and autonumber.
    Problem is that field "Nr_zgłoszenia" can be duplicated like here:

    Click image for larger version. 

Name:	Bez tytu?u2.png 
Views:	35 
Size:	3.0 KB 
ID:	29522

    Now i would like to create relationships between tbl_Braki and Przypominacze table.

    So as you can see it is done like one-to-many relationships.



    Now for each ID there is an treeview (node) which can be expand.
    I am thinking it is possible to do this small plus icon next to Nr_zgłoszenia field?

    for example for ID 1,2,3,4 and 5 it is only one employee attached with number 1. And for this employee i would like to add the same date of sending reminder.

    So, ID 1 and Nr_zgłoszenia 1 date of sending will be 01-01-2017
    ID 2, 1, 01-01-2017
    ID 3, 1, 01-01-2017
    ID 4, 1, 01-01-2017
    ID 5, 1, 01-01-2017

    I do not want to write the date 5 times, only one time and attach it to my Nr_złoszenia field.

    How can i relate this?

    Thank you for help,
    Jacek Antek

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Then you need a table where Nr_zgłoszenia is unique and save date in that table. Perhaps tbl_Braki should be split.
    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.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Okey.... thank you June7 !

    uff i have to rebuild my model once again...:/

    Purpose for this is for sending reminders.
    I have the table:

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	32 
Size:	13.6 KB 
ID:	29551

    and i will have to loop through tbl_Braki and all exactly matching rows put into e-mail in order to create report.

    Okey...
    Problem is that Nr_złoszenia is not unique because each client can have a lot of documents.

    ----->

    After earlier our topics with creating relational database model i have done few corrects to my model and i ask you about support with this Guys.

    from tbl_Braki i created 5 tables:
    1. tbl_slow_spolki - table with characteristics for all clients - this table is uptaded automatically once a day using QlikView.
    Zleceniodawca_nr field is a unique key as PK

    2. tbl_documents - my summary table where all information about documents will be saved:
    Identyfikator - PK - is related to tbl_Reminders where you have data for each document. I do not know if is good approach.
    So if user send reminder e-mail for each document with "Data_Wysłania" = '2017-06-06' i should have a list which i can report for each document.
    In tbl_braki it seems to me very simple but know i don't know...

    Nr_złoszenia - field which in tbl_Braki was not unique. When each employee can have multiple documents and the same Nr_zgłoszenia field:
    employee 1; document 1, Nr_zgłoszenia 1
    employee 1, document 2 , Nr_zgłoszenia 1
    emploee 1, document 3, Nr_zgłoszenia 1

    Hmm now i am not sure if it is neccessary here.

    3. tbl_slow_Documenty - Here it is document name and filed Obowiązkowy (is neccessary) with yes/no option

    4. tbl_Employees - all employess form SAP system with their names and Podobszar (text field), it can be write multiple time.

    5. tbl_Remindes - all dates when reminders were sent for each document - the most difficult part.

    Question1:
    1. How can i build the form which will be similar to form built on my tbl_Braki table? (like here below):

    Click image for larger version. 

Name:	Bez tytu?u2.png 
Views:	32 
Size:	43.9 KB 
ID:	29549


    Question2:
    If my problem connected with attaching all reminders' dates to each document is solved now?

    I will be very grateful for your help and tips, my boss is chasing me because of i still do not have a model for this...

    In attachment please see my database.

    Best Wishes,
    Jacek Antek
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Language barrier getting in the way. I do not understand what all the data entities are and how they relate to each other. What are tbl_Braki and tbl_slow_spolki for?
    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.

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok i am stupid...

    For second question:

    I can remove table tbl_Reminders and give simple field Reminders_Date into tbl_Documents.
    In tbl_documents i have list of all documents for each employee with Data_Wysłania (Send date).
    So my VBA code can loop through tbl_Documents simply...

    So my corrected model looks like:

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	32 
Size:	32.6 KB 
ID:	29553

    Okey still is question 1 to answer...

    Please help,
    Jacek Antek

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok June7,

    I am explaining:

    tbl_Braki - old table - it should be remove.

    tbl_slow_spolki - it is table with clients information.
    I can translate all fields for you if you want.

    Best Wishes,
    Jacek Antek

  7. #7
    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
    16,716
    Jacek,

    As I suggested earlier, readers do not know your environment or your business. If you start with a clear description in simple English, it will facilitate communications and get you to focused responses more quickly.

    Also, as I showed in an earlier post, you can use Google translate to convert a clear, thorough Polish description into English (and/or English to Polish).

    As for relationships and tables, these are derived from business rules/facts. If you have a clear description of the business, you can develop and test a data model with sample data and scenarios. Getting your tables and relationships designed to support your business is critical to database. If you don't get these designed and tested, or feel you can do this later, you are in for serious headaches, frustration and will be creating "work arounds" for this all too common design flaw.

    As always, good luck with your project.
    Last edited by orange; 07-20-2017 at 03:29 PM.

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi orange,

    as always you are voice of reason

    Ok,

    the model here is similar to model from this topic:

    https://www.access-programmers.co.uk...d.php?t=294029

    but with few difference which i would like to discuss here.

    Process:

    My company supports a lof of companies: clients.
    Within these companies there are employees.

    And now each employee can have multiplce documents (lacking documents).

    So users in my fact table should write all lacking documents for each client.
    Target for database: to report all lacking documents for specific employee and clients for specific people (list in Excel, it will be the table - screen above in my first post or sample database in attachmanet - tbl_Reminders_employees table).
    So users will be inputing lacking documents for each employee within specific company and users will have possibility to push the button and send e-mail from table facts for specific criteria
    (for example for John Smith wtihin dates 2017-01-01 for Podobszar (area): Wrocław.)
    Now i have to prepare model to do this.

    Clients and information for them are refresh avery day with uptade query (from Excel spredsheet: my tbl_Clients table).
    tbl_Employees - also information refresh every day with uptade query (when user is opening front-end macro is running).

    We have also to enclose here the time of sending reminder to client in table fact or maybe independently in separate table?

    Also i translate all fields for you Guys, maybe will be easier...

    I think that model which I am starting to create can be good approach, but i need only a little consultation at this.

    I also added the table tbl_Reminders_employee - this is a table with employees and theirs clients where should be reminders send.
    I think that this table can be combined with tbl_Employee but problems connected with this:

    1) tbl_Reminders_employee is a dictionary table where all data are manually inputed by users (it will be changing by request).
    2) tbl_Employee is automatically updated table in Excel which is inputed to Access using update queries and this is automatically work.

    we can assume that tbl_Reminders and tbl_Employees are already in Access.

    Thank you for your help and support,
    Warm Regards,
    Jacek Antek
    Attached Files Attached Files

  9. #9
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Anyone could confirm that this model is quite good ?

    Jacek

  10. #10
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Did anyone have a similar problem ?

    Best Wishes,
    Jacek Antek

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't think DokumentID field tblDocuments should be a primary key.
    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.

  12. #12
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Okey June,

    thank you,
    why do you think that?

    Best Wishes,
    Jacek Antek

  13. #13
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    What about model design and sending reminders table?
    It is necessary to build separate table or not ?

    Jacek

  14. #14
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Okey,

    i have found the one not neccessary field in my model:

    1. It is tbl_Document.Is_Nessesary field. This field is not needed here because the summary table (tbl_Documents) is related with tbl_Documents_Types where already this field exists.

    Thank you Guys for help and support,
    Jacek Antek

  15. #15
    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
    16,716
    jaryszek,

    To determine/analyze/
    "confirm that this model is quite good"
    , here's a link that you may find helpful.

    Good luck with your project.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-25-2016, 09:42 AM
  2. Replies: 9
    Last Post: 05-26-2016, 05:13 PM
  3. Create Relationships
    By snowboarder234 in forum Access
    Replies: 4
    Last Post: 03-20-2012, 04:27 PM
  4. Replies: 5
    Last Post: 09-17-2011, 12:44 PM
  5. Replies: 3
    Last Post: 06-07-2011, 05:05 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
  •  
Other Forums: Microsoft Office Forums