My first database

[djk21108]

Hi everyone,



I would really appreciate some help on my first database. I'm trying to learn as I go, but it's getting a little complicated with no background.

I am tasked with making a very simple database to replace the spreadsheet we normally use to complete this task.

We need to count the errors each Employee makes during data entry.

The information that needs to be recorded is as such.

1) Employee who did the entry
2) Employee who proofed
3) Date
4) BatchID (Unique string that labels the stack of documents to enter data from)
5) Type of Documents entered
6) # of Documents entered

So, where I need to go with this is as such:

I'm pretty sure I need one table that I list all employees names on. The end user should be able to select both the employee who did the data entry, and the employee who did the proofreading from that list.

I made a table called "Employees." I'm not sure if this table should have a primary key as an autonumber set, or just use the names as the primary key.

I don't really know where to go from here. I try to make queries that make it so the employees names from the employee table shows up in the second table I made with fields for all the other information.

Any information to get me started would be really appreciated.

Thanks so much.

[RayMilhon]

Never, never use the names as your primary key. What happens if you have 2 John Smiths. Either use an Autonumber, SSN or if your company has one an Employee ID Number. Whatever you use it has to be unique.

[djk21108]

Never, never use the names as your primary key. What happens if you have 2 John Smiths. Either use an Autonumber, SSN or if your company has one an Employee ID Number. Whatever you use it has to be unique.

Point taken. So my Employee table now has two fields. EmployeeID(autonumber) and Employee Name(text).

I want the employee's name to show up in operations involving other tables. Where do I go from here?

[djk21108]

Here's where I am progress wise

I have 3 tables set up:

Batch: Contains:
Primary Key: BatchID
Batch: Local Batch number given in office
EmployeeID: Which is linked as a one to many (this is the many) from another table
ProoferID: Which is linked as a one to many (this is the many) from another table
Date:
Document Type:
# Document:
# Errors

Employee:
Primary Key: EmployeeID
Employee Name


Proofer
Primary Key: Proofer ID
Proofer Name



I would love some help in determining the exact options I should use for defining the relationships, in terms of "Enforce Referential Integrity" and all that other jazz.

Thanks!

[djk21108]

I'm really struggling here. I attempted to create a query that combines the information from my 3 tables.

I can't enter things like Local Batch Number in my query. When I create a form to do it, that doesn't work either. I just get a beep when I start typing

[RayMilhon]

First get rid of the proofer table. All Proofers should also be employees

tblBatch
Batchid Primarykey
Batchdate
Dataentryid Foreignkey to employee table
Prooferid Foreignkey to employee table
totalErrors
Documenttype

[djk21108]

Thank you Ray.

I did as you directed.

When I selected prooferid and dataentryid as foreign keys that both relate back to EmployeeID in the Employee table, it tells me I can't ensure referential integrity. I had to keep that box unchecked.

Is that ok?

First get rid of the proofer table. All Proofers should also be employees

tblBatch
Batchid Primarykey
Batchdate
Dataentryid Foreignkey to employee table
Prooferid Foreignkey to employee table
totalErrors
Documenttype

[RayMilhon]

Yes. Referential Integrity is the integrity of the foreign key since you have 2 foreign key fields to the same table It cannot be ensured.

[djk21108]

Yes. Referential Integrity is the integrity of the foreign key since you have 2 foreign key fields to the same table It cannot be ensured.

Awesome. Thanks again.

An issue I'm running into occurs during form creation.

I want to make a form where the proofer can enter in the date, number of docs etc via typing it in.

The employee and proofer i want them to select from a drop down.

Only problem is, if they're selected in the drop down: it doesn't work because all I have is a prooferID and employeeID field. They correspond to numbers, whereas the drop down represents the names as selected from the employee table.

Any way to rectify this?

[djk21108]

Essentially I want people to be able to select a name from a drop down, and have access ACTUALLY interpret that as them selecting their unique EmployeeID number.

[djk21108]

I tried making a query that combines DataEntryID from Batchtbl, with EmployeeName from Employee table.

How do i populate that query with every employee's name?

[RayMilhon]

I assume your using a combo box for the Proofer, Enter ID Include both columns empID and empname. In the data section of the combo box there is an area that says bound column put in 1
In the format section under column count put 2, under column widths put 0;2 assuming the size of the combo box is 2 inches.

when clicking on the combo box you will see the employees name. Clicking on one will save the empid in the empid column. Oh and make sure the control source is the prooferid or empid field in the batch table your entering.

[djk21108]

I assume your using a combo box for the Proofer, Enter ID Include both columns empID and empname. In the data section of the combo box there is an area that says bound column put in 1
In the format section under column count put 2, under column widths put 0;2 assuming the size of the combo box is 2 inches.

when clicking on the combo box you will see the employees name. Clicking on one will save the empid in the empid column. Oh and make sure the control source is the prooferid or empid field in the batch table your entering.

I'm using the combo box for BOTH proofer and data entry person.

I'll try your instructions tomorrow at work! Thank you so much!

I thought I would have to construct queries to combine stuff, but it looks like I don't need to according to you.