Interesting form design problem: help!

The first thing I noticed was that it seemed a curious thing that Department
is apparently on the Many side of a one-to-many with Employees. That would
mean each employee can be associated with many deparments, but each
department is associated with one employee. This seemed unlikely, and I
thought of asking about it. I also noted your Top 3 requirement, and was
curious, so I did some checking. My best understanding was that you were
looking for a way to find the top 3 subform records for each main form
record. I am unfamiliar with the exact method for doing that, so instead of
asking about the structure I decided to pass along the search string I had
used, along with some other related information I thought could have been of
interest.
You said that you had a query to find the top 3 Departments for each
employee. What happens when you run the query? Since Departments per
Employee is a somewhat curious model, some more explanation would have
helped toward understanding the problem. If you see "as many names as there
are records" it suggests that each name is stored many times, which in turn
suggests a possible design anomaly. What is the linking field between the
two tables?

"scubadiver" wrote in message
...
It seems you have misunderstood the problem I have trying to design a
form.

"BruceM" wrote:

I am aware of the forum in which you posted. I didn't see how I could
have
totally lost you by suggesting the search string for a Google groups
search,
so I figured you must be referring to the other part of what I said.
Good
luck with your project.

"scubadiver" wrote in message
...
I have written my message in the "Forms" section, not the "reports"
section.
I am trying to design a form using a query based on two tables that has
a
1-to-many relationship.

Please re-read my original message. thanks

"BruceM" wrote:

On what point are you lost? If it is report sorting and grouping, you
are
missing out on one of the strongest features of reports. With the
report
open in design view, click View Sorting and Grouping (or click the
toolbar
icon). To help get you started with that, check out the Help topic
"Group
records in a report".

"scubadiver" wrote in message
...
You have totally lost me but will check out google. thanks

"BruceM" wrote:

I was curious, so I did some checking. I'm afraid I can't provide
detailed
guidance, but I can tell you that if you go to Google groups and
enter:
"microsoft public access" top per group
just as it appears here, quotes and all, you will see a number of
relevant
responses. Perhaps you can refine the search string, but that's
the
general
idea.

My investigations also led me to this, from a Duane Hookom post
last
year:

[A] (non-sql) method is to make sure your report is grouped. Then
add
a
text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount3

"scubadiver" wrote in
message
...
I thought it was possible to base a form on a query.

If I use the same fields from the original tables, the wizard
works
fine
but
I thought it might be easier if I use a query instead then I can
filter
the
records more easily than going through properties in the form
design.

"scubadiver" wrote:

I have two tables, "Employee" and "department", which has a
1-to-many
relationship.

From "employee" I have the fields "employeeID", "Fname" and
"Sname"
and,
from "department", I have the fields "WeekID" and "rate".

When I go through the wizard I choose these fields. Unlike what
I
would
expect, I am not getting the step in the wizard which shows the
fields
from
"employee" in the main form and the fields from "department" in
the
subform.

Not only do does the wizard go straight to the step where I name
the
form
but when I create the form, each name appears as many times as
there
are
records. This hasn't happened before.

Just to note, in the query I have a criteria which states that I
want
the
three most recent records for each employee (using the date as
identifier).

What could be wrong?

Cheers in advance