Just starting to design a form (or is it a query?)

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no
duplicates) with applicable information. The second table just contains
additional information about some of the same ID#'s from the other table,
maybe about 10 of them. So not all of the ID#'s are represented in the second
table. In the form I just want it to display the additional information if
applicable to the ID# typed in.

I'm just using these two simple tables as a start to try and get a form
together, before I start bringing over more info from Excel. Hopefully this
clarifies the issue?


"Klatuu" wrote:

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

One to Many is a term to define the relationship between tables. In your
case, the Assessment table is the one side where you have ID's with no
duplicates. That is as it should be. The second table is the Many side
where there can be duplicates. It is also acceptable for there to No records
that match an ID in the Assessment table. There should not, however, be any
records in the second table that have IDs that are not in the Assessment
table. Records like these are known as orphans.

Aha! here is a clue to why they call it a Parent - Child relationship. One
parent can have 0 or many children. A child without a parent is an orphan.

You say that there may be records in Assessments that have to matching ID in
the second table. That is okay, but, if it is the case that there will only
be one matching ID in the second table, then maybe the two tables should be
combined into one.

With all that being said, my original concept of a form/subform way of
viewing and editing the data is the usual way to do this.

"Dino" wrote:

Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no
duplicates) with applicable information. The second table just contains
additional information about some of the same ID#'s from the other table,
maybe about 10 of them. So not all of the ID#'s are represented in the second
table. In the form I just want it to display the additional information if
applicable to the ID# typed in.

I'm just using these two simple tables as a start to try and get a form
together, before I start bringing over more info from Excel. Hopefully this
clarifies the issue?


"Klatuu" wrote:

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

OK great! I've been working on it this morning, and I now have a query that
displays the correct information, but in an ugly table format. Are you saying
that I don't use a query at all? Or use a form/subform to display the query
results in a format that I like?
I'm a newbie at this, let me go experiment with forms in Access.


"Klatuu" wrote:

One to Many is a term to define the relationship between tables. In your
case, the Assessment table is the one side where you have ID's with no
duplicates. That is as it should be. The second table is the Many side
where there can be duplicates. It is also acceptable for there to No records
that match an ID in the Assessment table. There should not, however, be any
records in the second table that have IDs that are not in the Assessment
table. Records like these are known as orphans.

Aha! here is a clue to why they call it a Parent - Child relationship. One
parent can have 0 or many children. A child without a parent is an orphan.

You say that there may be records in Assessments that have to matching ID in
the second table. That is okay, but, if it is the case that there will only
be one matching ID in the second table, then maybe the two tables should be
combined into one.

With all that being said, my original concept of a form/subform way of
viewing and editing the data is the usual way to do this.

"Dino" wrote:

Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no
duplicates) with applicable information. The second table just contains
additional information about some of the same ID#'s from the other table,
maybe about 10 of them. So not all of the ID#'s are represented in the second
table. In the form I just want it to display the additional information if
applicable to the ID# typed in.

I'm just using these two simple tables as a start to try and get a form
together, before I start bringing over more info from Excel. Hopefully this
clarifies the issue?


"Klatuu" wrote:

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

A Query is a view of your data. A Form is used to display the data and allow
users to manipulate that data. A query can be the record source for a form.
A table can also be a record source for a form.

"Dino" wrote:

OK great! I've been working on it this morning, and I now have a query that
displays the correct information, but in an ugly table format. Are you saying
that I don't use a query at all? Or use a form/subform to display the query
results in a format that I like?
I'm a newbie at this, let me go experiment with forms in Access.


"Klatuu" wrote:

One to Many is a term to define the relationship between tables. In your
case, the Assessment table is the one side where you have ID's with no
duplicates. That is as it should be. The second table is the Many side
where there can be duplicates. It is also acceptable for there to No records
that match an ID in the Assessment table. There should not, however, be any
records in the second table that have IDs that are not in the Assessment
table. Records like these are known as orphans.

