Basic database and struggling from the beginning

[Burnsie]

Really stuggling – novice building a database.
Hello.
I’ve posted a few times in various forums, but I feel I really need to go back to the start with my database here.
I am rebuilding (from scratch) a database where you can track an employees’ health measures. These include things such as height, weight, BMI, blood pressure etc. This is within a work environment.
I therefore have created two tables;
1- Personal details (Name, age, DOB, employee ID)
2- Testing date (date, blood pressure, weight etc and again employee ID).
What I think should happen is that I create a relationship between the two employee ID fields in each tables to link them? The employee ID is unique to every different staff member.
Then what I had before is one form (personal details) with a testing subform where you can see each client’s testing scores from visit (i.e. Joe Blogg’s personal details would related, in the subform, to a number of different test dates).
Then I had a report based on the above so I can print a report (searching by employee ID) and give it to the client.
However….I’m stuck at the first stage here (relationships) as I cant get my two subforms to “link”.
So…going back to the start…where did I screw up?
:banghead:

 

[EternalMyrtle]

Hi,

I will try to help you. Even if Employee ID is a unique field, I wouldn't link the tables by that. You should set up your tables with an AutoNumber field and use that as the Primary Key.

Then your table structure will look like this:

tblEmployees
EmployeeID (PK)
CompanyEmployeeID
Sex
DOB (Do not store AGE that is a calculation and should be done in a query (Now()-DOB)
Etc.

tblTests
TestID (PK)
EmployeeID (FK)
TestDate
BloodPressure
etc..

Now what you are linking is the PK from tblEmployee and the FK in tblTest (also EmployeeID)

In your forms and subforms you will create a lookup so that the person's name or company-given id can be used to identify them

Let me know how this goes and we can move forward to the next step

 

[EternalMyrtle]

BTW, the relationship between tblEmployees and tblTests is one to many (one employee many tests). Make your relationships in the relationships windows (database tools>relationships).

In your table, the FK fields should be a NUMBER datatype with no lookups. Lookups will go in the forms.

 

[EternalMyrtle]

Drag the field onto the matching field and select enforce referential integrity

 

[Burnsie]

so far so good, i think.

(p.s. thanks for your help so far).

 

[EternalMyrtle]

Great!

Let me know if you need help with the subform and the report. If you use the wizard for the subform it should pick out the correct fields to link since this is a straightforward situation.

 

[Burnsie]

Great!

Let me know if you need help with the subform and the report. If you use the wizard for the subform it should pick out the correct fields to link since this is a straightforward situation.

Yep. Just need to work out how to make the report do what I want...for some reason, it keeps putting headings in a header so its all in table format, rather than allowing me to move any data set where id like it to go
:banghead:

 

[EternalMyrtle]

Right click on the controls and select "Layout>Remove" Then you should be able to move them around

 

[EternalMyrtle]

Select them all first--you can click outside and drag around them all to do this. Then make headers by using Group.

It takes a little practice to get used to report formatting but you will get the hang of it.

 

[Burnsie]

Right click on the controls and select "Layout>Remove" Then you should be able to move them around

booming! right...lets get this done!

 

[Burnsie]

right then... all i need to do now is work out how to produce a form based on Staffnumber.... ie click on reports and search for the number, that brings up the clients own report or their test results??

 

[Burnsie]

oooo, I’ve progressed. In fact, I’ve nearly finished.

Each person that gets "tested" will get a report with all their own test data on. I’ve made the report, but want to filter it, so all I do is click on the report and enter in parameter (employee number) and the report pops up.

I’ve made a query for this, but if I run the query, the results pop up in a table and not a report (can’t print the table off for the client).

Final bit of help??

 

[namliam]

create a report to display the data "nicely"?

 

[Burnsie]

create a report to display the data "nicely"?

I have done, but if i open the form, i need it to only display the person sitting in front of me. At the moment it displays the detials of the first person I saw (cleint number 1).

 

[namliam]

You have a query that does what you want it too, use the query as source for your report?

You looking for a report or a form? You are confusing me now.

 

[Burnsie]

You have a query that does what you want it too, use the query as source for your report?

You looking for a report or a form? You are confusing me now.

no, its a report ive completed, which is formatted and strcuted and look nice

so, how do you use a query as a source for the report

(sorry for being stupid)

 

[namliam]

The same way you use a table as the source....

I’ve made a query for this, but if I run the query, the results pop up in a table and not a report (can’t print the table off for the client).

I think you said here, I hve made a query for this???

 

[Burnsie]

The same way you use a table as the source....


I think you said here, I hve made a query for this???

errrrrr????

 

[namliam]

I’ve made a query for this, but if I run the query, the results pop up in a table and not a report (can’t print the table off for the client).

Might be my over reading your comment in this post, but your saying I have it fixed in a query.

 

[EternalMyrtle]

So you need to be able to search the report? You can search the report with Ctrl+F although it is not elegant.

The other option is to create a link from your form that opens up your report to the specific employee.

You could also probably create a pop-up search form to search the report but this would involve a lot of work.