Update a field based on other fields

[Mattcornish]

Hi guys

My apologies first off as I know this has been covered in other threads but not in the lamens terms I need (my boss asked for a database a week ago and that's the first time I have used one since about 2001).

I have a table with about 10 or so dates in. These dates would be entered sequentially as and when each task for the project is completed.
The last field is "status", I need this to look at the last date and if it's >"" to return "live" if it is blank then to look at the next one along and so on and so forth till it finds the correct status.

I am used to using excel so I would use an if statement normally but don't have a clue wat to do in access.

Hope u guys can help

 

[jdraw]

If we're going to help, you're going to have to tell us more.

Table name, field names etc. Application description...
The key to a useful database is to have proper tables and relationships.

Do you have knowledge of Normalization?

 

[Mattcornish]

Ok, I only have 1 table (well there are more but they only contain lookup values) "qualdetails" with fields "sp started", "sp created", "lp created" and so on all set as dates till it gets to the last field "status" (currently blank).

I dont know wat normalisation is and as there arnt any relationships other than lookups I hav tried to keep it simple

Hope this helps

 

[Mattcornish]

Also using access 2007 if that's of any use

 

[jdraw]

I don't have acc2007, only 2003.
Can you show the table structure? Are you designing a new database?
You'll be working from a Form right? You shouldn't interact directly with tables.

 

[Mattcornish]

Yes i am workin from a form. Due to the nature of the information I am dealing with I can't post the structure but I will do my best to describe it.

"project name","date 1", "date 2", "date 3", "status"

This is stripped down to the basics there are other fields but they are only to track assigned administrators and project definition details. None of these are calculated or anything just there for reference to the people defining the product.

Wat I am after is a constantly updated field "status" where if "date 1" is not blank then the current status is "product defined" if "date 2" is not blank then it's at "advanced testing"

If this doesn't help let me know and I'll create a duplicate structure with dummy data and post it here

 

[jdraw]

First off, you should at least learn/read about Normalization.

I'd suggest a table as follows for you ProjectStaus info

tblProjectStatus
Id autonumber PK
ProjectId Number FK to your Project table
StatusAssignedDate Date
StatusId Number FK to your tblStatus

This would give you tables

Project (whatever that is, but I think you have it)
tblStatusProject
tblStatus ( a lookup table with ID and StatusName which allows you to add more statuses as needed)

The tblStatusProject would have records like

1 (Project1), 23/3/2011(StatusAssignedDate),1 (the id of the Status "product defined"
1 (Project1), 17/5/2011(StatusAssignedDate),2 (the id of the Status "advanced testing"

You can find the current status for a project by looking at the Status where the StatusAssignedDate =Max(StatusAssignedDate) for the Project

Normalization reference
The first 3 topics here http://www.rogersaccesslibrary.com/forum/topic238.html