How to display a query as a subform?

[Hugo.Simoes]

I am designing this database where I have 3 main tables. First table is to
register all my employees, Second table is to register the organizations we
do business with, and the Third Table, I wanted to display a History of what
my employees have done for me!
The History form is displayed as a subform to the Employee form. If and when
I clicked on the "next record" button, i wanted the history to change along
with the record, this way, displaying what each employee had done for me in
the past in a datasheet view.
I was thinking I had to somehow, display a query off the Organization table
and then print it on the screen displaying each one's history. I have failed
to make that work.
Every time i send employees out to an Organization, they go in groups of 3
(as in Leader, Member and Reviewer). They only time they get assigned the
Leader/Member/Reviewer title is when they go to an organization. The same
employee may be all 3 different titles in different visits. So i added a
combo box that is picking up only the employees names off the Employee table,
this way setting their status for the organization they are going to. Form
some reason, when i did that, it said the field type was "number". God only
knows why!
On the History subform, I only wanted to know, the Company each was going
to, the date and the "status" (leader/member/reviewer); preferably the whole
team names together, in a datasheet view.
The organizations are assigned an ID, which is a unique number. The employee
table has a unique ID as well. On the history table, I established an ID as
well and I have linked it to the employee ID, therefore I could scroll over
the records and the screens would change together, ALWAYS giving me
information about the current ID being displayed.
I was able to make a simple query and get that information displayed on the
screen. But what i'm unable to do, was to make this "history" be displayed
according to the Employee Records, reason why I am requesting your help!
I hope this is not too complicated to understand.

 

[John Vinson]

On Mon, 25 Oct 2004 18:21:05 -0700, "Hugo.Simoes"

Answers inline.

I am designing this database where I have 3 main tables. First table is to
register all my employees, Second table is to register the organizations we
do business with, and the Third Table, I wanted to display a History of what
my employees have done for me!

How are Employees related to Organizations, if at all?

The History form is displayed as a subform to the Employee form. If and when
I clicked on the "next record" button, i wanted the history to change along
with the record, this way, displaying what each employee had done for me in
the past in a datasheet view.

It should do so, if you have the Master/Child Link Fields correct.

I was thinking I had to somehow, display a query off the Organization table
and then print it on the screen displaying each one's history. I have failed
to make that work.

Again... how are Organizations related to history, or to employees?

Every time i send employees out to an Organization, they go in groups of 3
(as in Leader, Member and Reviewer). They only time they get assigned the
Leader/Member/Reviewer title is when they go to an organization. The same
employee may be all 3 different titles in different visits. So i added a
combo box that is picking up only the employees names off the Employee table,
this way setting their status for the organization they are going to. Form
some reason, when i did that, it said the field type was "number". God only
knows why!

Because the EmployeeID *IS A NUMBER*. You may be *seeing* a name in
the Employee table, if you've fallen for the obnoxious, misdesigned
so-called "Lookup Wizard" abomination which Microsoft developed; if
so, the actual contents of the table - the numeric ID - is being
concealed by the lookup combo box. In other words, a number *is
absolutely correct* in this context.

On the History subform, I only wanted to know, the Company each was going
to, the date and the "status" (leader/member/reviewer); preferably the whole
team names together, in a datasheet view.

You'll need to base the subform on a Query then - linking the History
table to the Organization table, and to *three instances* of the
Employee table. Add the Employee table to the query window three times
- Access will alias it as Employee_1 and Employee_2. Join one instance
to Leader, one to Reviewer, one to Member, and pick up the employee
names from the linked tables.

Or, on the Subform, just use combo boxes bound to the three ID fields,
but displaying the names.

The organizations are assigned an ID, which is a unique number. The employee
table has a unique ID as well. On the history table, I established an ID as
well and I have linked it to the employee ID, therefore I could scroll over
the records and the screens would change together, ALWAYS giving me
information about the current ID being displayed.

That's where you went wrong.

The HistoryID is a unique value *for that history record*. It is NOT
an EmployeeID. You have three different employeeID's in the history
table but none of them are the primary key of the History table!

I was able to make a simple query and get that information displayed on the
screen. But what i'm unable to do, was to make this "history" be displayed
according to the Employee Records, reason why I am requesting your help!
I hope this is not too complicated to understand.

Which employee do you want to see? If you have an Employee form, do
you want to see the history record whether the employee was the
Member, the Reviewer, or the Leader? Will you ever (at ANY time in the
future, under ANY circumstances) send *four* employees to an
organization? If so, your table design may be too constraining!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps