Query Asking for Parameter

[rts]

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!

[RayMilhon]

Quick question are you using the formula in the same query?

[ssanfu]

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.......

[rts]

Yes, the formula is in the same query.

[RayMilhon]

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.

[rts]

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!

[RayMilhon]

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.

[rts]

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!

[RayMilhon]

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.