Calculated field that references row below

[Access_Novice]

In an Excel spreadsheet, in a column I have the following formula:

=IF(AND(P2="C",N2=N3),"Delete","")

This formula references cells on the same row, and on the row below it. I would like to do the same thing in a query in a calculated field. How would you reference the row below any given row?

[John_G]

Hi -

Access can't do that in a query, and a form would require some tricky VBA code. For the most part, Access can "see" and reference only the current record. While it can display data in what looks like a spreadsheet, it has nowhere near the capabilities that Excel has to work with it.

[Access_Novice]

Hi -

Access can't do that in a query, and a form would require some tricky VBA code. For the most part, Access can "see" and reference only the current record. While it can display data in what looks like a spreadsheet, it has nowhere near the capabilities that Excel has to work with it.

Ok. Thank you for your reply. What about this:

I have the following fields: name, PONumber,weekEndingDate, adjNum,regHours and overtimeHours. These are timesheets so any given day can have a correction, or even two corrections. Because of this, it is possible that for any given person, a date might appear two or three times.

The adjNum column is 0 for the initial entry, 1 for the first correcting entry, 2 for the second correcting entry, etc. If there is a correction for any given person on any given day, then I need to keep ONLY the most recent entry (adjNum = 3, then 2, then 1, then 0) and filter out the rest.

I created a calculated column which simply adds the WeekEndingDate and the adjNum. So now, all I have to do is this:

Within each person for any given weekendingDate (whether it repeats due to a correction or not), show only the largest date in the calculated column. So if week ending 10/20/2013 doesn't have any corrections, the max value (and in this case the only value) in the calculated column would be 10/20/2013. So keep that row.

Then on the next three rows for the same person, weekending 10/27/2013 shows up 3 times (the original entry, adjNum = 0, and adjNum =1 and 2 for two correcting entries). So for weekending 10/27/2013, the largest value in the calculated column would be 10/29/2013, therefore I would keep that row and the other two rows for weekending 10/27/2013 would be filtered out.

Do you have any suggestions on how to approach this? I tried "Group by" then entered 'max' on the criteria column under the calculated field in query design and I still see 3 instances of weekEndig 10/27/2013.

[NoellaG]