Aha! here is a clue to why they call it a Parent - Child relationship. One
parent can have 0 or many children. A child without a parent is an orphan.

You say that there may be records in Assessments that have to matching ID in
the second table. That is okay, but, if it is the case that there will only
be one matching ID in the second table, then maybe the two tables should be
combined into one.

With all that being said, my original concept of a form/subform way of
viewing and editing the data is the usual way to do this.

"Dino" wrote:

Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no
duplicates) with applicable information. The second table just contains
additional information about some of the same ID#'s from the other table,
maybe about 10 of them. So not all of the ID#'s are represented in the second
table. In the form I just want it to display the additional information if
applicable to the ID# typed in.

I'm just using these two simple tables as a start to try and get a form
together, before I start bringing over more info from Excel. Hopefully this
clarifies the issue?


"Klatuu" wrote:

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino

Thanks! I played around with form design and got a format that looks good
based on the simple sample query that I used. I guess now that I know how to
get started, I just need to practice with this, and experiment.

I did have one question about the form when it displays the data from the
query. I have the ID# at the top of the form, and I understand that I can
either allow manipulation of the number or lock it. After viewing the
requested data, I want to allow users to enter a new number in that field,
and have the form display the new info rather than having to close the form
and open a new form based on the next ID#.
Thanks for your help.


"Klatuu" wrote:

A Query is a view of your data. A Form is used to display the data and allow
users to manipulate that data. A query can be the record source for a form.
A table can also be a record source for a form.

"Dino" wrote:

OK great! I've been working on it this morning, and I now have a query that
displays the correct information, but in an ugly table format. Are you saying
that I don't use a query at all? Or use a form/subform to display the query
results in a format that I like?
I'm a newbie at this, let me go experiment with forms in Access.


"Klatuu" wrote:

One to Many is a term to define the relationship between tables. In your
case, the Assessment table is the one side where you have ID's with no
duplicates. That is as it should be. The second table is the Many side
where there can be duplicates. It is also acceptable for there to No records
that match an ID in the Assessment table. There should not, however, be any
records in the second table that have IDs that are not in the Assessment
table. Records like these are known as orphans.

Aha! here is a clue to why they call it a Parent - Child relationship. One
parent can have 0 or many children. A child without a parent is an orphan.

You say that there may be records in Assessments that have to matching ID in
the second table. That is okay, but, if it is the case that there will only
be one matching ID in the second table, then maybe the two tables should be
combined into one.

With all that being said, my original concept of a form/subform way of
viewing and editing the data is the usual way to do this.

"Dino" wrote:

Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no
duplicates) with applicable information. The second table just contains
additional information about some of the same ID#'s from the other table,
maybe about 10 of them. So not all of the ID#'s are represented in the second
table. In the form I just want it to display the additional information if
applicable to the ID# typed in.

I'm just using these two simple tables as a start to try and get a form
together, before I start bringing over more info from Excel. Hopefully this
clarifies the issue?


"Klatuu" wrote:

What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many
occurances. This is called (clever name) a One to Many relationship. If
there are multiple occurances of the same ID in, then your tables are not
normalized, and it will be almost impossible to match them up properly.

Now, assuming this is the case, what you need is a form with a sub form.
the form should be based on the table that is the One side and the sub form
should be based on the table that is the Many side. You can use the sub form
wizard to help with this, but basically, you link the form and subform using
the common ID number. So each time you move to a new record in the form, the
sub form will display the matching records from the Many side table in the
sub form.

Hopefully, this will give you a start. Post back if you have more questions
on getting this done.

"Dino" wrote:

Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment
amount, billing method, and comment. The other table has fields of ID# (the
same one), type of property, and different comment. I have established the
relationship between the ID#'s on the two tables. What I'm trying to
accomplish is:
A user would open a form (query?), type in an ID#, and all of the pertinent
data for that ID# would come up in an easily readable format, preferably not
a table, but some other design. How do I get started with this?
Thanks,
Dino