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

    Database concept - filter combobox based on Login table

    Hello Guys,



    I am creating new database and i would like to ask you about the support.

    I have tbl_Login table where i have (example):
    1.LoginName - ljar01
    2.Section_name - SOD (or ZUS)
    3.Section_branch - SOD_kadry (or SOD_płace or ZUS)

    2th records:
    1. pzie00
    2. ZUS
    3. ZUS

    Now i have also 3 different tables (dictionary table)
    a) tbl_Sekcje:
    1.SectionID - autonumber
    2.Section_name - the same as in Login_Table
    3.Section_topic - txt field (dictionary)

    Example:
    1. 1
    2. SOD
    3. Topic 1

    2th record:
    1. 2
    2. ZUS
    3. Topic 2

    b) tbl_Przyczyny (dictionary table)
    1. CorrectionID - autonumber
    2.Section_name - the same as in Login_Table
    3. Correction_cause - txt field
    4. Money - money field

    Example:
    1. 1
    2. SOD
    3. Cause1
    4. 400 zł

    2th records:
    1. 2
    2. ZUS
    3. Cause2
    4. 1000zł

    c) tbl_Korekty (summary table):
    1. ID - autonumber
    2. Section_FK - foreign key to tbl_Sekcje table
    3. Correction_FK - foreign key to tbl_Przeczyny table.

    And now my question is how to add records to summary table based on section_name from tbl_Login.
    How to create form and set up correctly database?

    Example:
    Login ljar01 has section_name SOD.
    So user if is login and opening the Main Form (tbl_Summary Form) should see only values from comboboxes with filtered section name = SOD.

    In above example user should have possibility to choose only within Comboboxes: Topic 1 from tbl_Sekcje and Cause1 from tbl_Przyczyny.

    Question 1 :
    Can i conlude Section_branch - SOD_kadry (or SOD_płace or ZUS) within Section_name and give it the numbers?
    So SOD_kadry = 1
    SOD_place = 2
    ZUS = 3

    and if user choose SOD_Kadry within table tbl_Login the value 1 will be asssing to field Section_name (administrator work,
    administrator doesnt have to know that number 1 = SOD_Kadry, the text here will be better solution).
    How can i create this some kind of lookup field within one table?

    So user choosing within Combobox SOD_place will be automatically choosing also number 1 (but it will be hidden?).

    Question 2:
    During opening the form i can read section_name using temp variable and input it into...where ?

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	51 
Size:	18.4 KB 
ID:	29874


    Please give me a hint Guys,
    Best Wishes,
    Jacek Antek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Guys one more time,

    I have solve question1:

    You can build combobox based on your owns value using lookup wizard in table design view:

    Attachment 29875

    Problem is how user can add values to it ? using form and bulding query table as source of lookup data?

    Best Wishes,
    Jacek Antek

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am not entirely following your explanation (sorry - Orange may be better at this!). One thing I would question, however, is the repetition of the Section_Name field across all tables. This is breaking a rule of normalization. Instead you would have a sections table (unless that is tbl_Sekcje?) and carry only the ID across to the other tables. I am unable to open your attachment, it says it is an invalid link.

    Maybe if you explain the purpose behind each table it would help us.

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    aytee111 - thank you, as always

    i will provide the correct link tomorrow.
    What do you do not understand?

    I know that section_name should be only once in tables. But i do not know how to do it.

    Section_name is only SOD_place, ZUS , SOD_kadry. so only 3 records and one field.

    And now depending on users' login : section_name can have multiple section_topics and separately multiple corection_causes and money attachet to them.

    Best Wishes,
    Jacek Antek

  5. #5
    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,

    I don't understand your post either.
    You are telling HOW you want to do something, or HOW you think you should implement it.
    But, I am not sure WHAT you are trying to do in simple, plain English. A clear description with no quasi-database terms and no jargon may help you get answers specific to your issue.

    You can not get focused/specific responses if people are having to guess the details of your post.

    Once readers understand WHAT you are trying to do/solve, there may be options/alternatives for HOW to implement it in Access.

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

    how are you ?

    Thank you for your answers!

    Ok i have try few things in my sample database and i want to show you it - example will be suitable to show what i mean.

    tbl_Korekty - my summary table
    tbl_Sections_Dictionary - my table with ID (autonumber) and 3 text fields meaning Areas where employee (user which is logged in) works:
    1. SOD_place
    2. SOD_kadry
    3. ZUS

    In other words - employee works always only in one Area.

    tbl_Przyczyny - For each correction(ID) in tbl_Korekty there can be one Correction_cause and Money for it - depends on area where employee works
    tbl_Sekcje - For each correction(ID) in tbl_Korekty there can be one Topic - depends on area where employee works

    In my example there is form frm_Login where if you are writing username: ljar01 and password: test , then will be tempvar("Section_name") saved:
    For ljar01 it is SOD_Płace Area (=1)

    Click image for larger version. 

