Table

I think the reason why they have the Parttype in table3 is because when they
create the report, they sum the number by by part type. So if in the access
design I shouldn't have this field in another table, but what should I do for
the report?

Thank you very much for your help.

"John Vinson" wrote:

On Tue, 23 Jan 2007 08:48:58 -0800, Hong
wrote:

The problem is now on the form, the people need to enter the Partnumber and
also need to enter the Part type, I am trying to make it easier, when they
enter the part number, the field for part type autofill according to what
part number they enter.

Sorry for the trouble.

I would recommend using a Combo Box (named cboPartNumber let's say)
for the part number, so that the user can select it from a list (which
will autocomplete if they type into it), to prevent typing errors.
Include the Part Type in the Query upon which the combo is based:

SELECT [Part Number], [Part Type]
FROM [Parts]
ORDER BY [Part Number];

In the Combo Box's AfterUpdate event, "push" the part type into the
bound textbox (named txtPartType) on the form:

Private Sub cboPartNumber_AfterUpdate()
If IsNull(Me!txtPartType) Then
Me!txtPartType = Me.cboPartNumber.Column(1)
End If
End Sub


Be aware that having the part type stored in both tables is VERY
DANGEROUS - since the user can overtype the part type, you *will* at
some point have one Part Number which has multiple different Part
Types stored. All but one of them WILL BE WRONG, and you'll have no
easy way to detect this fact.

John W. Vinson[MVP]

On Tue, 23 Jan 2007 12:16:01 -0800, Hong
wrote:

I think the reason why they have the Parttype in table3 is because when they
create the report, they sum the number by by part type. So if in the access
design I shouldn't have this field in another table, but what should I do for
the report?

Base the Report *ON A QUERY* joining the two tables, joining on the
part number.

That's how relational databases *work* - you store the data once, and
once only; and then use Queries to combine data from the various
tables containing the fields that you need.

It's a very common mistake to assume that data must all be in one
table in order to create a report - but it *IS* a mistake; reports are
almost always based on queries, very commonly multitable queries.

John W. Vinson[MVP]

Thank you very much.
How about a form, is the form is from the table? the database right now I am
using, the form is all from a query.

"John Vinson" wrote:

On Tue, 23 Jan 2007 12:16:01 -0800, Hong
wrote:

I think the reason why they have the Parttype in table3 is because when they
create the report, they sum the number by by part type. So if in the access
design I shouldn't have this field in another table, but what should I do for
the report?

Base the Report *ON A QUERY* joining the two tables, joining on the
part number.

That's how relational databases *work* - you store the data once, and
once only; and then use Queries to combine data from the various
tables containing the fields that you need.

It's a very common mistake to assume that data must all be in one
table in order to create a report - but it *IS* a mistake; reports are
almost always based on queries, very commonly multitable queries.

John W. Vinson[MVP]

On Wed, 24 Jan 2007 08:26:01 -0800, Hong
wrote:

Thank you very much.
How about a form, is the form is from the table? the database right now I am
using, the form is all from a query.

I don't understand your question, Hong.

A Query is simply a way to select records and fields from a table,
sort them, and so on.

You can base a Form on a Query just as easily as on a Table, and it's
very frequently a good idea. Updating the Query updates the underlying
table.

Could you explain what you mean by "How about a form"?


John W. Vinson[MVP]

Thank you John,

I am using access at work for reports.
So I am trying to understand it, and learn from it.
And the same time I feel some forms should work better base on the original
design.

"John Vinson" wrote:

On Wed, 24 Jan 2007 08:26:01 -0800, Hong
wrote:

Thank you very much.
How about a form, is the form is from the table? the database right now I am
using, the form is all from a query.

I don't understand your question, Hong.

A Query is simply a way to select records and fields from a table,
sort them, and so on.

You can base a Form on a Query just as easily as on a Table, and it's
very frequently a good idea. Updating the Query updates the underlying
table.

Could you explain what you mean by "How about a form"?


John W. Vinson[MVP]

On Thu, 25 Jan 2007 08:26:02 -0800, Hong
wrote:

Thank you John,

I am using access at work for reports.
So I am trying to understand it, and learn from it.
And the same time I feel some forms should work better base on the original
design.

If you have a question about making them work better, please post that
question, perhaps in a new thread so someone else will be encouraged
to contribute.

John W. Vinson[MVP]