Duplicate entries

[Janice]

I have a database of student detentions. I have been keying the information
in each day. Now, the db is so long that it is getting burdensome to look
at. Is there any way that I could create a form or table or something where
I could just pull up that student and key in any new information? Any
assistant would be greatly appreciated.

 

[PC Datasheet]

Create a query that sorts the students alphabetically by last name
ascending. Add an unbound combobox to the form header of your data entry
form. Set the row source property of the combobox to the query you just
created and set the bound column to the column containg the students' last
name. Set the Auto Expand property to Yes.

Now when you start typing a student's name in the combobox, the dropdown
list will scroll to the first student'sast name beginning with the lrtters
you entered. You can finish scrolling manually and then use the combobox's
AfterUpdate event to bring up the student's record.

 

[Steve]

-----Original Message-----
I have a database of student detentions. I have been keying the information
in each day. Now, the db is so long that it is getting burdensome to look
at. Is there any way that I could create a form or table or something where
I could just pull up that student and key in any new information? Any
assistant would be greatly appreciated.
.
Hello Alice,

You sure can. The easiest way is to create a Query. I
would love to help you accomplish this. But to make it
easier for me to help you, email me a list of how you
have your fields created in your form. Then I can send
you a print screen via email on exactly how to do it.

Do you use Microsoft Word? If you do it will be great.
This way I can send it to you as an attachment.

 

[Marc]

Hi,

If you look in the NWind sample database that comes with the product CD, the
phone list, and the orders list demonstrate two different ways to do this.
HTH
Marc

 

[Janice]

Steve:
The fields are as follows, respectively: Address ID, Student Status,
Name, Grade, Type of Detention, Date Issued, Date Served, Issuing Principal,
Offense, and Dropped Date (in case the student is no longer with us). As I
said, I am trying to accomplish a form or something to just pull up the
student name and all offenses by listed and I could just add another offense
to their name.

Thanks for your help,
-Janice ([email protected]

 

[John Vinson]

Steve:
The fields are as follows, respectively: Address ID, Student Status,
Name, Grade, Type of Detention, Date Issued, Date Served, Issuing Principal,
Offense, and Dropped Date (in case the student is no longer with us). As I
said, I am trying to accomplish a form or something to just pull up the
student name and all offenses by listed and I could just add another offense
to their name.

Your tables are incorrectly normalized. You should have (at least) TWO
tables: Students, with the student's unique ID, name, and other
biographical information; and Offenses, with a StudentID link to this
table.

Why AddressID? Surely you'll have more than one student at each
address?

With the two (or more) table structure, you could have a Form based on
the Student table, with a Subform based on the Offenses table. The
subform would show (and allow entry of) all offenses for that
particular student.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Janice]

Thanks for the advice. Why AddressID? is because I have never really used
Access. I am more of an Excel person and thought....well, there is no time
like the present.

Will I be able to import the names, etc. if I start a new table?

 

[John Vinson]

Thanks for the advice. Why AddressID? is because I have never really used
Access. I am more of an Excel person and thought....well, there is no time
like the present.

Will I be able to import the names, etc. if I start a new table?

Sure. An Append Query will let you append data from one table into
another table - all the data, or any selected portion of it.

Perhaps if you wish, you could post a description of your proposed
table structure, and identify the "Entities" - real-life people,
things or events - of importance to your application; someone should
be able to help set up the tables properly.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Janice]

The best way I have been able to describe what I want is like an invoice. I
want to be able to pull up the student name like you would a customer name,
see all the offenses, dates, type of detentions, etc. and add any new
offenses to that student's record.

This is the information I need:

John Smith
9th grade
Type of detention (A/S - After-school is one type)
Date Issued (10/21/04)
Date Served (10/25/04)
Issued by (Principal that issued the detention by 2 initials - TC)
Offense - Truancy or Disrespect or Class disruption
Student Status (Active or Inactive)
Date Dropped from Rosters (11/1/04)

Any help would be greatly appreciated!
-Janice

 

[John Vinson]

The best way I have been able to describe what I want is like an invoice. I
want to be able to pull up the student name like you would a customer name,
see all the offenses, dates, type of detentions, etc. and add any new
offenses to that student's record.

