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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Gathering information



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 10:03 PM posted to microsoft.public.access.gettingstarted
kmr
external usenet poster
 
Posts: 13
Default Gathering information

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?
  #2  
Old March 18th, 2010, 11:07 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Gathering information

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/For...arted/201003/1

  #3  
Old March 26th, 2010, 07:40 PM posted to microsoft.public.access.gettingstarted
kmr
external usenet poster
 
Posts: 13
Default Gathering information

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/For...arted/201003/1

.

  #4  
Old March 26th, 2010, 07:41 PM posted to microsoft.public.access.gettingstarted
kmr
external usenet poster
 
Posts: 13
Default Gathering information



"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/For...arted/201003/1

.

  #5  
Old March 26th, 2010, 08:19 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Gathering information

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

  #6  
Old March 26th, 2010, 08:22 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Gathering information

On Fri, 26 Mar 2010 11:40:02 -0700, 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.


Ummmm...

No.

You don't need to enter the ID.
You don't even need to SEE the ID!!!

It sounds like you're entering data directly into the table datasheet. That's
not how Access is designed to work!

Instead you would use a Form based on the table; on this Form you would have a
Combo Box which displays the employee's name, but stores the ID.

Use the tools that Access provides!!

You might want to look at some of these resources and tutorials, particularly
Crystal's video:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
  #7  
Old March 26th, 2010, 09:25 PM posted to microsoft.public.access.gettingstarted
kmr
external usenet poster
 
Posts: 13
Default Gathering information

Thanks for the information. I'll give it a try. I think it's been too long
though since I last worked with Access, and I just end up frustrated.

"John W. Vinson" wrote:

On Fri, 26 Mar 2010 11:40:02 -0700, 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.


Ummmm...

No.

You don't need to enter the ID.
You don't even need to SEE the ID!!!

It sounds like you're entering data directly into the table datasheet. That's
not how Access is designed to work!

Instead you would use a Form based on the table; on this Form you would have a
Combo Box which displays the employee's name, but stores the ID.

Use the tools that Access provides!!

You might want to look at some of these resources and tutorials, particularly
Crystal's video:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
.

  #8  
Old March 26th, 2010, 09:25 PM posted to microsoft.public.access.gettingstarted
kmr
external usenet poster
 
Posts: 13
Default Gathering information



"John W. Vinson" wrote:

On Fri, 26 Mar 2010 11:40:02 -0700, 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.


Ummmm...

No.

You don't need to enter the ID.
You don't even need to SEE the ID!!!

It sounds like you're entering data directly into the table datasheet. That's
not how Access is designed to work!

Instead you would use a Form based on the table; on this Form you would have a
Combo Box which displays the employee's name, but stores the ID.

Use the tools that Access provides!!

You might want to look at some of these resources and tutorials, particularly
Crystal's video:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
.

 




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 03:23 PM.


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