Last Value in Fixed set of Table Columns

[xyba]

On a form I want to have a field which will display a value that is the last in a defined set of fields of a record in a table. I've attached a simple example db. I want to look across the four fields (Dept1 to Dept4) of each record and find the last value enteredand display that in the Department textbox of the form.

So in the example, for record 1 it will display "Board" and for record 2 "Legal".

Is this possible through any means in Access?

 

[plog]

You need to structure your table correctly. When you start numbering fields, its time for a new table. Data should be input vertically (with more rows) and not horizontally (with more columns)

Obviously this is just a small part of a larger database, correct? I mean you don't just have 1 table, called 'Sheet1' with 5 fields in it? Since you've pared this down I will have to use the tables/fields you've given me.

You need a new table, let's call it 'SheetDepartments'. That leaves Sheet1 with just an ID field and this should be the SheetDepartments table's structure:

SheetDepartments
sd_ID, autonumber primary key
ID_Sheet1, number, foreign key to Sheet1
sd_Dept, text, will hold the department value
sd_DeptNum, will hold the number portion that is currently in the field name (Dept1, Dept2, etc.)

Those 4 fields are all you need now. Then to get the 'last' department for each ID in Sheet1, you find the highest sd_DeptNum for an ID and get the sd_Dept value.

 

[xyba]

You need to structure your table correctly. When you start numbering fields, its time for a new table. Data should be input vertically (with more rows) and not horizontally (with more columns)

Obviously this is just a small part of a larger database, correct? I mean you don't just have 1 table, called 'Sheet1' with 5 fields in it? Since you've pared this down I will have to use the tables/fields you've given me.

You need a new table, let's call it 'SheetDepartments'. That leaves Sheet1 with just an ID field and this should be the SheetDepartments table's structure:

SheetDepartments
sd_ID, autonumber primary key
ID_Sheet1, number, foreign key to Sheet1
sd_Dept, text, will hold the department value
sd_DeptNum, will hold the number portion that is currently in the field name (Dept1, Dept2, etc.)

Those 4 fields are all you need now. Then to get the 'last' department for each ID in Sheet1, you find the highest sd_DeptNum for an ID and get the sd_Dept value.

Thanks for this reply.

Yes it is part of a larger database but for ease I made a smaller one to post here. The fields are only numbered by dept to track the movement of a document between departments so, for example, Dept1 is the originator, Dept2 is where it is next referred to. So each of those fields can have multiple values and separate records.

So would that change the layout as you've stated above?

 

[arnelgp]

SELECT Sheet1.ID, IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1)) AS Expr1
FROM Sheet1;

long table is better than wide table.

 

[plog]

So would that change the layout as you've stated above?

Not necessarily. You might consider making sd_DeptNum a date to track when the department received it. With that you could identify logjams in the process.

 

[xyba]

SELECT Sheet1.ID, IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1)) AS Expr1
FROM Sheet1;

long table is better than wide table.

This works perfectly, thank you. How, though, would I show the query result for a record in textbox on a split-form?

 

[arnelgp]

open your form in layout view. insert a textbox on your form, upper part (not the datasheet view).
on the control source of the new textbox:

=IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1))

be warned, that the textbox you added is not editabale.

 

[xyba]

open your form in layout view. insert a textbox on your form, upper part (not the datasheet view).
on the control source of the new textbox:

=IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1))

be warned, that the textbox you added is not editabale.

Worked perfectly, thank you

 

[Galaxiom]

So would that change the layout as [plog] stated above?

Heed plog's advice. Whenever you start comparing across multiple fields it means the data should be in one field.

Your current structure would continue to require clumsy workarounds. Consider the complexity of a query to list all the sheets that are current at a particular station.

With the proper structure the query is very simple and will return the data for all stations with a simple Group By Station.

 

[xyba]

open your form in layout view. insert a textbox on your form, upper part (not the datasheet view).
on the control source of the new textbox:

=IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1))

be warned, that the textbox you added is not editabale.

How can I use this IIf statement in a calculated table field? I've entered it, both with and without the first "=" but I get an error message ech time saying "cannot be used in a calculated field".