Displaying a query as a subform?!

[Guest]

I hope I can illustrate this well enough in plain english and I appreciate
your help ahead of time!
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, based on the current ID on the screen.

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 a organization. The same
employee can be all 3 different titles but 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. For
some reason, when I asked the computer to look for those employee names in
another table, the field turned into numeric, and they were initially set up
as "text". Dont know if that means anything.
The employee table has a unique ID that is linked to the history ID, which
is also unique. The organization table also has its own unique ID, not linked
to anything else.
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.
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.

 

[Tony Vrolyk]

You have a lot of issues there. First your data. Each of the three tables
should have a unique ID field whis is set as the Primary Key. This
is most easily accomplished with an autonumber field. Sounds like you
already
have something like
(Table.Field)
Employees.EmployeeID
Organizations.OrganizationID
History.HistoryID

Then the question is to how you want to track History. Sounds like you have
a single History record with three fields for Leader, Member and Reviewer.
This can work but is not the easiest for querying. If you wanted to show all
Organizations visited for an Employee Joe, using your structure here is what
you would get:
SELECT OrganizationID, Leader, Member, Reviewer FROM History WHERE Leader=1
or Member=1 or Reviewer=1
1,Joe,,
2,,,Joe
3,Joe,,
4,,Joe,

Using a more normalized data structure you would get the following:
SELECT OrganizationID, EmployeeID, Title FORM History WHERE EmployeeID=1
1,Joe,Leader
2,Joe,Reviewer
3,Joe,Leader
4,Joe,Member

To query buy Organiztion would look like this
SELECT OrganizationID, EmployeeID, Title FORM History WHERE OrganizationID=1
1,Joe,Leader
1,Jane,Memeber
1,Jack,Reviewer

As you can see the second and third set of results is far easier to read and
run
reports on. This is why normalization is desired. Your method is more akin
to a flat file database. It works but doesn't take as full advantage of
Access' relational design

Before I go any further though, does this make sense so far? I hope I am not
insulting you intelligence but you never know a poster's experience level.
Maybe I could email you an example rather than try to describe it in text.
To email me take the REMOVE out of my email address.

Tony Vrolyk