+ Reply to Thread
Results 1 to 9 of 9

Access 2010 control to return value based on data from 2 drop down box selections

  1. #1
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Access 2010 control to return value based on data from 2 drop down box selections

    Hello- I am attempting to return a value from my table based on the results of 2 drop down box selections.

    I want a value entered into the Rate field that corresponds with the employee selected and the job type worked. I attached a sample of my table but please let me know if you need more information.

    Jodi
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Your title indicates an Access issue, but you uploaded an excel file. Is this an excel or access issue?
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Hi alansidman- It is an Access issue. What I provided is the export from the table I need to gain the information from. If you see the employee names in column A and then going across the headings are the various jobs. The intersecting is what I need to populate. I can attach the database if that would be more helpful. I would just need to take out some personal information for the employees first. Please let me know what you think.

    Thank you,
    Jodi

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Jodi,
    I am not understanding what you wish to accomplish. Please provide examples using the table provided of what you are trying to achieve.

  5. #5
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Hi Alansidman- I'm sorry I'm for the confusion. So what I am trying to do is get the query to review the employee name selected and the work type selected and return the value that corresponds to both.

    So if you look at the spreadsheet I provided- If Acklin, Robert and Recyle are entered into the table, the query will add the result 5.71 to the Rate field of the table. (I can do this by using an iif function however that would be a very long statement because we currently have over 40 employees and growing. I don’t want the user to have to mess with code when new employees or jobs are added- not sure if that is possible).

    From there, I need to be able to get the total for each work type ordered by each employee for the 1-15th and then the 16th-end of month. Example of what that looks like currently on our spreadsheet:

    1ST - 15TH
    HOURS 0.00 EN Enclave $-
    0.00 VT V-Train $-
    0.00 LR Laundry $-
    0.00 JT Janitor $-
    0.00 RE Recycle $-
    0.00 RR Resi Rec $-
    0.00 SH Shred $-
    0.00 TS Thrift $-
    0.00 CL Clean $-
    0.00 PW 0.0 $- $-
    0.00 PW 0.0 $-
    0.00 PW 0.0 $-
    0.00 TT 0.0 $- $-
    0.00 TT 0.0 $-
    0.00 TT 0.0 $-



    Total HOURS 0.00
    Total DOLLARS 0.00

    So I have the simple sql written out right now I just don’t know where to go to make it look up what information I need. I know I could use a Match/Index formula if I were doing it in excel but I don’t think that works in access or sql...

    SELECT Calendars.Work_Date, Calendars.Employee_Name, Calendars.Work_Type, Calendars.Hours_Worked, Calendars.Pieces, Calendars.Rate, Calendars.Pay_Rate, Calendars.F_S_Pay_Rate
    FROM Calendars INNER JOIN Job_Type ON Calendars.Employee_Name = Job_Type.Names;

    Calendars is the table where the data will go from the form as they enter the time and work detail.
    Job_Type is the table that I sent you in Excel that has the names of the employees and the pay rate for each job type.

    I hope this makes what Im trying to accomplish a little clearer. Thank you for helping me- I really appreciate it!

    Jodi
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    I also included the actual spreadsheet we are currently using which I am attempting to convert to Access. If you look at the Payroll Totals tab- this is what I am in need to reproducing. Doesn't have to necessarily look this way but needs to provide the same results.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    part of your problem is your table is not normalized. It is in a spreadsheet format that will make getting data from it very cumbersome.

    You should have two tables.

    tblEmployee
    --------------
    EEID (PK) autonumber
    EmployeeName text

    tblJob
    ----------
    JobID (PK) autonumber
    JobDescr text
    JobRate number

    then you need a junction table as you have a many to many relationship between the employees and the jobs

    tblJunction
    ------------
    JunctionId (PK) autonumber
    EEID (FK) number
    JobID (FK) number

    You can then build your query using a search form listing the employee and the job to get the rate.

    Look at this whitepaper to understand normalization

    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

    I hope this helps you to get a better understanding of Relational Databases.

  8. #8
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Thank you so much for your help- I was thinking that the table was designed wrong but didn't know how I should change it to make it work. Seems obvious that it should be two tables and the auto number id makes that possible. That really helps me a lot. So I am going to work through those updates and then I think I will be able to get the Rate and Totals completed.

    Since this is clearly going to take me longer than the time I have allotted to it- I need to fix one of the formulas on the attached spreadsheet. Maybe you can help me figure out how to rearrange or change it so that I don't have to change the formula each month. I will increase the points by 10 if you will also help me with another formula that is causing me trouble. You just let me know what you think.

    First Formula- The first tab of the March 2015 Production Test document attached does some simple math based on the day of week. This whole spreadsheet is put together so that they can get the total for certain criteria which you will see in columns DH-DS and the State Aid Calculatons.

    Starting with Columns C-T what it is doing is determining providing by day the total hours worked- So M-F gets reported separately from Sat/Sun.

    Because the day of week that the month starts on is always different- the formula in column C-I has to change so that what is reported as Sat/Sun or M-F is accurate. The current formula is looking at each employees tab and simply returning the value based on the day of week.

    Again- all of this is simply to gain the information for the State Aid Calculations portion of the spreadsheet. Is there a formula that will automatically determine if the day of week on the employee tabs matches the day of week on the March 2015 tab keeping in mind that on this tab the weeks are split up based on their work weeks of Saturday - Friday.

    Example: The formula for the first employee: =IF('ACKLIN, R'!$A$4="Sunday",'ACKLIN, R'!$C$21,"0.00")
    So next month on the Acklin, R tab- A4 wont be Sunday which will appropriately give a 0 but A4 will be Wed- So I need it to actually tell me if the dates match then provide the results from C21.

    I hope this makes some sense! If you need me to explain further or better lol please let me know!

    Thank you again for your help,
    Jodi
    Attached Files Attached Files

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Access 2010 control to return value based on data from 2 drop down box selections

    Since this is a new issue and an excel issue, I urge you to post a new thread in the proper excel sub forum for help on this issue. I currently don't have time to work on your issue and therefore you would be better served by our band of experts and gurus on this issue in a new thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populate data based on several drop down selections
    By NerdALRT in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-25-2014, 03:42 PM
  2. Changes VLOOKUP values based on two Drop Down (Data Validation) Selections
    By EssexKiwi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-10-2014, 08:16 AM
  3. Access 2010 - control what is printed by a yes/no box within three tables
    By LadyScot101 in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 12:28 PM
  4. [SOLVED] Copy Data based on selection in drop down control
    By smssms in forum Excel General
    Replies: 7
    Last Post: 04-26-2013, 08:53 AM
  5. Filter data in one tab based on drop down selections in a different tab
    By ragonef in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2012, 09:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1