Gathering information
in [Getting Started]
|
From: kmr on 18 Mar 2010 17:03 I have an end result in mind, but I just cannot figure out the best way to get to this result. I have a database on staff and drills performed. One table has staff first and last names. A second table has the first name (a relationship linking this table's first name to the first table's first name), date, station, time to ID, pass/fail, and observer. From the information I have entered, I want to make a list of all staff who have failed a drill. I have one column for pass and fail, so how to I create something so that it pulls only the fails from that column? Do I use a query, form, or report for this?
From: KenSheridan via AccessMonster.com on 18 Mar 2010 18:07 Whatever the final output format, start with a query which joins the two tables on the First Name columns. If the Pass/Fail column is a Boolean (Yes/No) data type enter False in the criteria row of this column in query design view; if it's a text data type with values 'Pass' or 'Fail' enter Fail in the criteria row. Having created the query you can base a form and/or report on it. However, using the First Name columns as the keys is not a good idea. Names can be duplicated, so are unsuitable as keys. Give the first table an autonumber primary key column such as EmployeeID and add a long integer number (not an autonumber this time) EmployeeID column to the second table as a foreign key to the second table. The tables can then be related on the EmployeeID columns and employees with the same name(s) can be accommodated (I worked with two Maggie Taylors once). The redundant First Name column in the second table can then be deleted. Ken Sheridan Stafford, England kmr wrote: >I have an end result in mind, but I just cannot figure out the best way to >get to this result. I have a database on staff and drills performed. One >table has staff first and last names. A second table has the first name (a >relationship linking this table's first name to the first table's first >name), date, station, time to ID, pass/fail, and observer. From the >information I have entered, I want to make a list of all staff who have >failed a drill. I have one column for pass and fail, so how to I create >something so that it pulls only the fails from that column? Do I use a >query, form, or report for this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1
From: kmr on 26 Mar 2010 14:40 You suggest not using names as a primary key, and instead using auto numbers or employee ID. I have a problem with, and maybe it's easy to solve and that I'm just not thinking of it. So, I have one table with all my staff and an employee ID and a second table with the employee ID and all the drill information. When I go to enter new drills, I will either need to have all employee IDs memorized or I will need to look at the other table to see what the employee ID is. To me, this is creating more work. The reason I chose to use first names was so that I don't have to memorize anything, and I just type everything in without having to think. I have 80 staff, I really can't try to memorize anything or have to look back each time to see what their ID number is. "KenSheridan via AccessMonster.com" wrote: > Whatever the final output format, start with a query which joins the two > tables on the First Name columns. If the Pass/Fail column is a Boolean > (Yes/No) data type enter False in the criteria row of this column in query > design view; if it's a text data type with values 'Pass' or 'Fail' enter Fail > in the criteria row. Having created the query you can base a form and/or > report on it. > > However, using the First Name columns as the keys is not a good idea. Names > can be duplicated, so are unsuitable as keys. Give the first table an > autonumber primary key column such as EmployeeID and add a long integer > number (not an autonumber this time) EmployeeID column to the second table as > a foreign key to the second table. The tables can then be related on the > EmployeeID columns and employees with the same name(s) can be accommodated (I > worked with two Maggie Taylors once). The redundant First Name column in the > second table can then be deleted. > > Ken Sheridan > Stafford, England > > kmr wrote: > >I have an end result in mind, but I just cannot figure out the best way to > >get to this result. I have a database on staff and drills performed. One > >table has staff first and last names. A second table has the first name (a > >relationship linking this table's first name to the first table's first > >name), date, station, time to ID, pass/fail, and observer. From the > >information I have entered, I want to make a list of all staff who have > >failed a drill. I have one column for pass and fail, so how to I create > >something so that it pulls only the fails from that column? Do I use a > >query, form, or report for this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1 > > . >
From: kmr on 26 Mar 2010 14:41 "KenSheridan via AccessMonster.com" wrote: > Whatever the final output format, start with a query which joins the two > tables on the First Name columns. If the Pass/Fail column is a Boolean > (Yes/No) data type enter False in the criteria row of this column in query > design view; if it's a text data type with values 'Pass' or 'Fail' enter Fail > in the criteria row. Having created the query you can base a form and/or > report on it. > > However, using the First Name columns as the keys is not a good idea. Names > can be duplicated, so are unsuitable as keys. Give the first table an > autonumber primary key column such as EmployeeID and add a long integer > number (not an autonumber this time) EmployeeID column to the second table as > a foreign key to the second table. The tables can then be related on the > EmployeeID columns and employees with the same name(s) can be accommodated (I > worked with two Maggie Taylors once). The redundant First Name column in the > second table can then be deleted. > > Ken Sheridan > Stafford, England > > kmr wrote: > >I have an end result in mind, but I just cannot figure out the best way to > >get to this result. I have a database on staff and drills performed. One > >table has staff first and last names. A second table has the first name (a > >relationship linking this table's first name to the first table's first > >name), date, station, time to ID, pass/fail, and observer. From the > >information I have entered, I want to make a list of all staff who have > >failed a drill. I have one column for pass and fail, so how to I create > >something so that it pulls only the fails from that column? Do I use a > >query, form, or report for this? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1 > > . >
From: KenSheridan via AccessMonster.com on 26 Mar 2010 15:19
You don't have to remember the surrogate key values or even be aware of what they are. Just use a combo box set up as follows whenever you need to select an employee. RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. In a form based on a table with a foreign key EmployeeID column its ControlSource would be EmployeeID. The combo box will list the employees by name and you'll see the name in the control when you make a selection, but its underlying value will be the numeric EmployeeID value, which users never need see. Ken Sheridan Stafford, England kmr wrote: >You suggest not using names as a primary key, and instead using auto numbers >or employee ID. I have a problem with, and maybe it's easy to solve and that >I'm just not thinking of it. So, I have one table with all my staff and an >employee ID and a second table with the employee ID and all the drill >information. When I go to enter new drills, I will either need to have all >employee IDs memorized or I will need to look at the other table to see what >the employee ID is. To me, this is creating more work. The reason I chose >to use first names was so that I don't have to memorize anything, and I just >type everything in without having to think. I have 80 staff, I really can't >try to memorize anything or have to look back each time to see what their ID >number is. > >> Whatever the final output format, start with a query which joins the two >> tables on the First Name columns. If the Pass/Fail column is a Boolean >[quoted text clipped - 24 lines] >> >something so that it pulls only the fails from that column? Do I use a >> >query, form, or report for this? -- Message posted via http://www.accessmonster.com |