Results 1 to 5 of 5
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Problem with relationship - bulding database

    Hi Guys,

    I have problem with relationships:

    I want to simplify my process desription in order to do it quickly ;-)

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	21 
Size:	16.2 KB 
ID:	30867



    My process:
    1. I have one correction document which can have multiple dates.
    One correction document is for one person.

    so i have 1 to many relationships between tbl_korekty and tbl_Daty.

    So user when is doing correction for Paul Zielinski and input dates from 01.2015 to 03.2015 input will be:

    Paul Zielinski, 01.2015
    Paul Zielinski, 02.2015
    Paul Zielinski, 03.2015

    because of this relationship.

    And now each record, each month can have also changed status, and date of changing it by user should be saved in my database.

    So i created tbl_Status table which has many dates for one record in tbl_Daty. and status_name within tbl_Status_name is always related to tbl_Status (1 to 1 relationship)

    So output table looks like (the last is the status change date )

    Paul Zielinski, 01.2015, status1, 17-10-2018
    Paul Zielinski, 01.2015, status1, 18-10-2018
    Paul Zielinski, 02.2015, status 2, 17-10-2018
    Paul Zielinski, 03.2015, status 1, 17-10-2018

    it seems ok.

    Problem appeared when my client changed his idea for tool.
    And now he wants to change status for one person once a time.

    So input:

    Paul Zielinski, 01.2015, status1, 17-10-2017
    Paul Zielinski, 02.2015, status1, 17-10-2017
    Paul Zielinski, 03.2015 status1, 17-10-2017

    If user chooses to change status from staus1 to status2 the input should be:

    Paul Zielinski, 01.2015, status2, 18-10-2017
    Paul Zielinski, 02.2015, status2, 18-10-2017
    Paul Zielinski, 03.2015 status2, 18-10-2017

    How can i create this?
    Please ask if you do not uderstand something,

    Best Wishes,
    Jacek Antek

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If status name is repeated in tbl_Status then how can this be a 1-to-1 relationship?

    Cannot link two autonumber fields so I hope one of those is not an autonumber.

    You give example records but it is not clear to me which table(s) the data comes from. Employee name is not in tbl_Status yet you appear to show employee name with data from tbl_Status.

    I am really not understanding what you asking for help with.

    If you want to change values in tbl_Status records then do so. What is preventing it?

    If you want to save history of status you can add new records in tbl_Status.
    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
    Thank you June7 !

    good point.

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	17 
Size:	20.3 KB 
ID:	30868

    employee is from tbl_Login_Emp.
    It is okey but if you have dates for each correction you can have a lot os statuses also for each record.

    So i want to give possibility to users to change status at once.

    For example let's take Korekty_ID, Name&Surname, Data_korekty (correction_date), status_date and status_name.

    We have in this case:

    1, Paul Zielinski, 01.2015, 16-10-2017, status1
    1, Paul Zielinski, 02.2015, 17-10-2017, status2
    1, Paul Zielinski, 03.2015, 18-10-2017, status3

    It is ok. I can create a subform and users will change all the statuses separately - for each record.

    I want to do it somehow different. User should have possiblity to change status for each Korekty_ID. Not for record.

    So changing status for Korekty_ID= 1 for status 2 should give the output like this:

    1, Paul Zielinski, 01.2015, 18-10-2017, status2
    1, Paul Zielinski, 02.2015, 18-10-2017, status2
    1, Paul Zielinski, 03.2015, 18-10-2017, status2

    how can i redesign my model to achieve this?
    Table tbl_Daty has to remain like here.

    Best Wishes,
    Jacek

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    An UPDATE sql action should be possible. In VBA, like:

    CurrentDb.Execute "UPDATE tbl_Status INNER JOIN tbl_Daty ON Daty_ID_FK=Daty_ID SET Status_name_ID_FK=" & Me.tbxStatus & ", Status_date=#" & Me.tbxDate & "# WHERE Korekty_ID_FK=" & Me.tbxKorektyID
    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
    wow June7 excellent !

    Thank you very much !

    It can solve all my problems!

    Best wishes,
    Jacek Antek

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

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  2. Database design relationship problem
    By cysklement in forum Access
    Replies: 4
    Last Post: 12-04-2014, 08:47 AM
  3. Replies: 2
    Last Post: 03-29-2014, 02:56 PM
  4. Relationship problem!
    By jamal numan in forum Access
    Replies: 6
    Last Post: 01-29-2012, 02:13 PM
  5. One to many relationship problem
    By Catherine in forum Access
    Replies: 3
    Last Post: 12-27-2011, 04:08 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