adding records to table from text box values

[Shannan Casteel via AccessMonster.com]

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplaced). The
ClaimNumber is an autonumber and the primary key in both tables. I made a
one to one relationship between the two tables. I have a form for the parts.
It includes 25 text boxes for both the part numbers and the quantities, so 50
total. I set the control sources for each of the part number text boxes to
PartNumber from the PartsReplaced table. I set the control sources for each
of the quantity text boxes to QuantityReplaced from the PartsReplaced table.

When I enter a part number and press tab to enter the quantity, every part
number text box on the page changes to match the first. In other words, if I
enter "1234" in the first box all other 24 text boxes change to read "1234".
The same thing happens for the quantities.

Should I make the relationship one - to - many? Not really sure how to do
this...

I need to be able store up to 25 parts with their respective quantities for
each warranty claim I will have. I will have run reports to tell me which
parts have been entered the most, as well as what parts were replaced for
each respective claim.

Please help...Thanks for your time,

Shannan
--
Message posted via http://www.accessmonster.com

[Phil Stanton]

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date, Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescription.
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced.
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need to
set the LinkMasterRecord and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil
"Shannan Casteel via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.com...

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplaced). The
ClaimNumber is an autonumber and the primary key in both tables. I made a
one to one relationship between the two tables. I have a form for the
parts.
It includes 25 text boxes for both the part numbers and the quantities, so
50
total. I set the control sources for each of the part number text boxes
to
PartNumber from the PartsReplaced table. I set the control sources for
each
of the quantity text boxes to QuantityReplaced from the PartsReplaced
table.

When I enter a part number and press tab to enter the quantity, every part
number text box on the page changes to match the first. In other words,
if I
enter "1234" in the first box all other 24 text boxes change to read
"1234".
The same thing happens for the quantities.

Should I make the relationship one - to - many? Not really sure how to do
this...

I need to be able store up to 25 parts with their respective quantities
for
each warranty claim I will have. I will have run reports to tell me which
parts have been entered the most, as well as what parts were replaced for
each respective claim.

Please help...Thanks for your time,

Shannan
--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecord and
LinkSlaveRecord stuff?

Thanks,
Shannan

Phil Stanton wrote:

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date, Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescription.
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced.
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need to
set the LinkMasterRecord and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplaced). The

[quoted text clipped - 29 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

Also, your probably right., but I'm not a big fan of subforms. I would
rather have the user press a button taking them to another form where they
can enter the part numbers then go back to the main form.

Shannan

Shannan Casteel wrote:

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecord and
LinkSlaveRecord stuff?

Thanks,
Shannan

Not the way to do it I suspect. I would suggest:-

[quoted text clipped - 27 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

Shannan Casteel via AccessMonster.com wrote:

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly, combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors, cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Phil Stanton]

I was just thinking of using a combobox on each line of the subform for
inputting the parts. Didn't realise you had 100,000 possible parts. How on
earth do you get the correct ones? As is mentioned in Tim's posting I should
imagine there are some groupings which can be applied, so that possibly you
would need 2 combo boxes on each line, the first to select the categoy, and
the second would display only those part within that category.

The LinkMasterRecord and LinkSlaveRecord are a couple of fields in the
subform. If you create the main form, create the subform then in design view
drag the subform onto the mainform, frequently these fields will sort
themselves out. If not there is a builder ( ...) at the side of these fields
and they should offer you the PartID as the link.

The trouble with having boxes for 25 returns is what happens on that
terrible day when you get 26 parts to exchange? Redesign? Not a good idea.

Phil
"Shannan Casteel via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.com...

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the parts? I
have
about 100,000 parts to choose from. I don't think a combo box would be
feasable. Also how do you change all the LinkMasterRecord and
LinkSlaveRecord stuff?

Thanks,
Shannan

Phil Stanton wrote:

Not the way to do it I suspect. I would suggest:-

One table for the Claim:- TblClaim - ClaimID, ClaimReference Date,
Claimant
etc
One table to list all the parts:- TblParts - PartID, PartNumber,
PartDescription.
One table to link them together:- JoinClaimPart - ClaimID, PartID,
QtyReplaced.
Set the ClaimID and PartID as joint keys with no duplicates allowed. This
will stop a part being entered twice for any claim.

You then have a Main form for the Claim and on it a subform for the Parts
Replaced and quantity replaced. Use a combo box to select the parts
replaced. You might as well set the default quantity to 1. You also need
to
set the LinkMasterRecord and LinkSlaveRecord to PartID

Using a query based on the Parts and JoinClaimPart tables you will be able
to get the statistics.

It will also allow you to use as many or as few parts as you want for each
claim

HTH

Phil

I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplaced).
The

