Starting over with raw data and random join question

[KristenD]

I have almost all of my info with the exception of few hundred pieces of data entered into my database. After looking it over and looking for ways to improve it, would it be easier to import all the tables into a brand new database and starting fresh with everything? I have made a lot of changes since the inception of the database so there are forms and queries and reports that no longer work because of the changes. Or should I go back and change them?

Also, if I am looking to join to tables can they be joined by their foreign keys? They are listed below:

tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumberFK (joined to tblJobs)
EmpType
CraftCode (would get rid of this)

tblEmpRating
EmpRatingID <PK>
EmpID <FK>
EvalDate
SuptID
StatusChange
CraftCode

I want to join the tables based on the EmpID can this be done or do I have to add another field to EmpRating ID?

Thanks!

 

[jdraw]

If you do NOT have a solid data model for what you are trying to build, you'll spend a lot of time moving already entered data from one spot to another and/or become frustrated because you can't easily get the data out of the tables for one reason or another.

If you have test data (both good and bad) based on your business and you have a working or evolving data model, you can play "stump the model" whereby you test your representative data against the model. You record every issue where "something isn't quite right" and reconcile every issue. Maybe the data isn't correct, or a business fact wasn't quite understood, or the evolving model structure is wrong--- make the corrective adjustment and retest until there are no major issues. At some point you will have a structure that supports the business.

Since you say

I have almost all of my info with the exception of few hundred pieces of data entered into my database

, I'm wondering if you have populated the data base a little too prematurely.

It's very difficult to change structures once "all" the data has been entered, but from experience, getting the tables and relationships established is the key to a successful, useable data base.

Good luck with your project.

 

[KristenD]

I have a test database that I have been using to test the changes as well as a live database that I enter the data in.

I have been using reports and queries from the live database as I test them out and add them there. There have been a few subtle changes over the past few months. Those have been easy changes where tables have been updated with the update and delete queries.

I wanted to know if it would just be easier to start fresh or just start deleting the forms, queries and reports that are no longer usable.

Did you have an answer for the join part of the question?

 

[jdraw]

You are only showing 2 tables here

tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumberFK (joined to tblJobs)
EmpType
CraftCode (would get rid of this)

tblEmpRating
EmpRatingID <PK>
EmpID <FK>
EvalDate
SuptID
StatusChange
CraftCode

and you are showing FK to other tables. I think you need to include the tables whose FK are shown.

In plain English what is the query you are trying to build? What do you want to do and where are (which table) the fields involved?
I can mock something up, provided I know the tables and fields involved.
Or you can create a small mdb formatted data base, based on your existing database--
copy table structures and fill with a few records with any confidential stuff removed.


I don't think there is a simple answer to your

I wanted to know if it would just be easier to start fresh or just start deleting the forms, queries and reports that are no longer usable.

Your Forms and Reports have a RecordSource, and it seems to be a query. When you change the fields or the data in tables related to those queries, the results will/may be affected. The subtle changes you mention, do not appear to be so subtle.

When the report or Form record source changes, you may have removed fields from the Table and the query, so the data/field will no longer be available to the Report or Form. Which is what you have found as "no longer works".

Is it better to write/create a Form from scratch, or adjust the Form's record source, then adjust the Form design???? Seems it depends on how big/significant is the change involved. You don't have to DELETE Reports or Forms. You could rename them -- FormEmployees_OLD or whatever. That way, if you want to resurrect something, it's still there, but has to be renamed.

 

[KristenD]

There is no confidential data in this file.

I'm not sure if it is even possible the more I think about it.

Instead of having the pop up table that is currently in the Employee Entry form, I want to just add it to frmRatedby Status and I wanted to try and match up the most current records with out having to go back and pull my files. I need to have tblEmpRating and tblEmpInfo joined. But some of the earlier records will not have info for the jobs. I hope I am explaining myself clearly. Basically on the entry side I want to get rid of a form and enter the info in another form but I need to do a join query so I can fix the form.

 

[KristenD]

I think I made that even more ambiguous than the original question.

I am looking to use the EvalDate in tblEmpRating for other reports that tie back to tblEmpInfo. I thought that I needed to join those tables in order to be able to pull information from them.

For instance, I want a query to show for a particular employee what jobs they have been on over a certain date range. Currently, I can't do that because when I go to put the data that I need in the query it returns with nothing.

Also the entry is confusing and I end up with duplicate data because I am using a pop up table for tblEmpInfo because essentially I only wanted that info when the employee was active. I hope I'm making more sense.

 

[jdraw]

You have to get your tables and relationships sorted out before programming.

You're going to have to show the code related to the query that returns nothing
As I read over your posts, I'm more and more convinced that you need to Normalize your tables and get the relationships designed to meet your needs.

I can't read/use an accdb so can not read your zip file.