Buttons to open Forms

[MarieLine77]

Hello there I am new here and to Access 2007.

I am creating a database with several tables and the main ones would be about
- Employee Database
- Resident Database

Right now I am creating all my tables and I will have several tables with information relating to either Employee or Residents and another later about reports which will also involve pulling information from both employees and residents tables...

*** MY QUESTION:
What I am a little confused about is that my friend has asked me to give her the option (as she opens the database) from two big buttons
EMPLOYEES
RESIDENTS
And I do not know who to do this.


For example:

SCREEN1: As she open the database, she clicks on the EMPLOYEE button
SCREEN2:
a) She sees all the main info about the employees i.e. name, DOB, etc...
b) If she pages Up and Down she sees all the employees scrolling up/down
c) Including a search button feature
b) At the bottom of that same form she wants to see 10 buttons related to other tables with further info about the employees.


SCREEN1: As she open the database, she clicks on the RESIDENTS button
SCREEN2:
a) She sees all the main info about the residents i.e. name, DOB, etc...
b) If she pages Up and Down she sees all the employees scrolling up/down
c) Including a search button feature
b) At the bottom of that same form she wants to see 10 buttons related to other tables with further info about the residents.

I would appreciate it if any advice you give me is not too complex as I am a beginner.

Many thanks.

 

[MarieLine77]

I have been asked to elaborate in simple words so I am going to try.

I have a database
When I open it I want to see TWO BUTTONS on a Form (like a login screen)
-Employees
-Residents

 

[Pat Hartman]

The "Switchboard Manager" is a tool included in A2007 and earlier that builds the kind of form you are looking for. I think it is located on the Database Tools ribbon in A2007.

FYI - People are people and you should have only a single table for people. You would add a flag to the table to indicate which people are employees and another to indicate which people are residents. Then to populate the form, you would use a query that selects all people with the employee flag = True or the resident flag = True depending on which set of people you want to display.
In the long run, you will have less work with a single table and single form were you use criteria to control the records shown than by maintaining two of everything.

 

[MarieLine77]

Thank you Pat Hartman.

I have played with the Switchboard and it is exactly what I need

I have attached what I have done so far on my database.

I could try to put all the employees and residents in the same table but I do not know what "flag" is?

Would appreciate it if you can spot my mistake on the ATTACHED print screen or think there is an easy way of making this database simpler.

Once again thank you for any assistance that you may be able to give me.

 

[MarieLine77]

Bump! Can anyone help by checking the attachment above for me?
Please feel free to ask me any questions and if it is wrong I will have no problem restarting the database all over again.

Thank you kindly.

 

[MarieLine77]

OK is this any better...

 

[Pat Hartman]

The Reports table should include only
ReportID, ReportDate, EmployeeID, and ResidentID. The employee and resident names are defined in their respective tables and should not be duplicated here.
Primary keys should have names that reflect the table they are in. I would Use EmployeeID, ReportID, and ResidentID.
Best practice is to use only a-z, A-Z, 0-9 and the underscore to form table, column and other object names. No programming language supports variable names that include spaces or special characters. You will save yourself a lot of aggrivation and typing if you fix your names now. My preference is to use CamelCase to make names readable but others prefer the_underscore.
I'm pretty good with understanding schemas but some of your names are leading me to believe that they belong in separate tables. For example, I'm sure there could be more than one complaint or compliment for an individual. That means that they should be stored in separate tables. I would call it Comments and it would contain CommentID, CommentType(complaint, complement, etc), CommentDate, CommentText; SicknessRecord, DisciplinaryRecord, SignificantOccurances, ContractNotes, MeetingAttendance all look like they should be moved to separate tables.
The Reports table seems to be the junction table and so it has the PKs of both tables it joins (Employees and Residents). ResidentID and ReportID do not belong in the Employees tables and EmployeeID and ReportID do not belong in the Residents table.
Apply the same logic to the Residents table and move out the data fields that occur multiple times to separate tables.

 

[MarieLine77]

The Reports table should include only
ReportID, ReportDate, EmployeeID, and ResidentID. The employee and resident names are defined in their respective tables and should not be duplicated here.
Primary keys should have names that reflect the table they are in. I would Use EmployeeID, ReportID, and ResidentID.
Best practice is to use only a-z, A-Z, 0-9 and the underscore to form table, column and other object names.

Thank you so much Pat Hartman.
I am going to start all over again slowly and following your advice.
Could you confirm if I should change the 3 PK from AUTONUMBER to text and numbers and type them in manually? i.e.
a-z for ResidentID
A-Z for EmployeeID
0-9 for ReportID

Thank you.

 

[MarieLine77]

For example, I'm sure there could be more than one complaint or compliment for an individual. That means that they should be stored in separate tables. I would call it Comments and it would contain CommentID, CommentType(complaint, complement, etc), CommentDate, CommentText

Can this comment table be used for both Employees and Residents?

 

[PNGBill]

If you have a Alpha style primary key that defines Resident or Employee then a query can tell you what records belong to residents and or employees - so yes, one table.

 

