Results 1 to 9 of 9
  1. #1
    rts is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    34

    Query Asking for Parameter

    Hello,



    I'm working on a query to calculate profit sharing percentages and I keep getting a parameter dialog box each time I run the query, even though the parameter for which it's asking is there. I'm also somewhat new to effectively using queries...

    I have a table called SalaryInfo with Employee ID as the FK joined to the Employee table. In the SalaryInfo table, I have the field ProfitSharePercent. In my query I have set the the "Totals" section of ProfitSharePercent to Last so that it will only show the most recent record for an employee's Profit Share Percentage. Access automatically renames this field "LastOfProfitSharePercent" when I use it in a formula to calculate the BaseProfitShare (Profit * LastOfProfitSharePercent). After I added the formula, however, a dialog box asks for "LastOfProfitSharePercent" each time I run the query. I just hit cancel or ok in the dialog and the query and formulas still work fine, but how do I get the Parameter dialog to go away?

    Thanks for your input!

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Quick question are you using the formula in the same query?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would be very cautious about using the function Last().

    Searching for info on the function Last(), in several places I find
    In Microsoft Access, the Last function returns the last value from the result set of a query.
    Another place, I find:
    Last refers to the ordinal position of a record in a set of data. If the set is not ordered, the actual "last" record is random. And even if it is ordered, if the sort key is not unique, records with equal sort key values will be in random order within their set. Remember your set theory, relational tables are unordered sets. Many people get fooled because small tables will usually maintain a consistant order. However, large tables are completely unreliable as to sequence of records unless they are sorted.
    Something to be aware of.......

  4. #4
    rts is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    34
    Yes, the formula is in the same query.

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    That's why it's prompting you for the parameter. It doesn't recognize it. It works when it runs because by then the field is created. In that circumstance you need create a second query using the first one as the source and add the calculated field there. That will eliminate the prompt. Also I would use the Group by instead of last. As ssanfu said last simply takes the last value Group by will get the same value plus if there are records with different amounts but everything else is the same you have no idea which amounts could be missing. I personally have never used last for that reason.

  6. #6
    rts is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    34
    Thank you - that makes sense. The "last" thing is working for me and the "group by" is not. I want to find the most recent information on each employee. The SalaryInfo table is on the many side of a one-to-many with the Employee table. It allows us to keep a history of each salary change and/or profit share % change for employees. If I use "group by" in my query, it will show a separate record for the same employee for each time they've had a salary/profit sharing change. If I use "last", it only shows the most recent record for that person. This query is going to be an append query to add all fields to a table to reflect profit sharing each year. I don't want it to pull up records of employee salary/profit sharing for years prior. Is this still the wrong idea?

    Thanks again for your help!

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Unfortunately last does not guarantee you're getting the correct record. It may work sometimes but it also may not work depending on how the records are returned. That's the point Steve was trying to make. Last does not guarantee the most recent record it only shows the last record based on the data returned. If you could post the text of your query here it would help us help you more efficiently.

  8. #8
    rts is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    34
    Here it is:

    SELECT TeamMembers.ID, TeamMembers.First, TeamMembers.Last, TeamMembers.StartDate, TeamMembers.ProfitSharingCategory, Last(SalaryInfo.Salary) AS LastOfSalary, Last(SalaryInfo.ProfitSharePercent) AS LastOfProfitSharePercent, (DateSerial(Year(Date()),12,31)-[StartDate])/7/52 AS YearsOfContribution, Last(SalaryInfo.AddlProfitShare) AS LastOfAddlProfitShare, IIf([YearsOfContribution]>1,[LastOfProfitSharePercent]*(DLookUp("[Profit]","RMDProfit","[YearEnding] = DateSerial(Year(Date()),12,31)")),[LastOfProfitSharePercent]*[YearsOfContribution]*(DLookUp("[Profit]","RMDProfit","[YearEnding] = DateSerial(Year(Date()),12,31)"))) AS BaseProfitShare
    FROM (TeamMembers LEFT JOIN ProfitShares ON TeamMembers.ID = ProfitShares.TeamMemberID) INNER JOIN SalaryInfo ON TeamMembers.ID = SalaryInfo.TeamMemberID
    GROUP BY TeamMembers.ID, TeamMembers.First, TeamMembers.Last, TeamMembers.StartDate, TeamMembers.ProfitSharingCategory, (DateSerial(Year(Date()),12,31)-[StartDate])/7/52, IIf([YearsOfContribution]>1,[LastOfProfitSharePercent]*(DLookUp("[Profit]","RMDProfit","[YearEnding] = DateSerial(Year(Date()),12,31)")),[LastOfProfitSharePercent]*[YearsOfContribution]*(DLookUp("[Profit]","RMDProfit","[YearEnding] = DateSerial(Year(Date()),12,31)")));

    I've been creating databases for about a year and thus far avoided getting heavily into querying (which I know is the most powerful feature). I want to make sure I'm doing things efficiently, right now I'm just doing what makes it work.

    Thanks so much for your insight!

  9. #9
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Ok, I've looked at it and here's the problem I have with Last. Last retrieves the last record for a group. For example:

    Record # MemberID amountpaid datepaid
    1 1 500 1/15/2013
    2 1 250 1/30/2013
    3 1 300 3/15/2013

    Odds are that when the query Select MemberID, Last(amountpaid) from thistable
    It will return 1 300

    However make a minor change
    Record # MemberID amountpaid datepaid
    1 1 500 1/15/2013
    2 1 250 4/30/2013
    3 1 300 3/15/2013

    Will net the same result
    1 and 300
    even though record 2 is later than record 3

    Since there is no sort you don't know with 100% certainty that the last record is the most recent record. Granted it could be 99% of the time but you don't know for sure. That's the problem I have with it. When I generate a report I want to know with 100% certainty it'll return the same data everytime and even with testing you don't know that with 100% certainty.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2013, 01:20 PM
  2. Replies: 3
    Last Post: 08-16-2012, 03:02 PM
  3. Replies: 13
    Last Post: 01-10-2012, 09:56 AM
  4. Parameter Query....
    By Casper2012 in forum Queries
    Replies: 5
    Last Post: 08-16-2011, 11:06 AM
  5. Parameter Query?
    By luckysarea in forum Queries
    Replies: 3
    Last Post: 04-22-2011, 12:57 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