This is the information I need:

John Smith
9th grade
Type of detention (A/S - After-school is one type)
Date Issued (10/21/04)
Date Served (10/25/04)
Issued by (Principal that issued the detention by 2 initials - TC)
Offense - Truancy or Disrespect or Class disruption
Student Status (Active or Inactive)
Date Dropped from Rosters (11/1/04)

Any help would be greatly appreciated!

Ok... I see (at least) the following entities with their attributes,
displayed (to save a step) as Tables with Fields:

Students
StudentID Autonumber [Primary Key] ' names aren't unique
LastName
FirstName
Grade
Active Yes/No
<any other needed info about the student as a person>

Offenses
Offense Text [Primary Key]
<perhaps fields describing the default punishment>

Principals
Initials [Primary Key] <though initials are even less unique than
names; might you not have James Kelly and Janet Keith, both
principals? Or would that be against your principles <g>?>
LastName
FirstName

DetentionTypes
DetentionType Text [Primary Key]
Description

Detentions
StudentID <link to Students>
Offense <link to Offenses>
DetentionType <link to DetentionTypes>
DetentionStartDate Date/Time
DetentionEndDate Date/Time
Comments <Text, or Memo if comments may exceed 255 bytes>


You would do the data entry by having a Form based on Students, with a
Subform based on Detentions. This Subform - in Continuous view - would
have combo boxes based on Offenses and DetentionTypes, and textboxes
for the date fields and comments.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Janice]

Thank you very much. I believe this will help me tremendously! My db is up
to 1514 entries as of Thursday.

-Janice

The best way I have been able to describe what I want is like an invoice. I
want to be able to pull up the student name like you would a customer name,
see all the offenses, dates, type of detentions, etc. and add any new
offenses to that student's record.

This is the information I need:

John Smith
9th grade
Type of detention (A/S - After-school is one type)
Date Issued (10/21/04)
Date Served (10/25/04)
Issued by (Principal that issued the detention by 2 initials - TC)
Offense - Truancy or Disrespect or Class disruption
Student Status (Active or Inactive)
Date Dropped from Rosters (11/1/04)

Any help would be greatly appreciated!

Ok... I see (at least) the following entities with their attributes,
displayed (to save a step) as Tables with Fields:

Students
StudentID Autonumber [Primary Key] ' names aren't unique
LastName
FirstName
Grade
Active Yes/No
<any other needed info about the student as a person>

Offenses
Offense Text [Primary Key]
<perhaps fields describing the default punishment>

Principals
Initials [Primary Key] <though initials are even less unique than
names; might you not have James Kelly and Janet Keith, both
principals? Or would that be against your principles <g>?>
LastName
FirstName

DetentionTypes
DetentionType Text [Primary Key]
Description

Detentions
StudentID <link to Students>
Offense <link to Offenses>
DetentionType <link to DetentionTypes>
DetentionStartDate Date/Time
DetentionEndDate Date/Time
Comments <Text, or Memo if comments may exceed 255 bytes>


You would do the data entry by having a Form based on Students, with a
Subform based on Detentions. This Subform - in Continuous view - would
have combo boxes based on Offenses and DetentionTypes, and textboxes
for the date fields and comments.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Janice]

John,
I set up the tables as you demonstrated. I got my form set up like I
wanted it. Now, how do I input into the form and make it save in a table.
In other words, when I enter a student it will display that student in the
detentions table I created but it won't retain it. When I key the next
student in, it just replaces it.

Thanks,
-J

The best way I have been able to describe what I want is like an invoice. I
want to be able to pull up the student name like you would a customer name,
see all the offenses, dates, type of detentions, etc. and add any new
offenses to that student's record.

This is the information I need:

John Smith
9th grade
Type of detention (A/S - After-school is one type)
Date Issued (10/21/04)
Date Served (10/25/04)
Issued by (Principal that issued the detention by 2 initials - TC)
Offense - Truancy or Disrespect or Class disruption
Student Status (Active or Inactive)
Date Dropped from Rosters (11/1/04)