[PNGBill]

Your tblComments will also have a field eg IndividualID which is where you will store the PrimaryKey for the person (employee or Resident).
Without this field, you would have no way to know which comment belongs to who.

 

[MarieLine77]

Please find attached amendments made so far.

PRIMARY KEY QUESTION:
Can I leave ReportsID on AutoNumber?
How about all the other tables that I am going to have to create as advise above. Can all the PK be left on AutoNumber or will this cause confusions?

Re the tblReports and tblComment, which will be better:
Use both ResidentID and EmployeeID
(I will type in the PK Manually for Residents a-z and Employees A-Z)
or the IndividualID?

Thank you.

 

[PNGBill]

I wouldn't change the Primary Key.
An earlier post from Pat suggested a Flag to identify the record as being Resident or Employee.

This could be Yes or No - not recommended or 1 or 2 ie single Integer field. 1 is known as Resident and 2 is known as Employee or you could have a text field with 3 chrs - Res or Emp.

In tblComment you would a field IndividualType and this would hold the 1 or 2, Res or Emp.
This means for every record in tblComment, you have two fields. One is the Primary Key of the individual and the other is the Type.

I would consider not have two tables for Individuals. This would resolve any issue on which table the comment record is matched to.
You just one field to tblIndvidual and that is the IndividualType as above.

tblComment just needs the tblIndividualID as a field and all can be traced by queries.

eg tblCustomers cover all customers and some field will define if they are Retail, Trade etc.

 

[MarieLine77]

If you have a Alpha style primary key that defines Resident or Employee then a query can tell you what records belong to residents and or employees - so yes, one table.

Can I still run this query if I have an autonumber PK?

 

[PNGBill]

A Primary Key is a Unique Name/Reference. It can be an AutoNumber or even Text but it must be Unique. It can also be a combination of two or more fields.

A Query will just process the records with the fields you supply to it. The query does not need a Primary Key although, most would have as the need to have a way to identify the record is common.

Pupils in a Class all have a FirstName and LastName.
There may be two John's and two Smith's but seldom two John Smiths.
This could be an example of using FirstName and LastName as joint Primary Keys. In practice, there could well be two John Smith's in the school so you would likely use an AutoNumber.

 

[Pat Hartman]

I wasn't suggesting that you change the primary keys from autonumber to text. I was suggesting that you make their names unique so they wouldn't all be called "ID". That way, you can look at a table and when you see a field that ends in "ID", you "know" it is a foreign key and you have a pretty good idea which table it points to. I work with applications that have dozens, even hundreds, of tables and using a coherent naming standard means the I don't have to keep things like this in my little pea brain. I can work them out on the fly. As we get older, we loose brain cells at an alarming rate and anything we can do to minimize the time we need to spend thinking about something is time well spent.

I also suggested that you change all your column names so they conform to professional standards by removing all spaces and special characters. Once you get to the point where you want to do something in VBA, you'll see what a hassle these "comfort" characters can be. you'll also see that VBA has its own defense mechinism against them. In some cases, it forces you to enclose the offending names in square brackets []. In other cases, it takes it upon itself to actually replace the offending characters with underscores. So, in the unlikely case you had two names - "Account $" and "Account %" which have both spaces and punctuation characters but are otherwise identical, and you wanted to add event code to these columns, Access would upchuck because VBA would want to change both fields to the same value "Account__".

 

[MarieLine77]

I wasn't suggesting that you change the primary keys from autonumber to text.

Yes, I understand that now and it was entirely my mistake

 

[MarieLine77]

I was suggesting that you make their names unique so they wouldn't all be called "ID". That way, you can look at a table and when you see a field that ends in "ID", you "know" it is a foreign key and you have a pretty good idea which table it points to.

I also suggested that you change all your column names so they conform to professional standards by removing all spaces and special characters.

Working on both the above right now but I do go slowly so bare with me.

 

[MarieLine77]

I was suggesting that you make their names unique so they wouldn't all be called "ID". That way, you can look at a table and when you see a field that ends in "ID", you "know" it is a foreign key and you have a pretty good idea which table it points to. I work with applications that have dozens, even hundreds, of tables and using a coherent naming standard means the I don't have to keep things like this in my little pea brain. I can work them out on the fly. As we get older, we loose brain cells at an alarming rate and anything we can do to minimize the time we need to spend thinking about something is time well spent.

I also suggested that you change all your column names so they conform to professional standards by removing all spaces and special characters. Once you get to the point where you want to do something in VBA, you'll see what a hassle these "comfort" characters can be. you'll also see that VBA has its own defense mechinism against them. In some cases, it forces you to enclose the offending names in square brackets []. In other cases, it takes it upon itself to actually replace the offending characters with underscores. So, in the unlikely case you had two names - "Account $" and "Account %" which have both spaces and punctuation characters but are otherwise identical, and you wanted to add event code to these columns, Access would upchuck because VBA would want to change both fields to the same value "Account__".

How does my database look now? I really worked hard on it and would appreciate your input before I move on to another area.

Print screen attached.

Thank you.