Design Help???

[Guest]

Ok guys, How should I go about doing this? I have to create a Drawing
Database which I will assign drawing numbers and give descriptions of the
drawings. The thing is. There will be 9 different types of Drawing. Assembly,
Weldments, Parts, Installation, Schematic, Layout, Spec's, Calculation,
General. Each will be given a special number, example. Assembly = 7151000,
7151001; Parts = 7351000,7351001. What I want to do is create a form for this
purpose. I would like to autogenerate the numbers upon a combo box selection
of type of Drawing and autofill the Drawing Number Text box with that number.
Any suggestions would be greatly appreciated.

 

[Guest]

Randomize and RND statements in VB.

 

[TC]

Steve, you need to ensure the table design is correct, before you worry
about the forms & reports.

For example: do you need to record a drawing /structure/, as in a bill
of materials? If so, you will need to allow for this in your table
designs.

I suggest that you tell us the fields that you propose for each of your
tables. We only need their names, not their types & lengths. It is also
critically important to select the right *primary key* field(s) for
each table. If you don't know what a primary key is, you need to stop
right here, and go research that term, before you even *think* of going
any further with the forms & reports.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[John Vinson]

Ok guys, How should I go about doing this? I have to create a Drawing
Database which I will assign drawing numbers and give descriptions of the
drawings. The thing is. There will be 9 different types of Drawing. Assembly,
Weldments, Parts, Installation, Schematic, Layout, Spec's, Calculation,
General. Each will be given a special number, example. Assembly = 7151000,
7151001; Parts = 7351000,7351001. What I want to do is create a form for this
purpose. I would like to autogenerate the numbers upon a combo box selection
of type of Drawing and autofill the Drawing Number Text box with that number.
Any suggestions would be greatly appreciated.

It sounds like your numbers are "Intelligent Keys" - and that's not a
compliment. If the 715 prefix means an Assembly and 735 means Parts,
you should REALLY consider changing your table design. Storing two
disparate pieces of information (a Drawing Type and a sequential
number) jammed into one field is simply NOT a good idea. You'll do
much better to use *two* fields, which can constitute a joint
two-field Primary Key and can be concatenated for display purposes if
you wish.

John W. Vinson[MVP]

 

[Guest]

It sounds like your numbers are "Intelligent Keys" - and that's not a
compliment. If the 715 prefix means an Assembly and 735 means Parts,
you should REALLY consider changing your table design. Storing two
disparate pieces of information (a Drawing Type and a sequential
number) jammed into one field is simply NOT a good idea. You'll do
much better to use *two* fields, which can constitute a joint
two-field Primary Key and can be concatenated for display purposes if
you wish.

John W. Vinson[MVP]

no, John, numbers are not intelligent keys. I'm not planning to store these
together. I'm in the design stages of this and I'm looking for the smart and
right way to design this, so in the future we dont have problems.

TC, not much to this. Basically the base of all the tables will be: KeyId,
Drawing Number, description, Comments. My first thought was to create
different tables for each of the different types of drawings. There really
isn't going to be any relationship between these Tables. I will be bring them
together on a subform for viewing a seaching information. Primary Key on all
the Tables will be the Drawing number. I havent as yet started anything just
planning.

Thanks guys for your input.
Steve

 

[John Vinson]

no, John, numbers are not intelligent keys. I'm not planning to store these
together. I'm in the design stages of this and I'm looking for the smart and
right way to design this, so in the future we dont have problems.

Ok... I'm not understanding then.

You say:

There will be 9 different types of Drawing. Assembly,
Weldments, Parts, Installation, Schematic, Layout, Spec's,
Calculation, General. Each will be given a special number, example.
Assembly = 7151000, 7151001; Parts = 7351000,7351001.

That certainly sounds like Assembly records have DrawingNumber
starting with 715, and Parts start with 735.

That's storing two pieces of information - the "715" meaning "this
record is an Assembly", and the "1001" meaning "this is the first
Assembly record".

Evidently I'm misunderstanding. What DID you mean by the above?

