how to add new date/dates to a column when a field is updated in access table?

  • Thread starter Thread starter tektrader78
  • Start date Start date
T

tektrader78

Hello everyone,
This question has been asked in many ways but been unable to get a
straight forward answer from the groups.
Basically im building a crm database in access that requires 15 date
fields for each status of an account. In the "account" table i have a
column called status that has a combo box of 15 status' such as new,
sold, pending, canceled etc to name a few.

I also have columns in the "account" table that correspond to the
status like newdate, solddate, pendingdate etc
Is it possible in access and how can i have the date added to each of
the corresponding columns when the status is changed.
thanks

da
 
You will have to use the after-update event of the field in your form to set
the date
one way would be to have the corresponding date fields in your forms as
hidden fields and update the value of the fields of those boxes when the
user changes the combobox
 
Thanks John for your helpfull updates,
I will try that out and see. Im taking a crash course in making
relational databases at work so bear with me if certian things are
basic or i may have missed a point you tried to make. Hsalims option
of having after_update event seemed good and straigtforward but as u
said i would have a lot of fields.

Yours seems great but i lost you on the final explanation of creating
the new table for "accountdate". Let me re explain that i do not want
to overwrite the various date status' but have all the status and
their respective dates for each account in record so i can use a
reporting tool to know time spent per status.

In relation to the same question, I already have a table for customer
info, a table for info on the work done for the cust that gets updated
every few days based on new info received on the customer and the last
table is administrative information on the customer.
The last question is and hopefully that would finally be the key to my
problems is "what is the best way to tie them together". RIght now its
done manually by respective departments and i want to make them all
relational via primary keys and use a form with color code regions to
be filled by the different departments. Is that fine?

Thanks and remember im a noobie!

PS:
whats the best crash book to use on creating something like what i
have above.
 
Yours seems great but i lost you on the final explanation of creating
the new table for "accountdate". Let me re explain that i do not want
to overwrite the various date status' but have all the status and
their respective dates for each account in record so i can use a
reporting tool to know time spent per status.
Click to expand...

You are building your table "wide and flat" with fields like

Account Status1Date Status2Date Status3Date Status4Date Status5Date

and so on across. You're storing data - what kind of status applies -
in FIELDNAMES. This is what's non normalized! If you ever change the
list of status values, or if you want to search across all the fields,
your design makes it difficult.

What I'm suggesting is that you grow the table *vertically* instead of
*horizontally*. You would have TWO tables for the information that is
now in your one table. One would be your table of Accounts, with
account information (but no status or status date information); the
second table would have records like

AccountID Status StatusDate
123 New 7/13/2003
123 Pending 8/2/2003
123 Sold 8/14/2003

A Query based on this table can "slice and dice" the information
pretty much any way you wish; you can even create a Crosstab query to
restore your current "wide-flat" spreadsheet appearance if that's
useful for reporting. And it makes your original question of how to
update the status trivially easy; simply use Date() as the Default
property of StatusDate, and when you add a new record to the table it
will automatically fill in today's date.
 
John,
Thanks for your help. I implemented it the way you suggested and can
make reports using a cross tab query.
I however have a new issue. Can you or anyone in the group suggest how
to migrate or map data from a flat access table to a new access table
structure.

Eg:
Old database format:
| acct# | status | status1date | status2date | status3date |
status4date etc
0001 new 1/1/03 1/1/03 1/1/03 1/1/03
0002 old 1/1/03 1/1/03 1/1/03 1/1/03
0003 paid 1/1/03 1/1/03 1/1/03 1/1/03

the status in the old format merely represented the current status for
that account number and status1 thru status4 where various dates that
indicate the new status of the acct number.

new databse format:

| acct# | status | statusdate
0001 new 1/1/03
0001 paid 1/1/03
0001 pending 1/1/03
0001 invoice 1/1/03

0002 new 1/1/03
0002 paid 1/1/03
0002 pending 1/1/03
0002 invoice 1/1/03

A modfunction was created to add blank dates and all 4 status to each
account number. The problem now is how to migrate the various status
dates from the old table format to the new table format.

Thanks
 
Back
Top