Mandatory Field

[Bill]

Hi All,

I have two related tables that are updated via a form. I need an item of
data in the second to be mandatory i.e.. Default Value only populates if
something else is entered but if nothing is entered I don't get an entry. If
nothing else is entered into the secondary table I would like the mandatory
value to be entered automatically.

I can't see how to do this. Any ideas?

Regards.
Bill.

 

[Douglas J. Steele]

Are you saying that when a row is inserted into table1, you want a row to
automatically be inserted into table2? Access doesn't work that way. If you
want an entry created, you have to explicitly create it. (Some other DBMS
allow you to create triggers so that when a row is inserted into table1, the
trigger will run and the code in it can insert a row into table2, but Access
doesn't support triggers)

 

[Bill]

Ummmm, I see what you mean!!
Regards.
Bill

Are you saying that when a row is inserted into table1, you want a row to
automatically be inserted into table2? Access doesn't work that way. If you
want an entry created, you have to explicitly create it. (Some other DBMS
allow you to create triggers so that when a row is inserted into table1, the
trigger will run and the code in it can insert a row into table2, but Access
doesn't support triggers)

 

[Bill]

Douglas,

Maybe I am doing about this all wrong.

I am trying to create a catalogue, that will be accessed via a menu system.
The number of layers in the menu might vary. At the moment I am working on
the assumption that the menu system will be three deep max, but sometimes
only two levels will be required. I am not entirely comfortable with how I
am trying to do this which is by having a table for each layer.

Any suggestions. I'm sure this is something that has been done before!

Regards.
Bill.

 

[Douglas J. Steele]

I'm not sure whether you're describing what's typically referred to as Bill
of Materials. If so, see whether
http://www.mvps.org/access/modules/mdl0027.htm and/or
http://www.mvps.org/access/queries/qry0023.htm at "The Access Web" is of any
use.

 

[Bill]

Thanks, I'll give them a look.
All the best.
Bill.

 

[Bill]

Sort if, but not quite.

Rather than a system that identifies components, the menus will lead to an
items.

For example.

Hardware>Screws>Exterior>1"
Hardware>Screws>Exterior>2"
etc
Hardware>Screws>Interior>1"
Hardware>Screws>Interior>2"
etc
Hardware>Bolts>3"
Hardware>Bolts>2"
etc
Drill Bits>Masonry>5mm
Drill Bits>Masonry>6mm
etc
Drill Bits>Wood>High Speed>3mm
Drill Bits>Wood>High Speed>4mm
etc

Each would result in a single item with a reference code and specification.

A CMDB is the sort of thing I guess.

Regards.
Bill.

 

[Dale Fye]

Bill,

Some would debate this, but what I think you need is a self referencing
table. When I build one of these (they work great for hierarchical data sets
such as employee trees, menu structures, site maps, ...). The advantage to
this type of structure is that you can nest your data as many levels deep as
you need to without having to worry about having a separate table for each
level.

ID - Primary key
Parent_ID - Identifies the Parent node
Node_Name - Short Name of the Item (in your case, this would be the various
menu level names down to the product at the lowest level)
Node_Desc - more detailed description of the menu item or product
Node_Type - In your case these might be ("Category", "Product")

Then you might have other fields as well. Alternately, you might just do
ID, Parent_ID, Node_Type) and then have separate tables for Categories and
Products, where you store the ID value from this table in the appropriate
table to store information about the specific Category or product.

This type of structure may be more difficult to query, but as you have found
out, it is not very easy to write queries for a multi-level system, where not
every menu element has the same depth, either.

HTH
Dale

 

[Bill]

Dale,

I think you have interpreted what I have been asking correctly. I will need
to go over what you have said very carefully before I know for sure and I
won't have the chance now for a couple of days.

Thanks again.
Bill