So Lost! Creating a database from scratch for the first time!

[jennolsen79]

Solution: Delete all records from "tblEmployeeClasses", then create the relationship.

Thank you so much. That worked. Can you tell, do my relationships look ok? I want to make sure the bones are good before I start all my massive data entry.

[ssanfu]

Why do you have "Position" and "PositionID" in "tblEmployees"? Isn't that what "tblJobHistory" is for???

Also, I would use "FirstName" and "LastName" or "F_Name" and "L_Name" in "tblEmployees".
"First" and "Last" are reserved words in Access.

[jennolsen79]

My next issue.

I have a lot of the EmployeeClasses data in Excel currently. I want to just import this data into tblEmployeeClasses to save me some data entry time. When I tried to do this, I got the following error:

Can anyone explain why I would be getting that error? I don't see anything that would cause this.

[jennolsen79]

Why do you have "Position" and "PositionID" in "tblEmployees"? Isn't that what "tblJobHistory" is for???

Good Point. I'll remove it.

Also, I would use "FirstName" and "LastName" or "F_Name" and "L_Name" in "tblEmployees".
"First" and "Last" are reserved words in Access.

I changed those.

This is what my new relationships look like. Does this seem like it would work correctly now?

[jennolsen79]

My next issue.

I have a lot of the EmployeeClasses data in Excel currently. I want to just import this data into tblEmployeeClasses to save me some data entry time. When I tried to do this, I got the following error:

Can anyone explain why I would be getting that error? I don't see anything that would cause this.

I've narrowed this down to it having something to do with the relationship. I have all my relationships set to

with join properties of

Am I going wrong on the relationship/join property?

Sorry for all the questions and baby steps. I've never done this before and I want to make sure I get it right.

[jennolsen79]

Perhaps I am doing this in the wrong order?

Is the correct order when creating a database:
1. Tables
2. Data entry
3. Relationships
4. Queries
?

[Dal Jeanis]

1A) Add an autonumber primary key to the tblEmployeeClasses.
1B) There shouldn't be any other unique keys on tblEmployeeClasses. Even if an employee can only take each class once (no refreshers allowed on all courses?), I'd still enforce that business rule a different way.
1C) You only want one-way referential integrity on tblEmployeeClasses to tblEmployees and to tblClasses. The one-to-many should be doing that correctly, so I don't think that's your problem.

2) Create relationships before Data entry.

3) Enter primary records (tblEmployees and tblClasses) before you enter junction records (tblEmployeeClasses).

4) Queries can be created at any time. They can only be tested when you have data, though.

[jennolsen79]

1A) Add an autonumber primary key to the tblEmployeeClasses.
1B) There shouldn't be any other unique keys on tblEmployeeClasses. Even if an employee can only take each class once (no refreshers allowed on all courses?), I'd still enforce that business rule a different way.
1C) You only want one-way referential integrity on tblEmployeeClasses to tblEmployees and to tblClasses. The one-to-many should be doing that correctly, so I don't think that's your problem.

2) Create relationships before Data entry.

3) Enter primary records (tblEmployees and tblClasses) before you enter junction records (tblEmployeeClasses).

4) Queries can be created at any time. They can only be tested when you have data, though.

1A - Done - But why? tblJobHistory doesn't have a PK...
1B - There isn't, and employees can take certain classes more than once. In fact, some are required to be taken annually. That's why in tblClasses there is a RecertFreq.
1C - When I removed all the relationships, I had no issues importing the data, thus why I thought data entry was before relationships.

2 - Thanks.

3 - Most of that is done already (thankfully)

4 - Great. I think I'll save them until the end then, so I can focus on one thing at a time.

[jennolsen79]

Trying to recreate relationships I'm running into issues again. Is there a way to compare the data from two tables to determine where information differs? Or should I just scratch one of the tables and start over?

[jennolsen79]

Trying to recreate relationships I'm running into issues again. Is there a way to compare the data from two tables to determine where information differs? Or should I just scratch one of the tables and start over?

Nevermind. I figured this one out as far as being able to compare the information.

[Dal Jeanis]

1A) The reason you put a PK on tblEmployeeClass is this - if you want to delete one of the records that show Bob took Class X, how do you control with absolute certainty which one you're deleting?

1B) That indicates that your problem with import was the data type of one or more fields. Either something was being stored in Excel in a different format than the equivalent field, or the order of the fields was different. The solution to this is to import your Excel spreadsheet into a new temporary table, then massage the data as needed, then append the data from the work table to the real tables.

1C) Deleting the relationships probably just hid the problem. Although I'm not sure how that could have fixed the data type issues... hmmm... hopefully you meant that you could import the tblEmployeeClasses out of order without issues...


The concept of Work tables in 1B is the key to making your initial setup painless. You create a table, add fields, update values, and so on, until you have a table that has all the info you want. Then you skootch the data over into the live table, and you're done.

FYI "Skootch" is a technical term. Use it often, to demonstrate your mastery of the technology.