Access Table and form design assistance

All I seem to be going around in circles with my Db design.  Thanks to all have helped with questions thus far.

I need to 're design' my tables, I think, to best utilise the Db.

Currently the Db is a data capture tool for staff performance reviews.  the initial set up was based on information that was imported from a spreadsheet [tblStatementDetails] and a form was built from this data.  Now I need to 'improve' the Db so that the main table [tblStatementDetails] holds all employees data.  this information will come from a table [EmployeeData].

I am sure I know the answer but logic has escaped me on this one....

The main form [tblMainPage]currently 'loads' data from [tblStatementDetails] through a search of ID numbers, BUT, as this does not 'link' to the [EmployeeData] not all staff are within the main data table.How do I get the [tblStatementDetails] to 'update' with the data from [EmployeeData] as most recent data is imported monthly.

[EmployeeData] is from in import where as the Perfomance data is entered / updated against each employee via the form

I guess to really simplify my questions:

I need all employee related data to read from [EmployeeData] table and the related Performance Management information to read from [tblStatementDetails] BUT need to capture all employee data with related Performance data in [tblStatementDetails].

Maybe I am making more out of this than needs...but I seem to be travelling in circles on how to get this


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
You need to look into access help: Database design basics
This will introduce you to the basics of access database design.

Making it simple, You need a Parents table (id, f1, f2, f3, ....)
Then a childrens table (cid, f1, f2, f3, ...)

You fill Parents records first.
Then for each child's record you insert its parent id, to make it unique.

You then build a query beteeen the two tables linked through pid and that makes all the parent data available for every child.
Forms are built with such query as their record sources.
Good luck!
shaz0503Author Commented:

Thanks - this is really doing my head in ... and I haven't tried your suggestions as I really don't understand

I have tried various ways of getting this to work, form from query et c etc - but doesn't want to play the game. the form worked based on the query from the two tables (ie displayed the correct information) but would not allow me to update

What happens is

1   the team users a form to update staff records

Currently the user is required to update or enter two sets of information (HR Data) & (performance data)

2  This information is currently captured in a table [tblStatementDetails].

To eliminate the requirement for HR Data to be entered by the user or updated, I need the form to display individual records using a table [EmployeeData] as this is imported from a download from an enterprise system and records ALL employee information - this data may change as staff leave and commence.

the need is to allow the user to add performance information where there is a new employee (based on the [EmployeeData] table) within the form that populates the [tblStatementDetails] table.

If I could 'simply' import the most recent EmployeeData to the [tblStatementDetails] by updating only selected fields (Name, ID etc) - this would be perfect.....

hopes this add some more info
Hamed NasrRetired IT ProfessionalCommented:
To design a database, it needs time, and this site is not designed for such job.
Here it helps to debug issues that arise while designing or using databases.

You need to apprach the issue with patience, and be sure taht all started as begginers. You will remeber this when you pass the Master rank in this site.

As a starter, this is a sample database, with 2 tables, 2 forms, and a query as a link between the 2 tables.
Good luck!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hi Shaz
It's really difficult to give you advice on this without having a description of all the different entities in your data and what they represent, preferably with some example data.
It sounds line all your data is coming from a spreadsheet that you are importing as tblStatementDetails, correct?  I'm still not quite clear where EmployeeData comes into the picture.  Is it another imported data source, or is it data derived from tblStatementDetails?
Whatever the answers, we need to come up with a proper normalised set of tables to store your data.
Can you please post some sample import data, with a description of what the columns are (if it's not obvious from the headings).
shaz0503Author Commented:

I have attached a very cut down version of the Db giving me grief.

Data on the form is captured in the [tblStatementDetails] table.  

As mentioned above, currently all the information is being entered by the user either as an update or a new record.

What I would like to happen is that the employee data ie ID; Area Description; Surname; Given Name; Hire Date; Termination Date; Employment Term; Position Description -
is 'picked up' from the [EmployeeData] table as an import of new data is undertaken on a monthly basis. The use of the [EmployeeData] would ensure that all staff are within the [tblStatmentDetails] table

the remainder of the information on the form is input by the user and is subsequently updated as needed.

all records information should be stored in the [tblStatementDetails]

hope this helps some what


Hamed NasrRetired IT ProfessionalCommented:
You need to compact and repair the database before attaching, it reduced the size to 600KB instead of 12MB.

I think you needto do some homework on database design, and normalization as mentioned in previous post.
Your employeedata table includes info about employee department, and name, ... This should not be repeated in the tblStatementDetails table. This applies to department description and as you can see, abbreviation of the description is used. The proper info can be pulled out from the tblCollegeArea table.
The form can be built on a query from the two tables, where it will expose the employee data based on the lemployee id in the tblStatementDetails table.

I think you have to stop here, normalize your data and proceed. This will improve querying and reporing the data.

Check this version of database, where some redundant fields removed from tables. The form is based on a query Query1, that links three tables. More normalization may be required.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shaz0503Author Commented:

thanks for the compact tip - should have realised

I am going to study up and play with this Db over the weekend and see how I go....


Hey Shaz
hnsar has given you about 10,000 points worth of time and advice here.
S/he has:
  1. Downloaded 12.0 MB of "very cut down" data (I wasn't prepared to do that!)
  2. Given you advice on compacting a database, especially before posting (I would suggest zipping it too!)
  3. Given you advice on normalising your data
  4. Reorganised your data to give you an example of how normalisation works in practice.
  5. Posted an updated version of your database back for you to study.
I think you should accept 30811267 as a solution with an A grade.
shaz0503Author Commented:

Thanks to all for their assistance on this one

continued to play around with it and all now seems good

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.