Any help would be greatly appreciated!

Ok... I see (at least) the following entities with their attributes,
displayed (to save a step) as Tables with Fields:

Students
StudentID Autonumber [Primary Key] ' names aren't unique
LastName
FirstName
Grade
Active Yes/No
<any other needed info about the student as a person>

Offenses
Offense Text [Primary Key]
<perhaps fields describing the default punishment>

Principals
Initials [Primary Key] <though initials are even less unique than
names; might you not have James Kelly and Janet Keith, both
principals? Or would that be against your principles <g>?>
LastName
FirstName

DetentionTypes
DetentionType Text [Primary Key]
Description

Detentions
StudentID <link to Students>
Offense <link to Offenses>
DetentionType <link to DetentionTypes>
DetentionStartDate Date/Time
DetentionEndDate Date/Time
Comments <Text, or Memo if comments may exceed 255 bytes>


You would do the data entry by having a Form based on Students, with a
Subform based on Detentions. This Subform - in Continuous view - would
have combo boxes based on Offenses and DetentionTypes, and textboxes
for the date fields and comments.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

John,
I set up the tables as you demonstrated. I got my form set up like I
wanted it. Now, how do I input into the form and make it save in a table.
In other words, when I enter a student it will display that student in the
detentions table I created but it won't retain it. When I key the next
student in, it just replaces it.

I'm not certain what you're doing, so I can't be sure how to answer!

My idea was that you would have a Form based on the Students table,
and a Subform based on the detentions table, using the StudentID as
the master/child linking field. You don't "input into a form and save
it to a table" - the Form *IS* a tool that lets you enter data into
the table; set the Form's Recordsource to the table, or to a query
based on the table.

Also you should NOT *enter a student into the detentions table*. The
detentions table does NOT contain any information about students,
other than the StudentID and the detenetion that student is serving.
Information about students should exist *only* in the student table.

Please post back with a description of your tables, and how you are
entering data for those tables.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Janice]

This is the way I set it up: (However, the point is moot now - my Jump Drive
with all the information has disappeared and I have to begin from scratch
anyway.)

Students
StudentID Autonumber [Primary Key] ' names aren't unique
LastName
FirstName
Grade
Active Yes/No
<any other needed info about the student as a person>

Offenses
Offense Text [Primary Key]
<perhaps fields describing the default punishment>

Principals
Initials [Primary Key] <though initials are even less unique than
names; might you not have James Kelly and Janet Keith, both
principals? Or would that be against your principles <g>?>
LastName
FirstName

DetentionTypes
DetentionType Text [Primary Key]
Description

Detentions
StudentID <link to Students>
Offense <link to Offenses>
DetentionType <link to DetentionTypes>
DetentionStartDate Date/Time
DetentionEndDate Date/Time
Comments <Text, or Memo if comments may exceed 255 bytes>


You would do the data entry by having a Form based on Students, with a
Subform based on Detentions. This Subform - in Continuous view - would
have combo boxes based on Offenses and DetentionTypes, and textboxes
for the date fields and comments."

 

[John Vinson]

John,
I set up the tables as you demonstrated. I got my form set up like I
wanted it. Now, how do I input into the form and make it save in a table.
In other words, when I enter a student it will display that student in the
detentions table I created but it won't retain it. When I key the next
student in, it just replaces it.

The student should NOT BE IN the detention table. (Yes, I know the
student probably should be in detention - but that's different!)

You explained how you have the Tables set up (looks a bit familiar...
<g>) but not the Form. My idea was that you should have a Form based
on the Student table, with a Subform based on the Detentions table.
The student information will be entered once only for each student;
when a student needs to be entered into the detentions table, you
would bring up that student's information on the main form (use a
combo box to search for the record for example) or entered if they are
not yet in the system.

The Subform would have combo boxes for the principal, the type of
detention, etc.

You would rarely have any reason to look at the Detentions *table*. It
wouldn't have much useful information in it for human eyes - numeric
studentID's, numeric detention-type codes, and so on. Not to worry;
making sense of that is the computer's job! That's why you use Forms
for onscreen viewing and Reports for printing.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps