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
|
|||
|
|||
Can a field store a date and/or any other data type?
I'm using Excel to keep data on students
I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#2
|
|||
|
|||
Can a field store a date and/or any other data type?
hi,
JethroUK wrote: I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? You need to store that in two fields: Started: DateTime or Yes/No Field. Completed: DateTime mfG -- stefan -- |
#3
|
|||
|
|||
Can a field store a date and/or any other data type?
No. Unlike columns in a spreadsheet, fields in a database are properly
typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#4
|
|||
|
|||
Can a field store a date and/or any other data type?
Then is it possible to assign a date type field as 'not null'? - which could
in turn be described on a form/report as 'started' (but not yet completed) I am currently describing over 12 activities with 12 fields/columns and it is likely (once i have database set up i will go on to describe/track 20-30 activities) so i don't really want several fields per activity to maintain, if one date field will describe it "Allen Browne" wrote in message ... No. Unlike columns in a spreadsheet, fields in a database are properly typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#5
|
|||
|
|||
Can a field store a date and/or any other data type?
Step away from the keyboard! You are trying to "commit spreadsheet". Having
12 fields with similar data with the possibility of going to 30 fields is a very bad idea. Your table has serious normalization problems and just will NOT work correctly in a relational database. Period. I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JethroUK©" wrote: Then is it possible to assign a date type field as 'not null'? - which could in turn be described on a form/report as 'started' (but not yet completed) I am currently describing over 12 activities with 12 fields/columns and it is likely (once i have database set up i will go on to describe/track 20-30 activities) so i don't really want several fields per activity to maintain, if one date field will describe it "Allen Browne" wrote in message ... No. Unlike columns in a spreadsheet, fields in a database are properly typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#6
|
|||
|
|||
Can a field store a date and/or any other data type?
my problem is only small (as described), but even with your vast knowledge
you don't seem to see a simple solution so I don't think reading a book would help me at all i think i'll just have to use two fields to describe start & end every single activity "Jerry Whittle" wrote in message ... Step away from the keyboard! You are trying to "commit spreadsheet". Having 12 fields with similar data with the possibility of going to 30 fields is a very bad idea. Your table has serious normalization problems and just will NOT work correctly in a relational database. Period. I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JethroUK©" wrote: Then is it possible to assign a date type field as 'not null'? - which could in turn be described on a form/report as 'started' (but not yet completed) I am currently describing over 12 activities with 12 fields/columns and it is likely (once i have database set up i will go on to describe/track 20-30 activities) so i don't really want several fields per activity to maintain, if one date field will describe it "Allen Browne" wrote in message ... No. Unlike columns in a spreadsheet, fields in a database are properly typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#7
|
|||
|
|||
Can a field store a date and/or any other data type?
Jethro, everyone here is trying to help you. The problem is you are trying
to shoehorn a spreadsheet solution into a relational database. If you continue on your current path, it will only get harder. You probably would be better off staying in Excel. To be specific, You really don't need the 30 fields for one student. What you need is a table that identifies the students, one to identify the activities, and another to show the current status of an activity for a student. So to keep it as simple as possible (you may need more info in your tables) tblStudent StudentID - Autonumber PK StudentFirstName - Text StudentLastName - Text tblActivity ActivityID - Autonumber PK ActivityDescription - Text tblStudentActivity StuActID - Autonumber PK StudentID - Long Integer - Foreign Key to tblStudent ActivityID - Long Integer - Foreign Key to tblActivity ActivityStartDate - Date ActivityCompleteDate - Date Now, you use a record in the tblStudentActivity table for each Activity for Each Student. This is a very basic relational design. If you have any questions on this, please post back. -- Dave Hargis, Microsoft Access MVP "JethroUK©" wrote: my problem is only small (as described), but even with your vast knowledge you don't seem to see a simple solution so I don't think reading a book would help me at all i think i'll just have to use two fields to describe start & end every single activity "Jerry Whittle" wrote in message ... Step away from the keyboard! You are trying to "commit spreadsheet". Having 12 fields with similar data with the possibility of going to 30 fields is a very bad idea. Your table has serious normalization problems and just will NOT work correctly in a relational database. Period. I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JethroUK©" wrote: Then is it possible to assign a date type field as 'not null'? - which could in turn be described on a form/report as 'started' (but not yet completed) I am currently describing over 12 activities with 12 fields/columns and it is likely (once i have database set up i will go on to describe/track 20-30 activities) so i don't really want several fields per activity to maintain, if one date field will describe it "Allen Browne" wrote in message ... No. Unlike columns in a spreadsheet, fields in a database are properly typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#8
|
|||
|
|||
Can a field store a date and/or any other data type?
"Klatuu" wrote in message
... Jethro, everyone here is trying to help you. The problem is you are trying to shoehorn a spreadsheet solution into a relational database. If you continue on your current path, it will only get harder. You probably would be better off staying in Excel. Jethro, please hear that. Don't frustrate yourself with your current path. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#9
|
|||
|
|||
Can a field store a date and/or any other data type?
I can see the benefit of having a separate table for student activities
tblStudentActivity StuActID - Autonumber PK StudentID - Long Integer - Foreign Key to tblStudent ActivityID - Long Integer - Foreign Key to tblActivity ActivityStartDate - Date ActivityCompleteDate - Date but i'm struggling to see the benefit of a separate table for activity description (assuming it's as unique as the activity) tblActivity ActivityID - Autonumber PK ActivityDescription - Text wouldn't it be easier to put both in same table: tblActivity StuActID - Autonumber PK StudentID - Long Integer - Foreign Key to tblStudent ActivityDescription - Text ActivityStartDate - Date ActivityCompleteDate - Date "Klatuu" wrote in message ... Jethro, everyone here is trying to help you. The problem is you are trying to shoehorn a spreadsheet solution into a relational database. If you continue on your current path, it will only get harder. You probably would be better off staying in Excel. To be specific, You really don't need the 30 fields for one student. What you need is a table that identifies the students, one to identify the activities, and another to show the current status of an activity for a student. So to keep it as simple as possible (you may need more info in your tables) tblStudent StudentID - Autonumber PK StudentFirstName - Text StudentLastName - Text tblActivity ActivityID - Autonumber PK ActivityDescription - Text tblStudentActivity StuActID - Autonumber PK StudentID - Long Integer - Foreign Key to tblStudent ActivityID - Long Integer - Foreign Key to tblActivity ActivityStartDate - Date ActivityCompleteDate - Date Now, you use a record in the tblStudentActivity table for each Activity for Each Student. This is a very basic relational design. If you have any questions on this, please post back. -- Dave Hargis, Microsoft Access MVP "JethroUK©" wrote: my problem is only small (as described), but even with your vast knowledge you don't seem to see a simple solution so I don't think reading a book would help me at all i think i'll just have to use two fields to describe start & end every single activity "Jerry Whittle" wrote in message ... Step away from the keyboard! You are trying to "commit spreadsheet". Having 12 fields with similar data with the possibility of going to 30 fields is a very bad idea. Your table has serious normalization problems and just will NOT work correctly in a relational database. Period. I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JethroUK©" wrote: Then is it possible to assign a date type field as 'not null'? - which could in turn be described on a form/report as 'started' (but not yet completed) I am currently describing over 12 activities with 12 fields/columns and it is likely (once i have database set up i will go on to describe/track 20-30 activities) so i don't really want several fields per activity to maintain, if one date field will describe it "Allen Browne" wrote in message ... No. Unlike columns in a spreadsheet, fields in a database are properly typed, so you cannot store a non-numeric value in a Number field, or a non-date value in a date/time field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JethroUK" wrote in message ... I'm using Excel to keep data on students I'm currently describing the state of any particular field as either '*' = student has started this part of their programme - or a date "15/7/08" to describe their end/completion date for that same part of the programme I want to import all this data into Access but can a field store a date and/or any other data type as per Excel? |
#10
|
|||
|
|||
Can a field store a date and/or any other data type?
"JethroUK©" wrote in message
... but i'm struggling to see the benefit of a separate table for activity description (assuming it's as unique as the activity) It depends how unique the activities are. "Go to study hall" may be a common activity, but "read page 997 of War and Peace" may be a rare activity. If you do not have a finite number of unique activites, then the combined table makes sense. The other approach to look at it is if the two tables end up being an almost one to one relationship then a single table makes sense. If there is a many to one relationship on activity then the seperate tables make sense. John... Visio MVP |
|
Thread Tools | |
Display Modes | |
|
|