TC, not much to this. Basically the base of all the tables will be: KeyId,
Drawing Number, description, Comments. My first thought was to create
different tables for each of the different types of drawings. There really
isn't going to be any relationship between these Tables. I will be bring them
together on a subform for viewing a seaching information. Primary Key on all
the Tables will be the Drawing number. I havent as yet started anything just
planning.

Your design will be a LOT better if you have *ONE* table of Drawings,
with a field indicating the drawing type. You can use this field with
a search criterion to display only one particular type of drawing.
Having nine basically identical tables, one for each type of drawing,
will NOT be a good design.

John W. Vinson[MVP]

 

[Guest]

Ok... I'm not understanding then.

You say:

There will be 9 different types of Drawing. Assembly,
Weldments, Parts, Installation, Schematic, Layout, Spec's,
Calculation, General. Each will be given a special number, example.
Assembly = 7151000, 7151001; Parts = 7351000,7351001.

That certainly sounds like Assembly records have DrawingNumber
starting with 715, and Parts start with 735.

That's storing two pieces of information - the "715" meaning "this
record is an Assembly", and the "1001" meaning "this is the first
Assembly record".

Evidently I'm misunderstanding. What DID you mean by the above?


Your design will be a LOT better if you have *ONE* table of Drawings,
with a field indicating the drawing type. You can use this field with
a search criterion to display only one particular type of drawing.
Having nine basically identical tables, one for each type of drawing,
will NOT be a good design.

John W. Vinson[MVP]

Thanks for you suggestions, Well I havent decided on 9 different tables,
just a thought. If I have one table, one field being a lookup field for the
differnet types of drawing, How do I keep track of the Drawing numbers when
they are being incrementing? Being there will be 9 different numbers I need
to keep track of?


Thannks,
Steve

 

[John Vinson]

Thanks for you suggestions, Well I havent decided on 9 different tables,
just a thought. If I have one table, one field being a lookup field for the
differnet types of drawing, How do I keep track of the Drawing numbers when
they are being incrementing? Being there will be 9 different numbers I need
to keep track of?

I'd REALLY recommend avoiding the Lookup Wizard. It's of *very*
limited utility and causes a great deal of confusion (see a critique
at http://www.mvps.org/access/lookupfields.htm); and it is NOT
necessary since you can always put a Combo Box on a form, with or
without a lookup defined.

Could you explain what the "9 different numbers" are? Are you perhaps
making the incorrect assumption that you can search only by one field?

John W. Vinson[MVP]

 

[Guest]

My appologies for not explaining myself more clearly. I am planning to use a
combo box not a lookup field. No I know you can seach muliple fields. Mainly
this:

Designer needs a number for an Assembly. So We will assign the next available
number to his drawing. Anything that is an Assembly will get the number
7151000 and the next Assembly will get 715001 so on and so on. Another
designer need a number for Weldment, then we will assign that drawing 7251000
next
Weldment will get 7251001 so on and so on.

7151000 (Assembly)
7251000 (Weldment)
7351000 (Part)
7451000 (Installation)
7551000 (Schematic)
7651000 (Layout)
7751000 (Specification)
7851000 (Calculation)
7951000 (General Arrangement)

I need to keep track of what drawing has what number and a description and
a comment if needed.

thanks for your help
Steve

 

[John Vinson]

My appologies for not explaining myself more clearly. I am planning to use a
combo box not a lookup field. No I know you can seach muliple fields. Mainly
this:

Designer needs a number for an Assembly. So We will assign the next available
number to his drawing. Anything that is an Assembly will get the number
7151000 and the next Assembly will get 715001 so on and so on. Another
designer need a number for Weldment, then we will assign that drawing 7251000
next
Weldment will get 7251001 so on and so on.

We're talking past each other.

YOUR DESIGN IS WRONG.

I'm sorry, but it simply is *wrong*.

The manner in which it is wrong is that you are using *ONE* field -
the Drawing number - for two different purposes. The first three
digits of the drawing number identify the type of drawing; the next
four digits identify *which* Assembly, or Part, or whatever drawing
this is.

It is *incorrect design* to use one field for two purposes. It makes
your data entry more complex, it makes searching more complex, it
reduces the efficiency of your database, it makes linking to related
tables more difficult.

My suggestion would be to use *TWO* fields: a DrawingType field, with
values 715, 725, and so on; this could be linked to a DrawingTypes
table, with fields DrawingType and Description:

715 Assembly
725 Weldment

<and so on>

The second field would be a SeqNo field, Long Integer, with values 1,
2, 3, 4, and so on (or 1001, 1002 and so on, if you want the redundant
digit).

You could select these two fields in table design view and click the
Key icon to make them a joint two-field Primary Key; you could
concatenate them in a Query for display; and you could assign a new
SeqNo value in a Form in the AfterUpdate event of the DrawingType
combo:

Private Sub cboDrawingType_AfterUpdate()
If IsNull(Me!txtSeqNo) Then
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[DrawingTable]", _
"[DrawingType] = " & Me!cboDrawingType)) + 1
Else
MsgBox "This drawing already has a type!", vbOKOnly
End If
End Sub

