Interesting form design problem: help!

G

Guest

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
 
G

Guest

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.
 
B

BruceM

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.txtCount>3
 
B

BruceM

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

Guest

You have totally lost me but will check out google. thanks

BruceM said:
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.txtCount>3

scubadiver said:
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.
 
G

Guest

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

BruceM said:
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.txtCount>3

scubadiver said:
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.
 
B

BruceM

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 said:
You have totally lost me but will check out google. thanks

BruceM said:
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.txtCount>3

scubadiver said:
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.

:

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
 
G

Guest

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 said:
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 said:
You have totally lost me but will check out google. thanks

BruceM said:
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.txtCount>3

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.

:

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
 
B

BruceM

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 said:
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 said:
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 said:
You have totally lost me but will check out google. thanks

:

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.txtCount>3

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.

:

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
 
G

Guest

It seems you have misunderstood the problem I have trying to design a form.

BruceM said:
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 said:
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 said:
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".

You have totally lost me but will check out google. thanks

:

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.txtCount>3

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.

:

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
 
B

BruceM

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 said:
It seems you have misunderstood the problem I have trying to design a
form.

BruceM said:
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 said:
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

:

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".

You have totally lost me but will check out google. thanks

:

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.txtCount>3

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.

:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top