[quoted text clipped - 29 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

After reading several replies, I don't think I'm making clear what exactly it
is that I need.
Let me explain...

I have a database for warranty claims. We reimburse according to labor and
parts. For the parts section of the database, all I want is to be able to
enter any number such as "987978A1" along with its quantity, let's say 2 for
this example, and if on another claim someone else lists this same part with
a quantity of 1, I need to see a report saying:

PartNo Quantity
987978A1 3

However, I need to be able to enter more than one part number on each claim.
I have no way of narrowing down parts. Although I've used Access to build a
rather complicated database in the past, I'm no expert, but I would think
this would be rather simple. (A group of text boxes that stores in the same
field in a table.)

I really appreciate all the time everyone has put forth. Thanks for your
help,

Shannan

Tim Marshall wrote:

What exactly did you mean by using a combo box to select the parts? I have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly, combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors, cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.

[Shannan Casteel via AccessMonster.com]

If I have 26 parts, I would just split it into 2 claims...bosses suggestion!

Phil Stanton wrote:

I was just thinking of using a combobox on each line of the subform for
inputting the parts. Didn't realise you had 100,000 possible parts. How on
earth do you get the correct ones? As is mentioned in Tim's posting I should
imagine there are some groupings which can be applied, so that possibly you
would need 2 combo boxes on each line, the first to select the categoy, and
the second would display only those part within that category.

The LinkMasterRecord and LinkSlaveRecord are a couple of fields in the
subform. If you create the main form, create the subform then in design view
drag the subform onto the mainform, frequently these fields will sort
themselves out. If not there is a builder ( ...) at the side of these fields
and they should offer you the PartID as the link.

The trouble with having boxes for 25 returns is what happens on that
terrible day when you get 26 parts to exchange? Redesign? Not a good idea.

Phil

Phil,

[quoted text clipped - 43 lines]

Shannan

--
Message posted via http://www.accessmonster.com

[Bob Quintal]

"Shannan Casteel via AccessMonster.com"
<fo***@AccessMonster.com> wrote in
news:53***********@AccessMonster.com:

Also, your probably right., but I'm not a big fan of subforms.
I would rather have the user press a button taking them to
another form where they can enter the part numbers then go
back to the main form.

Shannan

Whether or not you are a fan of subforms, they are the right
tool for this application. You could base your main form on a
tab control, with the claim info on one tab, and the subform for
the parts on the other.

And to answer a question you asked previously, your relationship
should be one to many, because you have one claim, and many
parts.

Shannan Casteel wrote:

Phil,

Thank you for your reply.

What exactly did you mean by using a combo box to select the
parts? I have about 100,000 parts to choose from. I don't
think a combo box would be feasable. Also how do you change
all the LinkMasterRecord and LinkSlaveRecord stuff?

Thanks,
Shannan

Not the way to do it I suspect. I would suggest:-

[quoted text clipped - 27 lines]

Shannan

--
Bob Quintal

PA is y I've altered my email address.

[Phil Stanton]

Sorry for delay in replying, boating this weekend.

So how do you expect to get reliable statistics of parts used if there is no
check that you have entered a valid part number. Can you guarantee to enter
987978A1 and not 987987A1 by mistake. As quite a few replies have
indicated, you will have a main form for the claimant, and each line of the
subform would have a different part number which is valid (Ok I agree you
could key in the incorrect part number, but it would still have to be a
valid one) and the quantity. You could also display the appropriate
description ( pulled from the part number table) for that part number, so
that the person doing the entry has a chance of checking whether it is a
sensible part.
If you also hold the prices on the parts table, all the material sums are
done automatically as well.
The whole thing depends on the JoinClaimPart table which appears
meaningless. What you would see is something like this
ClaimID PartID Qty
1 1476 2 This is claim 1 the 1476th item in the
parts table 2 of them
1 12 1 This is claim 1 the 12th item in the
parts table 1 of them
1 1111 3 This is claim 1 the 1111th item in the
parts table 3 of them
2 12 3 This is claim 2 the 12th item in the
parts table 3 of them
2 1111 2 This is claim 2 the 1111th item in the
parts table 2 of them

The parts used report is based only on the PartID and would look like this
PartID SumQty
12 4
1111 5
1467 2
By using the description and price in the parts table this could be expanded
to
PartID partName SumQty Price Total
12 Nut 4 £1.40 £5.60
1111 Screw 5 £0.70 £3.50
1467 Fan Belt 2 £3.40 £6.80
Total £15.90

And as I said, by having some form of grouping of the parts, this could be
for car warranty work
A similar report would be for Television repair work

Phil


"Shannan Casteel via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.com...

After reading several replies, I don't think I'm making clear what exactly
it
is that I need.
Let me explain...

I have a database for warranty claims. We reimburse according to labor
and
parts. For the parts section of the database, all I want is to be able to
enter any number such as "987978A1" along with its quantity, let's say 2
for
this example, and if on another claim someone else lists this same part
with
a quantity of 1, I need to see a report saying:

PartNo Quantity
987978A1 3

However, I need to be able to enter more than one part number on each
claim.
I have no way of narrowing down parts. Although I've used Access to build
a
rather complicated database in the past, I'm no expert, but I would think
this would be rather simple. (A group of text boxes that stores in the
same
field in a table.)

I really appreciate all the time everyone has put forth. Thanks for your
help,

Shannan

Tim Marshall wrote:

What exactly did you mean by using a combo box to select the parts? I
have
about 100,000 parts to choose from. I don't think a combo box would be
feasable.

Hopefully you have a separate table dealing with parts available and are
not, as it looks from your first post, though I may have read it
incorrectly, combining information into the one table.

Do you have some sort of classification or part type scheme to make
things easier for your users? Like electrical, building materials,
transistors, cleaning supplies, etc, etc. This might be a good idea and
would require another table:

Part_Types

TypeName
TypeId

This would lead to the requirement for a required field in your parts
table that holds the TypeId information.

If so, you can use multiple combo boxes to choose a part type, then an
actual part.

[Shannan Casteel via AccessMonster.com]

Phil,

Thanks for your reply....How was your weekend?

In regards to your post, Thank you for putting so much time into it. I still
don't think I'm making it clear as to what I want. I really don't care if an
invalid part number is entered. I can take care of that by other means. All
I need is a way to keep track of the number of times any text string has been
entered into these 25 text boxes, along with their associated quantities. I
do not have a table with all of the possible parts. I know this sounds weird,
but our company has bought a product line from another company. We also sell
parts for obsolete machines. It is a very complicated situation, but there
are processes in place that make it simple to select the correct part for the
customer........very hard to explain.

Anyway, thanks for your time.

Shannan Casteel

Phil Stanton wrote:

Sorry for delay in replying, boating this weekend.

So how do you expect to get reliable statistics of parts used if there is no
check that you have entered a valid part number. Can you guarantee to enter
987978A1 and not 987987A1 by mistake. As quite a few replies have
indicated, you will have a main form for the claimant, and each line of the
subform would have a different part number which is valid (Ok I agree you
could key in the incorrect part number, but it would still have to be a
valid one) and the quantity. You could also display the appropriate
description ( pulled from the part number table) for that part number, so
that the person doing the entry has a chance of checking whether it is a
sensible part.
If you also hold the prices on the parts table, all the material sums are
done automatically as well.
The whole thing depends on the JoinClaimPart table which appears
meaningless. What you would see is something like this
ClaimID PartID Qty
1 1476 2 This is claim 1 the 1476th item in the
parts table 2 of them
1 12 1 This is claim 1 the 12th item in the
parts table 1 of them
1 1111 3 This is claim 1 the 1111th item in the
parts table 3 of them
2 12 3 This is claim 2 the 12th item in the
parts table 3 of them
2 1111 2 This is claim 2 the 1111th item in the
parts table 2 of them

The parts used report is based only on the PartID and would look like this
PartID SumQty
12 4
1111 5
1467 2
By using the description and price in the parts table this could be expanded
to
PartID partName SumQty Price Total
12 Nut 4 £1.40 £5.60
1111 Screw 5 £0.70 £3.50
1467 Fan Belt 2 £3.40 £6.80
Total £15.90

And as I said, by having some form of grouping of the parts, this could be
for car warranty work
A similar report would be for Television repair work

Phil

After reading several replies, I don't think I'm making clear what exactly
it

[quoted text clipped - 51 lines]

If so, you can use multiple combo boxes to choose a part type, then an
actual part.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Tim Marshall]