Try 2 queries: 1 groupsquery that gets the POnumber (I guess that's the personal number unique for each employee) and the max date and link it on POnumber and date field to the query giving you already constructed.

[Access_Novice]

Try 2 queries: 1 groupsquery that gets the POnumber (I guess that's the personal number unique for each employee) and the max date and link it on POnumber and date field to the query giving you already constructed.

POnumber is not a unique identifier in my query called qryBillingHistory2. This query does not have any unique identifiers. Hopefully that's ok. I came up with an idea. I am concatenating name & weekending Date, then applying Group By to all columns and a 'max' to the column called adjNum. This worked a bit in that the max adjNum for any name+weekending combination did come up, however the original entry (where adjNum = 0) also came up. It's as if Access doesn't recognize the zero.

Any idea what I did wrong?

[June7]

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[Access_Novice]

Try 2 queries: 1 groupsquery that gets the POnumber (I guess that's the personal number unique for each employee) and the max date and link it on POnumber and date field to the query giving you already constructed.

I meant to delete my last post. I should have written this:

Try 2 queries: 1 groupsquery that gets the POnumber (I guess that's the personal number unique for each employee) and the max date and link it on POnumber and date field to the query giving you already constructed.

POnumber is not a unique identifier in my query called qryBillingHistory2. This query does not have any unique identifiers. Hopefully that's ok. As an example in my calculated field I have:

JamesDoe11/03/20130
JamesDoe11/03/20131
JamesDoe11/03/20132
JamesDoe11/03/20133

If I click the Sum icon and do Group by on every column, and select 'max' on the calculated field with the values above, I should get that last value. But all four values still appear. Is this because all the values are text and not numbers? I thought that the 'max' function can still be used on text values (because each letter is assigned a value).

[NoellaG]

Hi,

I included an example of solving it in 2 queries: a totals query linked to the original query, hope this makes things clearer.

[Access_Novice]

Hi,

I included an example of solving it in 2 queries: a totals query linked to the original query, hope this makes things clearer.

Awesome. I think that will give me what I am looking for. Just two things.

1. In the final query I need to show weekending date.
2. You linked the first query to the table. How did you do that?

Your reply was very helpful. A great way to start off a day. Thank you.

[NoellaG]

Hi,

1. I think the field WeekendingDate is included in my example query qryBilling, isn't it?
2. In the top section of the query by example window you can add tables and queries and link them by dragging the linking fields over each other, just as in the relationship window. relations created in a query window only exist in that query. I included an avi video to show how I created the query. You can link to queries in the same way.

[Access_Novice]

Hi,

1. I think the field WeekendingDate is included in my example query qryBilling, isn't it?
2. In the top section of the query by example window you can add tables and queries and link them by dragging the linking fields over each other, just as in the relationship window. relations created in a query window only exist in that query. I included an avi video to show how I created the query. You can link to queries in the same way.

1. I don't know what I was looking at but yes, you did include WeekEndingDate.
2. Thank you for taking the time to create the AVI video file. I think it's a very good idea. When you linked the query to the table, you created a double join. Just to be sure, why did you use a double join?

[Access_Novice]

Here is what I have as of now. I want to group by each person & weekendingDate combination, so first I created a calculated field to concatenate Person & weekendingDate. I did Group By to all columns except for a 'max' on the adjNum column. For any name&weekEndingDate combination, I did not get only the maximum adjNum. I still got all of them, i.e. 0,1,2,3 in one example. So the name&WeekEndingDate repeated 4 times in this case.

I remembered that the adjNum field in the underlying table was set to short text so maybe that is why max is not working on adjNum. So I tried to convert adjNum to a number and that didn't work. I tried two ways of converting:

I went to the adjNum field in query design. I put the adjNum inside of val( ) and that didn't work. Then I figured maybe you can't apply a function directly to a field that has been added to the design grid. Is this true? Thinking that this might be true, I created another calculated column called convAdjNum and put that in the Val( ). So the calculated field looked like this: convAdjNum: Val([adjNum]). I set this field to 'max' with all the others to Group By. This didn't work either.

I think my problem has something to do with the fact that adjNum is designated as a short text data type in the underlying table. Any ideas?

[Access_Novice]

So anyway I am concatenating name+weekending+adjNum. This is fine. Although this is a string, I should still be able to apply the MAX function to it because the Max function is supposed to take the characters and convert them to numbers. But for some reason, this isn't working. Here is my calculated field:

concat: Max([lastName] & [weekEnding] & [adjNum])

[Access_Novice]

Ok, I found out why I'm not getting the expected results. I should have mentioned that there were some other columns called Hours and OTHours. I didn't mention these because I didn't think they mattered but they appear to be the problem. So the max on the concat field did work some what. Two of the original values are gone, but two remain. Here is what I see:

Smith 3/17/2014 2 40 0 Smith3/17/2014
Smith 3/17/2014 3 40 5 Smith3/17/2014

The 2 and 3 are in the column called adjNum. I presume that the 2nd row is not being taken as the max value because of the 5 OT hours which makes these two different rows as far as Access is concerned. Other situations may be reversed, e.g. the lower adjNum might have the OT hours and the higher adjNum may not have OT in which case, the first row would be the max row.

So I have to find a way to tell Access to disregard the values in the Hours and OThours columns, then take the max of the adjNum columns. I do need to keep the Hours and OThours columns.

Any ideas?

[June7]

Yes, can use functions with fields in query, example:

SELECT *, Val([fieldname]) AS FieldConverted FROM tablename;

However, Val() and all number conversion functions will error on Null.

What is adjNum? Why is it a text field? Why couldn't you change the field type, what happened?

Options may be:

1. nested SQL - Did you explore the link referenced in post 6?

2. separate query objects - do a query to get the max or min for the desired grouping then join that query to table or another query with the other fields (Hours and OTHours) you want

3. domain aggregate function