I'm not sure what you mean by automatically format. I suppose one could create a macro to add to a form so that when a new author is added, the input mask would place a comma and a space after the entry of the last name and before the entry of the first name. I'll look to see if it can be done, but I hadn't planned on doing it for this task. One might ask oneself why I have put both last and first names into a single field. The answer for me is that a name is the combination of the two - a first name by itself has no particular validity as a data element, at least in my view. But I could be persuaded otherwise.
2. in all of the tables, can we get rid of the first ID column. If we do, should the book ID, publisher ID etc then be an auto number ?
If you use the import wizard and allow Access to create its own Primary Key, it will place that Primary Key as the first ID column. Since the Primary Key that Access will have created is the same as the ID number that existed in the imported table, you should NOT delete the first column; you should delete the second column, which has become redundant. Because you imported a column of numbers, it would be difficult to have Access change the data type from number to Autonumber and you would thus have to manually enter each new ID number. By keeping the Access column with the autonumber and deleting the second ID column, you can add an indefinite number of new records without having to also add a new ID for each new record.
3. is the copyright year field property supposed to be a long integer?
Yes. There may be reasons why one would want to have one of the other field sizes for the number data type, but for simplicity's sake, let's stick to long integers for all number types.
4. for the columns that will be used to link to other tables, do you label those as Foreign key (and likewise, yes, duplicates ok)? [ie for tblBook_PublisherID, tblBookAuthor_BookID, and tblBookAuthor_AuthorID?]
Yes. One can use any naming schema that works, but in principle, it's a good idea to name things for what they are and, where possible, have the name identify the source of the field if it's a Foreign Key. And all foreign keys must be a number data type (never an autonumber) and must be set for duplicates OK, never "no duplicates". If one leaves the FK as "no duplicates", one will be forced into a one-to-one relationship and all our relationships in this database should be one-to-many relationships (or "no duplicates"-to-"duplicates OK".
5. in the order table, how do we set the default value for the date as "current date"? I tried doing the default setting and it caused an error.
Default values require one to create an expression using the expression builder which is called by clicking on the three dot icon that appears when the default value line is selected. Since, in this case, the expression relies on a built-in function, one needs to use the expression builder dialog box to drill down to the built-in functions and find the correct one. If you get an error after doing this, I suspect there is a mismatch between the data type and the function you are trying to use. In this case, you need to have a date data type and you should use a date/time function.
6. Likewise in the order table, do we need to make a validation rule that the wholesale price can not be more than 80% of the retail price (I tried using the expression builder but it was not working for me.)
No. It is much easier to create a tool in the form for this purpose since the user will enter data into the tables via a form. So, for now, just create the field in the Orders table to receive the data and we will fill in the required data by creating a form for that purpose on Thursday.
4. for the columns that will be used to link to other tables, do you label those as Foreign key (and likewise, yes, duplicates ok)? [ie for tblBook_PublisherID, tblBookAuthor_BookID, and tblBookAuthor_AuthorID?]
Yes. One can use any naming schema that works, but in principle, it's a good idea to name things for what they are and, where possible, have the name identify the source of the field if it's a Foreign Key. And all foreign keys must be a number data type (never an autonumber) and must be set for duplicates OK, never "no duplicates". If one leaves the FK as "no duplicates", one will be forced into a one-to-one relationship and all our relationships in this database should be one-to-many relationships (or "no duplicates"-to-"duplicates OK".
5. in the order table, how do we set the default value for the date as "current date"? I tried doing the default setting and it caused an error.
Default values require one to create an expression using the expression builder which is called by clicking on the three dot icon that appears when the default value line is selected. Since, in this case, the expression relies on a built-in function, one needs to use the expression builder dialog box to drill down to the built-in functions and find the correct one. If you get an error after doing this, I suspect there is a mismatch between the data type and the function you are trying to use. In this case, you need to have a date data type and you should use a date/time function.
6. Likewise in the order table, do we need to make a validation rule that the wholesale price can not be more than 80% of the retail price (I tried using the expression builder but it was not working for me.)
No. It is much easier to create a tool in the form for this purpose since the user will enter data into the tables via a form. So, for now, just create the field in the Orders table to receive the data and we will fill in the required data by creating a form for that purpose on Thursday.
0 comments:
Post a Comment