Interesting form design problem: help!

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

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

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

I should mention that the non-sql method from my previous post will work
only with reports.

"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

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

I didn't realise these messages go into Google. I have just found my message
:-)

"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

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

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

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

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