Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    orange,

    thank you very much !

    Your description is good but i would like to correct few sentences.


    You deal with Companies who may have Departments and/or Sections (some organizational units)
    Such Units have Employees.


    My company has a lot of internal and external clients - other companies (we are outsourcer).
    In my company there are employees which are servicing my company's clients.
    Each employee in my company has his own department (area) and subdepartments.
    Within my company's clients there are employees who should be serviced by employees from my company.

    The Employees get paid for Work done in a time/Pay period.
    At some future point, there is a realization that there was an error in the Employee's pay record/pay slip/pay check.
    You want to correct the Employee's Pay record and identify the Cause of the error.
    It is connected with disability and moneys paid by my government.
    Client's Employee can get additional money when they get disabled or change their disability level (from 1 to 2 or from 2 to 3).
    Employee has to go to polish court and for example it lasts one year (or longer).
    Employee is working all this time, and after year he gets positive opinion from court that he should get the additional money for present time and for 1 year before.


    Now my company enter here. All corrections' tickets are inputed by different people from other departments into Probit or SAP system.
    And now my department has to check all these corrections (their correctness) and input it into SAP or Probit systems.

    Problem is that in my company within these systems there are not any adequate report for our clients. Clients want to know everything including exactly amounts of money for each employee.
    So in consequence i am doing Access tool to help people with creating reports for our clients.


    ?? Could it be the Employee who notices the error? Or
    ?? Is it always the Company (or some other organization)??
    It is always company or other organizations.

    Code:
    The Employee's pay rate is known.
    The length of the Employee's Pay period is known.
    So the Employee's Gross Pay can be calculated.
    The appropriate Taxes/Deductions are known.
    So the Employee's Net Pay can be calculated.
    All what is known Employee Correction amount and for each period.

    Code:
    In order to "correct" the Pay record in error, you need to know
    - the original amount
    - the revised amount
    - the reason for the error
    I need get report from SAP for specific month which will show what each employee has to have corrected.

    Best Wishes,
    Jacek Antek

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

    My description was my best guess and used your posts and June's comments to build it. I would be extremely surprised if my guess was very close based on the uncertainty of the posts and the lack of overview description. I now recognize that you have internal and external clients; that you get pay adjustment info from a government department; pay adjustments can occur retroactively based on government assessment....
    It also sounds like you have the SAP system and your proposed Access system that must work in parallel in order to obtain all relevant information. Is that correct?

    Here is a draft model based on the things mentioned in the various posts. It seems that there is a disconnect between the SAP system and the Government Adjustment Info. I may have collapsed a few things, but this is a draft model of the subject matter. It may be way off base. It may be useful.
    Attached Thumbnails Attached Thumbnails jacek_CompClientPayAdj_Draft0.jpg  

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You originally had section in tbl_Employees which would allow limiting lists of topics and causes by section associated with employee. If you have section in tbl_Employees it is not needed in tbl_Korekty - selection of employee would also identify the section. The post 14 model removes section from tbl_Employees so employee cannot be a restricting parameter. I suspect an employee could work in various sections over a period of time involved in the issue. This would mean employees and sections have many-to-many relationship. So tying employee to a single section would not be appropriate. Select employee and section when record is created in tbl_Korekty. Restrict topic and cause choices by the selected section. Save topic and cause into tbl_Korekty. Actually saving section into tbl_Korekty is really optional because the section can later be retrieved through either topic or cause saved in tbl_Korekty - just as it could have if section were in tbl_Employees.
    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.

  4. #19
    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
    June, Jacek,

    Jacek updated his description and showed he uses Client and Employee. As I read it (still a little confused), his company deals with internal and external clients. He also said that Employees inputted the data from paper records received from the gov't. to adjust existing pay records.
    I used Employee to represent the "data entry/input" person. I used Client to represent the "person" (whether internal to his company OR external to his company)--that is the person whose PayCheck/Stub needs to be adjusted.
    As I have asked Jacek, clarity in the requirements is key to getting the db structure designed to support the requirement.

    What exactly is a Client? What exactly is an Employee? No doubt an Employee of his Company could be an Internal Client. But the details have been hard to extract. I don't think such a process would allow an Employee -who is also the internal client involved -to adjust his/her own record---but again that is a guess on my part.

    Some of these are guesses on my part, and he can adjust, clarify or ignore as he feels they are relevant. The draft model was a target to be tested, shot down, adjusted.....

    It seems he is focused on physical Access while some readers (me and possibly aytee111) are still trying to sort out facts for analysis and design. And I do appreciate that there is more than 1 way to design a database.

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I agree, still a lot of confusion. However, employee/client/widget, concept of restricting choices by association of values is same. He just needs to pin down the entities and relationships.
    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. #21
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Guys,

    thank you for your help! orange - nice map - i will refer to it later within my post.

    Ok let's go

    It also sounds like you have the SAP system and your proposed Access system that must work in parallel in order to obtain all relevant information. Is that correct?
    I have SAP, QV and Access.
    Access is register tool.
    From SAP i have data about working employees and my clients.
    In QV automatically I am combining and joining different instances of SAP in order to get txt files.

    Next these txt files with companies and SAP employees are every day automatically put into Excel files. These Excel files are my every day refreshing Access tables (using update, append and delete queries).

    Code:
    You originally had section in tbl_Employees which would allow limiting lists of topics and causes by section associated with employee. If you have section in tbl_Employees it is not needed in tbl_Korekty - selection of employee would also identify the section.
    You (and orange) have a point here!

    I did not think that my users table (employees inputing data) should be joined with summary table! it is very good.
    The second thing here, i did not know that creating tables only with 2 fields (ID and lookup field) is appropriate. This knowledge is very helpful for me.

    1. One question here. If I have sections in tbl_Employees and sections are 3 : ZUS, SOD_place, SOD_kadry. These 3 options can be manually inputed into combobox like here in attachment (using list of values as type record source field and delete tbl_Sectionds_Dictionary table):

    Click image for larger version. 

