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
|
|||
|
|||
Mutliple Tables lookup?
I played around with the Table Analyzer Wizard. Is there a way to not use the wizard to split the database? I would like to have a few different tables.
Employees (EmployeeNumber, FirstName, LastName, Address, City, State, ZipCode, CellNumber, HomeNumber, BirthDate, HireDate, TerminationDate, Salary, ReferredBy, VacationDay1, VacationNote1, VacationDay2, VacationNote2, VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5, VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7, VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10, VacationNote10, MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM, WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM, Notes, DateCreated, UserCreated, Date Modified, User Modified, DateLeadsAcquired, LeadsAcquiredAM, LeadsAcquiredPM, LeadNotes) I would like the primary key to be EmployeeNumber on different tables. How would I do this? how would I create lookup tables? what happens to my original table? |
#2
|
|||
|
|||
Mutliple Tables lookup?
Having repeating groups of fields is not normalized. All of your Vacation
fields belong in a related table EmployeeNumber, VacationDate, VacationNote Your Monday etc fields should also be broken out with a more normalized structure. Once you have your new tables created, you can use append queries to append the records/values from your existing table. -- Duane Hookom MS Access MVP "Westley" wrote in message ... I played around with the Table Analyzer Wizard. Is there a way to not use the wizard to split the database? I would like to have a few different tables. Employees (EmployeeNumber, FirstName, LastName, Address, City, State, ZipCode, CellNumber, HomeNumber, BirthDate, HireDate, TerminationDate, Salary, ReferredBy, VacationDay1, VacationNote1, VacationDay2, VacationNote2, VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5, VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7, VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10, VacationNote10, MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM, WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM, Notes, DateCreated, UserCreated, Date Modified, User Modified, DateLeadsAcquired, LeadsAcquiredAM, LeadsAcquiredPM, LeadNotes) I would like the primary key to be EmployeeNumber on different tables. How would I do this? how would I create lookup tables? what happens to my original table? |
#3
|
|||
|
|||
Mutliple Tables lookup?
Westley,
I wasn't sure if you were talking about splitting the database or splitting tables, so I have covered them both below. Either way it will give you some helpful information. By your post it currently looks like you have everything in the employees table. This really does need to be split out by purpose. This is much easier to do if you don't currently have information in your tables. If you do have information in your tables it is still possible however it becomes a lot more complicated. First I am not entirely sure that you are familiar with the concept of Primary Keys and Foriegn Keys. Primary Keys are specific to a table, they make the record unique and give you a reference that you can refer to either in queries, reports, or whenever else you would need to refer to a specific record. Foriegn Keys allow you to create a relationship with other tables. They are ussualy the primary key in another table. I have split your current table out for you the way I believe it should be. Please note that this is just my interpretation of your field names and you may need to tweak it a little. Employee Table EmployeeNumber, FirstName, LastName, Address, City, State, ZipCode, CellNumber, HomeNumber, BirthDate, HireDate, TerminationDate, Salary Vacation Table Vacation Id(Probably use autonumber), Employee Number(Will refer to the employee table), Vacation Day, Vactation Note By splitting out the vacation information into another table and refering to the employee table you are not limiting yourself to ten vacation days and are eliminating possible blank fields for each employee record. Leads Table Leads ID(Probably use autonumber), Employee Number(again will refer to the employee table), LeadsAquiredDay(Select Day), LeadsAMorPM(Select AM or PM), LeadNotes Refered By Based on the information that you provided, I think this is the best table structure for you just add the DateCreated, UserCreated, Date Modified, User Modified to the tables you want to track this in. If you don't currently have any information in your tables just create the tables above and your all set. Otherwise, you will need to create some append queries and or copy information by hand depending on the situation. To be able to offer data entry I recommend using a form with a subform of the corresponding table that you want to work with. Eventually, you will want to delete your original table. I am not sure what the Table Analyzer does with your original table. I personally have never used it. I always do the work myself rather than letting access do it for me. There is actually a seperate wizard to split the database. It can be found by going to Tools -- Database Utilities -- Database Splitter. All the database splitter does is takes your tables and places them in another database (Back End Database). Then, in the current database it will place links to the tables in the back end database. In the current database, all of your forms, reports, pages, macros, and modules will stay where they are. All of this can be done manually by creating a blank database, and copying the tables to the new database. Once the tables are copied you will want to confirm that the tables are in the new database. After you have confirmed that the tables were copied over correctly you can delete the tables in the your current database and create Links to the Back End tables. To create Links to your tables go to File -- Get External Data -- Link tables. Spilting the database only really helps in situations where you want to be able to redesign the front end portion of the database while still allowing users to work with the current front end portion. The reason for this is because whenever you make changes to forms, reports, etc you have to have the database open exclusively, otherwise you may not be able to save your changes. By spliting the database you can make a copy of the front end portion of the database, make any changes to it, and then copy it over the current front end database when no one is using it. This will also allow you to fully test any changes you make and correct them without interfering with day to day operations. However if you are making changes that will need to be tested, I recommend that you redirect your linked tables to to a copy of your Back End Database so you don't corrupt your data. I hope this helps you. If you need more clarification on some of the items I talked about, or help with creating the forms, feel free to look for me on MSN Messenger. My e-mail address is . Thank you, Kevin McBrearty |
#4
|
|||
|
|||
Mutliple Tables lookup?
Save yourself a step. If you already have data in your
existing table and want to move/copy it to another table as described previously, just use a make-table query (or two). Create a query using your current table as the base and select only the fields that you want in your new table (employee number, vacation date, vacation note for example). Change this query to a make-table query and give your table a name. You can even create it in a new database from here. Run the query and you will have your new table the way you want it. The only problem with this is that access defaults the fields supplied in a make table query to 'text'. If one or more of your new fields are not text, you would have to modify the new table after you create it. Also re-running the make-table query will wipe out any existing table of the same name, including any changes made, so be careful. -----Original Message----- Having repeating groups of fields is not normalized. All of your Vacation fields belong in a related table EmployeeNumber, VacationDate, VacationNote Your Monday etc fields should also be broken out with a more normalized structure. Once you have your new tables created, you can use append queries to append the records/values from your existing table. -- Duane Hookom MS Access MVP "Westley" wrote in message ... I played around with the Table Analyzer Wizard. Is there a way to not use the wizard to split the database? I would like to have a few different tables. Employees (EmployeeNumber, FirstName, LastName, Address, City, State, ZipCode, CellNumber, HomeNumber, BirthDate, HireDate, TerminationDate, Salary, ReferredBy, VacationDay1, VacationNote1, VacationDay2, VacationNote2, VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5, VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7, VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10, VacationNote10, MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM, WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM, Notes, DateCreated, UserCreated, Date Modified, User Modified, DateLeadsAcquired, LeadsAcquiredAM, LeadsAcquiredPM, LeadNotes) I would like the primary key to be EmployeeNumber on different tables. How would I do this? how would I create lookup tables? what happens to my original table? . |
#5
|
|||
|
|||
Mutliple Tables lookup?
1) I hope you aren't suggesting that the existing table structure doesn't
require a major over-haul 2) Newly created fields in a "made" table will inherit field properties from the source table -- Duane Hookom MS Access MVP "Ernie" wrote in message ... Save yourself a step. If you already have data in your existing table and want to move/copy it to another table as described previously, just use a make-table query (or two). Create a query using your current table as the base and select only the fields that you want in your new table (employee number, vacation date, vacation note for example). Change this query to a make-table query and give your table a name. You can even create it in a new database from here. Run the query and you will have your new table the way you want it. The only problem with this is that access defaults the fields supplied in a make table query to 'text'. If one or more of your new fields are not text, you would have to modify the new table after you create it. Also re-running the make-table query will wipe out any existing table of the same name, including any changes made, so be careful. -----Original Message----- Having repeating groups of fields is not normalized. All of your Vacation fields belong in a related table EmployeeNumber, VacationDate, VacationNote Your Monday etc fields should also be broken out with a more normalized structure. Once you have your new tables created, you can use append queries to append the records/values from your existing table. -- Duane Hookom MS Access MVP "Westley" wrote in message ... I played around with the Table Analyzer Wizard. Is there a way to not use the wizard to split the database? I would like to have a few different tables. Employees (EmployeeNumber, FirstName, LastName, Address, City, State, ZipCode, CellNumber, HomeNumber, BirthDate, HireDate, TerminationDate, Salary, ReferredBy, VacationDay1, VacationNote1, VacationDay2, VacationNote2, VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5, VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7, VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10, VacationNote10, MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM, WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM, Notes, DateCreated, UserCreated, Date Modified, User Modified, DateLeadsAcquired, LeadsAcquiredAM, LeadsAcquiredPM, LeadNotes) I would like the primary key to be EmployeeNumber on different tables. How would I do this? how would I create lookup tables? what happens to my original table? . |
Thread Tools | |
Display Modes | |
|
|