Match a max value in a row of fields with the row header

[moore020]

Hello,

I am new to this forum and somewhat new to access in general. I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value. Can this be done and if so, how difficult is it? My main background and knowledge is in excel.



Thanks,

Josh

[orange]

Can you give us a description in plain English about the Item and the Statuses through which it progresses?
We only know what you tell us about you and the issue/opportunity, so please tell us more.

[moore020]

It is less an item and more a service. Examples of the statuses it will go through are: Request for Quote, Holding for Quotes, Purchase Order Creation, In repair, and Complete. As you go through the process you would add a date to the appropriate status and the calculated field would update based on the date inputs. I will have several thousand records and each record will vary in status.

[orange]

You need tables and relationships to deal with the following and you'll have to fill in some details specific to your business.

Customer       Quote     PurchaseOrder     PurchaseOrderDetails    Service

If you keep any info about these things, then you'll probably use these tables.
Do you refer to this process as a Job or Project?
If so, you may just need

Customer  Job  JobStatus

[moore020]

I have created three tables and tried to link them through relationships. The main table is "Asset Status", the primary key is a project number. This is the table that includes the calculated asset status field I am wanting to populate. I refer to them as projects.

[orange]

You will learn a lot by working through this tutorial. The secret is to get your tables and relationships designed so that they support your business requirements. Too often people jump in to Access nitty gritty before designing their database, and it goes downhill from there.

[orange]

A Customer creates 1 or more Projects
A Project goes though many Statuses
A Project is in 1 and only 1 Status at any point in Time.

Customer --->Project----->ProjectStatus<----Status

[moore020]

I read through the tutorial and may be able to provide more information. We have project managers who must update the status (Created a Status Table) of a project or Asset (Created an Asset Table). I am trying to find a way that when they update the attributes of the status by adding a date to also update the status that is visible by higher level managers.

[orange]

What you are dealing with is what we call specification. You must identify all of the things involved. The clearer the description the better. The key is to get all of the specifications defined and accounted for and to adjust your design accordingly. The worst thing is to build something, that doesn't match the spec, hasn't been tested and then have to constantly create work arounds to do what should have been designed in. Spend the time to get your design correct.

Create sample data and some scenarios; test the scenarios against the model; reconcile every anomaly. Adjust until the model matches the requirement --it works. Now start development/design of interface/forms etc.

[moore020]

I am stepping away for the day, but will evaluate your recommendation and get back to you. Thank you for the help.

[moore020]

Okay Orange, I hope I can provide details that will help you to help me.

I am creating a database that tracks an item. The first thing that happens is an item is awarded. When the item is awarded it has specific information associated with it (i.e. part number, serial number, and description). This information would be input by me and be used to feed other tables. The next step starts to involve others in the tracking of the progress of the item through stages in a process which I previously outlined (26 different stages with a date that the stage is started and the user would also input comments at each stage and also in between stages). I am needing to create forms that will allow the user to look at one item at a time. I need to be able to create metrics based on how long each stage takes and how many items I have in each status each day (Example of this would be how many items do I have in each one of the stages right now and how has that number changed this week).

I hope this information is meaningful.

Thanks,

Josh

[orange]

Did you adjust your set up based on the tutorial? How about posting a jpg of your tables and relationships?