Name:	logins.png 
Views:	54 
Size:	141.6 KB 
ID:	29932

    or this combobox field should have recordset set as table tbl_Sections_Dictionary?

    What is better? Or create relationship between tbl_Login_Emp (employees inputing data - Users) and tbl_Sections_Dictionary...?
    I am confused here now.

    I suspect an employee could work in various sections over a period of time involved in the issue.
    Fortunately, employee will be attached only to one section - always.

    Jacek updated his description and showed he uses Client and Employee. As I read it (still a little confused), his company deals with internal and external clients. He also said that Employees inputted the data from paper records received from the gov't. to adjust existing pay records.
    I used Employee to represent the "data entry/input" person. I used Client to represent the "person" (whether internal to his company OR external to his company)--that is the person whose PayCheck/Stub needs to be adjusted.
    Ok your model orange (nice map). I need to make little explanations here.

    My mother company is a big company where there are a lot of daughter-companies (about 100 or more). My company -IBS is one of them. Now my company can service companies within my mother company group (internal clients) and outside my company(separate companies - external clients). It doesn't matter for our purposes because as you did in your map - client is client and no matter internal or external.

    Now in your map there is whole table for goverment. This is not necessary here. Some companies and their employees within my company (internal companies) are inputting these data into SAP. This is separated process at all.
    My team where I am building this database has this data already in SAP.
    So from paper my team get list of adjustments where corrections have to be made. Now my team (Users or employees inputing data) checking if corrections is made good in SAP and if it is - they can do it via SAP. After all also should input specific client into register tool in Access.

    2. Second question here.
    Where is table with possibility to input each month and money value for each ID?

    For employee 1 for serviced company 1 can be for example 10 records - each one for each month where adjustment has to be done. These table should contain also moneyValue for each month.

    It seems he is focused on physical Access while some readers (me and possibly aytee111) are still trying to sort out facts for analysis and design. And I do appreciate that there is more than 1 way to design a database.
    I am appreciate that there are people here with other approach - i can learn a lot from you, thank you!

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

    are you there masters ?

    Jacek

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

    how are you?

    I solved problem connected with question 1 - i created foreign key in table tbl_Login_Emp and join sections_Dictionary table with one to many relationship.

    Question 2 - still in progress

    Question 3 - the most important for me right now:
    I combined table with employees and companies where they worked in QV:
    Click image for larger version. 

Name:	companies.png 
Views:	45 
Size:	12.1 KB 
ID:	30002

    As you can see it can be a lot of PESEL_numbers (unique for each employee) because of many companies and departments attached to it.

    This table I inserted from Excel flat file to Access. It is okey.

    Now i had also second table tbl_Employees uploaded also from Excel:
    PESEL (the same as PESEL_number) with Name (Imię) and Surname(Nazwisko).
    Problem is that i can't create i think the most efficient relationship here.

    PESEL can be few times in tbl_Employees (many Surnames and Names can be attached to it).

    My temporary solution:


    Click image for larger version. 

