A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Mutliple Tables lookup?



 
 
Thread Tools Display Modes
  #1  
Old June 13th, 2004, 02:53 AM
Westley
external usenet poster
 
Posts: n/a
Default 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  
Old June 13th, 2004, 04:48 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default 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  
Old June 13th, 2004, 05:23 AM
Kevin McBrearty
external usenet poster
 
Posts: n/a
Default 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  
Old June 15th, 2004, 12:55 AM
Ernie
external usenet poster
 
Posts: n/a
Default 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  
Old June 15th, 2004, 01:07 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.