Shannan Casteel via AccessMonster.com wrote:

If I have 26 parts, I would just split it into 2 claims...bosses suggestion!

No. If this is the approach you're going to take, forget using a
database, because you simply aren't using the power of a database to do
the job.

You sound like you're much better off using Excel.

However, if you do wish to do this properly, Phil's first suggested data
structure on his reply on the 19th is the proper way to go.

You said you had about 100,000 parts to choose from which made it sound
like you have a specific inventory of parts. If this is not the case,
ie, if you just take whatever a garage tells you without worry of
verifying the existence of a part (which is a legitimate business
model), then instead of the three tables, you just need the two you
described in your original post. However, a 1:1 relation ship is not
appropriate - you need a one to many - and your structure is grossly
incorrect.

A record on a claim table should be able to be linked to any number of
records on the parts table. What you've done is create a parts table
with 25 separate fields for up to 25 parts for a claim. This is a
common enough mistake for people used to using spreadsheetsd like Excel
and for whom relational database structures are unfamiliar. But it's a
very very bad way of doing things.

I'll say it again, it's a very, very very <insert favourite explicative>
BAD way of doing it.

The proper way of doing things is a ONE TO MANY RELATIONSHIP, ie, one
record from the claims table to many records of the parts table.

A parts table record should have one and only one part information per
record.

