shaz0503
asked on
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
rgds
Shaz
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
rgds
Shaz
ASKER
hnasr
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
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
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!
database-start.mdb
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!
database-start.mdb
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).
--
Graham
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).
--
Graham
ASKER
all
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
rgds
Copy-of-SoEHRversion-091210.accdb
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
rgds
Copy-of-SoEHRversion-091210.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hnsar
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....
rgds
S
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....
rgds
S
Hey Shaz
hnsar has given you about 10,000 points worth of time and advice here.
S/he has:
--
Graham
hnsar has given you about 10,000 points worth of time and advice here.
S/he has:
- Downloaded 12.0 MB of "very cut down" data (I wasn't prepared to do that!)
- Given you advice on compacting a database, especially before posting (I would suggest zipping it too!)
- Given you advice on normalising your data
- Reorganised your data to give you an example of how normalisation works in practice.
- Posted an updated version of your database back for you to study.
--
Graham
ASKER
hnsar
Thanks to all for their assistance on this one
continued to play around with it and all now seems good
rgds
Thanks to all for their assistance on this one
continued to play around with it and all now seems good
rgds
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!