You may want to change what happens if the user tries to change a
Weldment drawing to an Assembly drawing just by changing the type, I
don't know what your business rules might be.

John W. Vinson[MVP]

 

[TC]

Steve, I've done a number of technical dwg systems, and I have not
struck many where DwgID would be usable as a primary key in all, or
even most, of the relevant tables.

For example: do your dwgs have multiple sheets? Do you need to identify
those sheets individually? Can the dwgs be revised? Do you need to
track those revisions? Do you need to track the distribution of
particular dwg [sheet [revision ] ] s to particular recipients?

If you answer yes to *any* of those questions, then, you have at least
one table where DwgID will *not* serve as the primary key.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Thanks John, I'll take your advise. I appreciate you help in this.

Steve

My appologies for not explaining myself more clearly. I am planning to use a
combo box not a lookup field. No I know you can seach muliple fields. Mainly
this:

Designer needs a number for an Assembly. So We will assign the next available
number to his drawing. Anything that is an Assembly will get the number
7151000 and the next Assembly will get 715001 so on and so on. Another
designer need a number for Weldment, then we will assign that drawing 7251000
next
Weldment will get 7251001 so on and so on.

We're talking past each other.

YOUR DESIGN IS WRONG.

I'm sorry, but it simply is *wrong*.

The manner in which it is wrong is that you are using *ONE* field -
the Drawing number - for two different purposes. The first three
digits of the drawing number identify the type of drawing; the next
four digits identify *which* Assembly, or Part, or whatever drawing
this is.

It is *incorrect design* to use one field for two purposes. It makes
your data entry more complex, it makes searching more complex, it
reduces the efficiency of your database, it makes linking to related
tables more difficult.

My suggestion would be to use *TWO* fields: a DrawingType field, with
values 715, 725, and so on; this could be linked to a DrawingTypes
table, with fields DrawingType and Description:

715 Assembly
725 Weldment

<and so on>

The second field would be a SeqNo field, Long Integer, with values 1,
2, 3, 4, and so on (or 1001, 1002 and so on, if you want the redundant
digit).

You could select these two fields in table design view and click the
Key icon to make them a joint two-field Primary Key; you could
concatenate them in a Query for display; and you could assign a new
SeqNo value in a Form in the AfterUpdate event of the DrawingType
combo:

Private Sub cboDrawingType_AfterUpdate()
If IsNull(Me!txtSeqNo) Then
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[DrawingTable]", _
"[DrawingType] = " & Me!cboDrawingType)) + 1
Else
MsgBox "This drawing already has a type!", vbOKOnly
End If
End Sub

You may want to change what happens if the user tries to change a
Weldment drawing to an Assembly drawing just by changing the type, I
don't know what your business rules might be.

John W. Vinson[MVP]

 

[Guest]

Well we will not track rev's and sheets. Just assign a number and Inventor
Vault will track the rest. Hey any tips would be greatly appeciated.

Thanks
Steve

 

[TC]

Sorry, I'm not sure what else I could offer.

Cheers,
TC (MVP Access)
http://tc2.atspace.com