|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
May 23rd, 2005, 02:18 PM
|
|||
|
|||
adding information/keeping history of user name and time stamp
Hello,
I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you! 
|
|
#2
May 23rd, 2005, 02:56 PM
|
|||
|
|||
|
Do you want to simply store the last person who modified the record or do
you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#3
May 23rd, 2005, 03:52 PM
|
|||
|
|||
|
Store every person who modified the record.
Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#4
May 23rd, 2005, 04:36 PM
|
|||
|
|||
|
Then you will need to add a history table that is linked one to many to
your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#5
May 23rd, 2005, 06:41 PM
|
|||
|
|||
|
problem i have is that the table don't have primary key because there are no
unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#6
May 23rd, 2005, 07:48 PM
|
|||
|
|||
|
Uh Oh! Not a good deal. One of the most important reasons for having a
relational database is to avoid duplicate data. No, an AutoNumber field won't work and, while it would allow you to create the link I discussed, it would not prevent the entry of duplicate invoice numbers and serial numbers. It would just hide their existence. If you need unique invoice numbers and serial numbers, then you will need to add something, like a unique index, on those fields to prevent that. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... problem i have is that the table don't have primary key because there are no unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#7
May 23rd, 2005, 08:42 PM
|
|||
|
|||
|
yes, it is so difficult designing this database because there are no unique
field. The system the team uses, you can key same invoice #, date, Amnt and Serial #'s. So when I pull a report and import into database, I can't set the primary key(s). What would you suggest? Should I just not have the history table added ? "Lynn Trapp" wrote: Uh Oh! Not a good deal. One of the most important reasons for having a relational database is to avoid duplicate data. No, an AutoNumber field won't work and, while it would allow you to create the link I discussed, it would not prevent the entry of duplicate invoice numbers and serial numbers. It would just hide their existence. If you need unique invoice numbers and serial numbers, then you will need to add something, like a unique index, on those fields to prevent that. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... problem i have is that the table don't have primary key because there are no unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#8
May 23rd, 2005, 08:48 PM
|
|||
|
|||
|
Well, you can add the UserName to your field in the AfterUpdate event of
your form AND immediately append the record to another table with the same structure -- a sort of archive table. This will effectively give you a record of every user who has updated every record in your original table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... yes, it is so difficult designing this database because there are no unique field. The system the team uses, you can key same invoice #, date, Amnt and Serial #'s. So when I pull a report and import into database, I can't set the primary key(s). What would you suggest? Should I just not have the history table added ? "Lynn Trapp" wrote: Uh Oh! Not a good deal. One of the most important reasons for having a relational database is to avoid duplicate data. No, an AutoNumber field won't work and, while it would allow you to create the link I discussed, it would not prevent the entry of duplicate invoice numbers and serial numbers. It would just hide their existence. If you need unique invoice numbers and serial numbers, then you will need to add something, like a unique index, on those fields to prevent that. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... problem i have is that the table don't have primary key because there are no unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#9
May 24th, 2005, 03:43 PM
|
|||
|
|||
|
Hi,
What code do i need to use to have it append only the record the user has touched? I have created a append query, but this will append all the records from the main table to history table. THank you! "Lynn Trapp" wrote: Well, you can add the UserName to your field in the AfterUpdate event of your form AND immediately append the record to another table with the same structure -- a sort of archive table. This will effectively give you a record of every user who has updated every record in your original table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... yes, it is so difficult designing this database because there are no unique field. The system the team uses, you can key same invoice #, date, Amnt and Serial #'s. So when I pull a report and import into database, I can't set the primary key(s). What would you suggest? Should I just not have the history table added ? "Lynn Trapp" wrote: Uh Oh! Not a good deal. One of the most important reasons for having a relational database is to avoid duplicate data. No, an AutoNumber field won't work and, while it would allow you to create the link I discussed, it would not prevent the entry of duplicate invoice numbers and serial numbers. It would just hide their existence. If you need unique invoice numbers and serial numbers, then you will need to add something, like a unique index, on those fields to prevent that. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... problem i have is that the table don't have primary key because there are no unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
#10
May 24th, 2005, 04:03 PM
|
|||
|
|||
|
Could you post the append query that you have?
-- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hi, What code do i need to use to have it append only the record the user has touched? I have created a append query, but this will append all the records from the main table to history table. THank you! "Lynn Trapp" wrote: Well, you can add the UserName to your field in the AfterUpdate event of your form AND immediately append the record to another table with the same structure -- a sort of archive table. This will effectively give you a record of every user who has updated every record in your original table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... yes, it is so difficult designing this database because there are no unique field. The system the team uses, you can key same invoice #, date, Amnt and Serial #'s. So when I pull a report and import into database, I can't set the primary key(s). What would you suggest? Should I just not have the history table added ? "Lynn Trapp" wrote: Uh Oh! Not a good deal. One of the most important reasons for having a relational database is to avoid duplicate data. No, an AutoNumber field won't work and, while it would allow you to create the link I discussed, it would not prevent the entry of duplicate invoice numbers and serial numbers. It would just hide their existence. If you need unique invoice numbers and serial numbers, then you will need to add something, like a unique index, on those fields to prevent that. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... problem i have is that the table don't have primary key because there are no unique characters. for example: there are duplicate invoices #'s and serial #'s. I couldn't make anything as primary key. Adding autonumber will now work because it will not be the same number in the history table, correct? Thank you! "Lynn Trapp" wrote: Then you will need to add a history table that is linked one to many to your main table. You can then set the value to CurrentUser in the AfterUpdate event of your data entry form. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Store every person who modified the record. Thank you for your help! "Lynn Trapp" wrote: Do you want to simply store the last person who modified the record or do you want to store every person who modifies the record? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "GEORGIA" wrote in message ... Hello, I was builiding a database for co-worker of mine. I have a form with drop down box with usernames, Status and when status changes to "Complete" it automatically time stamps. She wants to keep the history of who touched that record. Is there anyway to concatenate username, status and time stamp to another column name "History" and keep adding to that every time that record is touched by someone? For Example:. Invoice # Invoice Date Username Status Time STamp Hisotry 123 2/15/2005 JOE Complete 1:15pm Joe Complete 1:15 Smith Complete 2:35 Or can anyone recommend better solution for this? any help would appreciate it! Thank you!
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Wrong user reported as having a file already open | CPCA-IT | General Discussions | 1 | December 1st, 2004 08:33 PM |
| Columns and layout problems | Phil | Setting Up & Running Reports | 4 | August 18th, 2004 01:34 PM |
| New User needs to Import old Outlook Express emails and addresses | DougFromSydney | Contacts | 1 | June 17th, 2004 05:32 PM |
| How many tracked changes can excel history hold? | Fernando | Setting up and Configuration | 2 | June 1st, 2004 05:06 PM |
| not installed for current user | phil maida | Setting up and Configuration | 1 | May 27th, 2004 07:11 AM |
Linear Mode