Name:	tempvar.png 
Views:	43 
Size:	87.3 KB 
ID:	29888

    Ok User is opening Form tbl_Korekty:
    Automatically Section_name text box (unboud) is fullfiled by tempvar("Section_name"), in our example tempvar("Section_name") = 1.

    So tbl_Korekty.Section_FK is fullfied based on Section_name texbox value :

    Code:
    =[Forms]![frm_Korekty]![Section_name]
    So now i know that employee ljar01 is working within Area SOD_Płace.

    All what i want to do it is create the relationship in order to get specific fields from tbl_Sekcje and tbl_Przyczyny which i described above (screen below):

    Click image for larger version. 

Name:	sections.png 
Views:	42 
Size:	21.4 KB 
ID:	29889



    So in Combobox tbl_Przyczyny should be only values Cause1 and Cause5 to choose (because employee works within Area SOD_Płace)
    And in Combobox tbl_Sekcje should be only values Topic1 and Topic3 to choose (because employee works within Area SOD_Płace)

    I do not know how to create relationships here.
    Please help Guys,
    Best Wishes,
    Jacek
    Attached Files Attached Files

  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,

    Please describe the "business" this proposed database will support. I see Employees and Areas where they work, but have no idea of the "business" involved.
    Try 4-5 lines of simple plain English --no database terms and no jargon.

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

    okey.

    But it is my purpose to show you only part of the tool - technical solution and how to do it --> because tool is very demanding. I do not want to add here clients names and employees from SAP here in order to do not mess here. Additionally now i am starting and try to implement first technical solution. Just to know.
    But ok... Maybe your approach is better - thank you!

    Employees(Users) within theirs Area has to input data into summary table. They have to register all data and after that should have possibility to send it via e-mail to clients. (each employee will have report consisting of correction cause, money and topic)
    Data are from paper and as usually in my tools - they are provided by clients.

    So Employees(Users) for each employee from paper or for ID (in my sample database) has to write what caused the correction, what amount of money was spent, and what is topic of this correction.

    Please ask me if you have any further questions,
    Best Wishes,
    Jacek Antek

  9. #9
    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
    We are not asking about the tool. We are asking what, in plain English, is the overview purpose of the tool. What are the requirement for the tool?

    If you could buy a variety of tools off the shelf --and the salesman asked you --What exactly are you looking for? What does this tool deal with? What does it output?....

    Nobody wants your data, nor are we trying to get private information---you can call it widgets or whatever. What do you want to do with these "things"?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want cascading comboboxes? Maybe:

    ControlSource for Tekst35: =[Forms]![frm_Login]![Section_name] (or some other method to pass Section_Name value to frm_Korekty)

    RowSource for Kombi47
    SELECT [tbl_Przyczyny].[PrzyczynyID], [tbl_Przyczyny].[Correction_cause], [tbl_Przyczyny].[Money] FROM tbl_Przyczyny WHERE Section_Name = [Tekst35];

    RowSourcefor Kombi49
    SELECT tbl_Sekcje.* FROM tbl_Sekcje WHERE Section_Name = [Tekst35];

    Then code in each combobox GotFocus event to Requery itself.

    Me.Kombi47.Requery

    Me.Kombi49.Requery

    Bind the comboboxes to fields.

    Also, set Section_FK and Correction_FK fields in tbl_Korekty as compound index to prevent duplicate pairs.

    Options for data entry in a many-to-many relationship:

    1. single form bound to tbl_Korekty with comboboxes to select correction cause and section topic

    2. main form bound to tbl_Przyczyny and subform bound to tblKorekty with a combobox to select section topic

    3. main form bound to tbl_Sekcje and subform bound to tblKorekty with a combobox to select correction cause
    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.

  11. #11
    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,

    If you understand the requirement, would you please show us in point form?

    Jacek seems to want to show us a solution, but aytee111 and I do not comprehend the requirement or business set up.

    I think it would help the op and readers if we had some description of the issue/opportunity to be solved.

    Thanks.

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

    you are amazing !

    I will consider your solution but i am afraid that this is not the most efficient way to create relationships here.
    It is because of Section_name field - it appears 3 times instead of one ! And this is the root cause of my problem here.

    How to build relationship to avoid occuring Section_name 3 times here...
    What should i do or what i should describe you in details in order to explain it to you ?

    ------------------->

    orange,
    thank you for your help and support, i appreciate it.

    I already have answered for your question but i am going to answer it once again:

    Code:
    What are the requirement for the tool?
    People now are writing all processed clients' employees into different Excel's workbooks.
    For each employee invoice corrections can be done for all months when these employees worked.

    So John Smith worked in my company from 2009 to 2015 (or still is working).
    In 2011 from january to may when he got invoice for his work there were a mistakes.

    Now users should take the John Smith from paper and do corrections for each month.
    So for this ID users should correct the invoices for each month.

    Invoice1, employee1, company1, invoice topic1, january 2011, 300 zł
    Invoice1, employee1, company1, invoice topic1, february 2011, 600 zł
    Invoice1, employee1, company1, invoice topic1, march 2011, 400 zł
    Invoice1, employee1, company1, invoice topic1, april 2011, 1000 zł
    Invoice1, employee1, company1, invoice topic1, may 2011, 2000 zł

    Code:
    What exactly are you looking for?
    The input raport should show each employee for what company, and for what correction cause and for what amount invoce correction was done.

    .............................

    orange, maybe we are a little missunderstanding each other - but together we can create the great solution!

    thank you very much for your help!

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think language barrier is interfering. What do you mean by 'he got invoices for his work' - why would employees get invoices? Do you really mean payroll statements?

    Apparently I did not understand the requirement. I am now really lost. You describe invoices and correcting invoices but your posted db doesn't seem to have any data that in any way resembles invoices (nor pay statements).

    So data entities are employees, sections, invoices (or pay statements?), causes, topics?

    What is the business flow? How are these entities related?

    If you have a standard set of topics and you want to limit list of topics by the section an employee is associated with, then need a table of topics with field for sectionID (tbl_Sekcje). Same goes for list of causes (tbl_Przyczyny). Then there is the lookup table for sections (tbl_Sections_Dictionary). So yes, section is in 4 tables.

    But why would section be in tbl_Koretky? Why would section have a status? If you are correcting an invoice/payroll error then where is the table that logs the invoiceID (or pay period), its error topic, status of that correction?
    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.

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

    thank you.

    I did the mess as always, sorry for that.

    In my sample database i did not do the table with employees and companies (i wanted to avoid not necessary data) .
    Invoice is not good word for this (sorry for my english btw).
    It is a data from paper for each employee - what should be corrected in system. Example for this is:

    So John Smith worked in my company from 2009 to 2015 (or still is working).
    In 2011 from january to may when he got invoice for his work there were a mistakes.

    Now users should take the John Smith from paper and do corrections for each month.
    So for this ID users should correct the invoices for each month.


    This should be:
    John Smith worked in my company from 2009 to 2015 (or still is working).
    In 2011 from january to may he has to have corrections done in system (in SAP ).

    In reference to this, users should take the John Smith amounts of money from paper for specific period of time, and do corrections for each month for him.
    For this Employee users should correct the amounts of money for each month.

    so we can call it "correction".

    Data entities are:
    employees - for which employee correction has to be done - example above (name_surname, unique ID)
    login_employees (users) - people who are processing all corections and have to register it in Access tool
    companies - companies where each employee works
    corrections - correction_cause (one for one correction - one corection_cause but few moneys' records foe each corrected month), corrected money for each month, correction topic (one for one correction)

    If you have a standard set of topics and you want to limit list of topics by the section an employee is associated with, then need a table of topics with field for sectionID (tbl_Sekcje). Same goes for list of causes (tbl_Przyczyny). Then there is the lookup table for sections (tbl_Sections_Dictionary). So yes, section is in 4 tables.
    Yes, i want to exactly do this !

    Could you please show me this in my new sample database?

    But why would section be in tbl_Koretky?
    Because tbl_Korekty it is my summary table. tbl_korekty contains one case number (correction number = ID). So user in this table will be registering all processed corrections and give them statuses.
    This is like in the picture below from my other database model:

    Click image for larger version. 

Name:	ssss.png 
Views:	37 
Size:	21.0 KB 
ID:	29912
    tblClientDocuments is the summary table and from there all other data entities are related.
    In my present database i have to relate somehow each correction with employee's area (Section_name).

    It is necessary to give users opportunity filter only their areas in order to report it.

    Why would section have a status?
    Section has to have status because of first data will input users with area SOD_kadry, next one will be SOD_płace.
    So Employees from SOD_place looking at status will know that they can start to process it and change data in SAP.

    ZUS is separately from it.

    Code:
    If you are correcting an invoice/payroll error then where is the table that logs the invoiceID (or pay period), its error topic, status of that correction?
    This is table tbl_Korekty and InvoiceID = CorrectionID is ID in my sample database.


    ---->

    I created new sample database - added table Employees, tbl_Money (for each employee for every month can be few records with amounts of money)

    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	37 
Size:	79.1 KB 
ID:	29916

    Now if I am understanding your solution here:

    If you have a standard set of topics and you want to limit list of topics by the section an employee is associated with, then need a table of topics with field for sectionID (tbl_Sekcje). Same goes for list of causes (tbl_Przyczyny). Then there is the lookup table for sections (tbl_Sections_Dictionary). So yes, section is in 4 tables.
    I have to insert additionally 2 foreigns keys into tbl_Korekty: Sekcje_FK (to get topic from there) , Przyczyny_FK (to get Correction_cause) from there.
    And i can filter necessary section_name within these tables. It would be the simplest thing ever, but... most efficient ?

    Thank you once again for helping me friends,
    Best Wishes,
    Jacek Antek
    Attached Files Attached Files

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

    Here is what I am "getting" from your post and June's comments.

    You deal with Companies who may have Departments and/or Sections (some organizational units)
    Such Units have Employees.
    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.

    ?? Could it be the Employee who notices the error? Or
    ?? Is it always the Company (or some other organization)??

    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.

    In order to "correct" the Pay record in error, you need to know
    - the original amount
    - the revised amount
    - the reason for the error




    Here is a fact sheet (specific to Australia) but parts will be relevant to your situation.

    Code:
    What information must be included on the pay slip?
    
    Pay slips must contain details of the payments, deductions, and superannuation contributions for each pay period. 
    The following information must be included on all pay slips issued to each employee as prescribed by the 
    Fair Work Act 2009 and the Fair Work Regulations 2009.
    
    A pay slip must include all of the following:
    
        the employer's name
        the employer's ABN (if any)
        the employee's name
        the date of payment
        the pay period
        the gross and net amount of payment
        any loadings, monetary allowances, bonuses, incentive-based payments, penalty rates, or other 
    separately identifiable entitlement paid.
    
    Additionally, where relevant, a pay slip must include any of the following:
    
        If the employee is paid an hourly pay rate, the ordinary hourly pay rate and the number of 
    hours worked at that rate and the amount of payment made at that rate
        If the employee is paid an annual rate of pay (salary), the rate as at the last day
     in the pay period
        
    Any deductions made, including the name, or the name and number, of the fund or the 
    account of each deduction
        
    If the employer is required to make superannuation contributions for the benefit of the employee:
    
            the amount of each contribution the employer made or is required to make during the pay period
    
            the name, or name and number, of any superannuation fund into which the contributions
     were made or will be made.

Page 1 of 2 12 LastLast
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