You would need a foreign key field in the parts table which contains the
ClaimNumber you're sing as the PK in the claims table.

It doesn't matter a hoot if you're supposed to have 25 parts to a claim
or not, the above is the correct way to structure things.

Remember, there are ALWAYS at least two purposes for a database. The
first is to fulfill the operational requirements of the business. In
your case, that is getting claim information together. The second
purpose, which is so often forgotten, is that a database serves as a
history of the activity of the business which can be used as an analysis
tool. To answer questions such as, for example, in your business:

* How do total costs of claims compare from year to year?

* Are there any trends in costs throughout a year?

* How much is a particular client costing us and has his pattern
changed over time?

* How often are the garages using a partiicular part/service (which
makes a very strong case for having a table of specific parts/services
as per Phil's original response to you).

Presumeably there is cost information on a part. Using the VERY BAD
method you proposed in your post, how do you tally total costs for a
bunch of claims? You have to come up with a way to total the costs per
claim and then total the claims for a specific period. What if you want
to investigate a specific part? Hmmm, it might be in field 1 here,
field 24 there, etc, etc. A total nightmare. Crumbs, can you even
imagine trying that in Excel? So much easier to have ONE total cost per
record so that you can specify, when necessary, a part/service and pull
cost information from there.

My above points against your proposed database structure also apply to
making the case for Phil's original suggestion.

Databases are about making extraction of information efficient. 25
parts per record is not efficient and is more like a dinner party where
the host trys to carry all the plates for all his dinner guests at the
same time instead of bringing them out one at a time.

Sorry if I sounded harsh, I'm not trying to be. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Phil Stanton]

You're much politer than I am, Tim - you try to help the guy, and Shannan
just don't want to know.
I give up

Phil
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:de*********@coranto.ucs.mun.ca...

Shannan Casteel via AccessMonster.com wrote:

If I have 26 parts, I would just split it into 2 claims...bosses
suggestion!

No. If this is the approach you're going to take, forget using a
database, because you simply aren't using the power of a database to do
the job.

You sound like you're much better off using Excel.

However, if you do wish to do this properly, Phil's first suggested data
structure on his reply on the 19th is the proper way to go.

You said you had about 100,000 parts to choose from which made it sound
like you have a specific inventory of parts. If this is not the case, ie,
if you just take whatever a garage tells you without worry of verifying
the existence of a part (which is a legitimate business model), then
instead of the three tables, you just need the two you described in your
original post. However, a 1:1 relation ship is not appropriate - you need
a one to many - and your structure is grossly incorrect.

A record on a claim table should be able to be linked to any number of
records on the parts table. What you've done is create a parts table with
25 separate fields for up to 25 parts for a claim. This is a common
enough mistake for people used to using spreadsheetsd like Excel and for
whom relational database structures are unfamiliar. But it's a very very
bad way of doing things.

I'll say it again, it's a very, very very <insert favourite explicative>
BAD way of doing it.

The proper way of doing things is a ONE TO MANY RELATIONSHIP, ie, one
record from the claims table to many records of the parts table.

A parts table record should have one and only one part information per
record.

You would need a foreign key field in the parts table which contains the
ClaimNumber you're sing as the PK in the claims table.

It doesn't matter a hoot if you're supposed to have 25 parts to a claim or
not, the above is the correct way to structure things.

Remember, there are ALWAYS at least two purposes for a database. The
first is to fulfill the operational requirements of the business. In your
case, that is getting claim information together. The second purpose,
which is so often forgotten, is that a database serves as a history of the
activity of the business which can be used as an analysis tool. To answer
questions such as, for example, in your business:

* How do total costs of claims compare from year to year?

* Are there any trends in costs throughout a year?

* How much is a particular client costing us and has his pattern changed
over time?

* How often are the garages using a partiicular part/service (which
makes a very strong case for having a table of specific parts/services as
per Phil's original response to you).

Presumeably there is cost information on a part. Using the VERY BAD
method you proposed in your post, how do you tally total costs for a bunch
of claims? You have to come up with a way to total the costs per claim
and then total the claims for a specific period. What if you want to
investigate a specific part? Hmmm, it might be in field 1 here, field 24
there, etc, etc. A total nightmare. Crumbs, can you even imagine trying
that in Excel? So much easier to have ONE total cost per record so that
you can specify, when necessary, a part/service and pull cost information
from there.

My above points against your proposed database structure also apply to
making the case for Phil's original suggestion.

Databases are about making extraction of information efficient. 25 parts
per record is not efficient and is more like a dinner party where the host
trys to carry all the plates for all his dinner guests at the same time
instead of bringing them out one at a time.

Sorry if I sounded harsh, I'm not trying to be. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Phil Stanton wrote:

You're much politer than I am, Tim - you try to help the guy, and Shannan
just don't want to know.

That could well be, but I just figured he's just having trouble with the
relational model way of doing things. Several of the other managers I
work with are Excel people and they are used to my lecturing them about
why spreadsheets are not supposed to be for storing data. One decided a
number of years ago to design an Access 97 app to keep track of
purchasing and all were full of praise about it. The only thing is...
there was just one table. Each purchase order record could have up to
12 individual records, plus a plethora of many other gaffs. Sounds like
Shannon's design and it has caused us no end of trouble with respect to
analysis of trends - in fact it's impossible to do it logically without
vast and com,plex VBA functions (there were no form or data level
contraints on what could be entered).

I'm hoping we can direct Shannon away from this path. He's already been
told in another thread the design described in the OP of this thread
sucks the big one, hopefully the message will get through. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me