Name:	example.png 
Views:	46 
Size:	118.3 KB 
ID:	30003


    So tbl_Company.PESEL_number is left joined with tbl_Employees PESEL. But here i can't set up primary keys...
    How to do it ? It is necessary at all?

    When i was trying to create tbl_Employees.PESEL as Primary key there was a problem with duplicates there - Access doesn't allow to do that.

    Please help,
    All answers will be welcome,
    Best Wishes,
    Jacek

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If PESEL is not unique in either tbl_Company or tbl_Employees then these tables cannot be properly related. You need to assign CompanyID to each employee, not PESEL.

    What is PESEL - what data does it hold? Why would there be multiple names associated with a PESEL? For example, a PESEL can be associated with Jane Smith and Jack Jones?
    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.

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

    thank you!

    PESEL is unique for each citizen of Poland, so in this case PESEL corresponds to employee.


    No, Pesel can be associated only with Jane Smith or only with Jack Jones.
    Problem is with data quality here.

    Pesel in tbl_Employees can have multiple values because in source system there are mistakes.
    For example Jane Smith can be write as Jane Smith, Jane Smith-Mirgan, Jane Smith with space - thanks to it unique Pesel contains many name and surnames... Unfortunately i can change this data in SAP and my boss doesnt care about it...
    But of course he wants to get proper model.

    Best Wishes,
    Jacek

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

    so i have heer only 2 possibilities:
    1. go to source and make PESEL unique (for example takes only last surnames attached to given PESEL)
    2. leave this table separately and using Dlookup in order to input data into summry table...

    Thank you Guys!
    Best Wishes,
    Jacek

  12. #27
    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,

    As per my various posts, especially #17 and 19, you really have to describe your business facts so that readers (and you) understand your needs in context. I offered a draft model based on your info and June's comments/advice. You can use that model, adjust it, change it a lot or scrap it as you see fit. I tried to put all the pieces into some coherent picture using mostly guess work since you have provided little detail of the business, and little or no feedback on the how or if the model matches or does not match your business.
    You seem to want to work using physical Access and more of a trial and error approach. That is not the way I approach database, but I also realize different people do things differently. If you have errors/mistakes in the feeder system, then you'll have to address that. Better to address errors at the source or as close to the source as possible than to try to work around known errors and carry them forward.

    My recommendation is for you to do the analysis; get the facts; build a model/blueprint and test and revise that blueprint with data and test scenarios. Don't jump to physical database until you have a vetted model. Bounce it off colleagues to ensure all facets of the business under study have been accounted for.

    Good luck with your project.

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

    why didn't read you my post #21 with full explanation about my model?
    I referenced to your map there and was answering is your model matching to my business requirements.
    If you do not understand it - please ask me - i will do my best to answer you.

    I used yours model and improved my database design thanks to it. Thank you for that !

    In my post #23 there are relationships showed in the picture - my model is looking like that now. And it is working smoothly!

    Code:
    My recommendation is for you to do the analysis; get the facts; build a model/blueprint and test and revise that blueprint with data and test scenarios. Don't jump to physical database until you have a vetted model. Bounce it off colleagues to ensure all facets of the business under study have been accounted for.
    Aa it is my dream. I have to create something very very quickly and in my company almost always there is no care about data in feeder system (btw it is a nice word in english They want only result and me as developer has no choice to do it as i should do. So i am creating a lot of workarounds.... :/

    As i wrote about PESEL - i will try to back to feeder system and improve my tables... i hope it will work...

    Best Wishes,
    Jacek

  14. #29
    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
    Yes, I did read your 21. There is an expression in English ---"like pulling hen's teeth".
    It means that getting details or specifics is extremely circuitous; that every response to a request for info is more of a tangent than direct fact.
    Anyway, I think I have offered as much info as I can. I wish you success with your project.
    Good luck.

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

    thank you for that! For now i have to get to feeder system and try to improve my data there.

    thank you June7 also.

    I will come back and show my database later here - maybe it will be useful for others,

    Best Wishes,
    Jacek

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

Similar Threads

  1. Replies: 1
    Last Post: 11-05-2013, 10:09 PM
  2. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  3. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 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