assuring values

[jlute]

Hi, there!

I have a subform in datasheet view. Its record source is a "many"
table that's in a "one" relationship. I need to assure that [Use] in a
given recordset can never contain more than one "CS", "UN" or "PT".
For example:

ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |

Does anyone have any ideas? Thanks for your consideration!

 

[Marshall Barton]

I have a subform in datasheet view. Its record source is a "many"
table that's in a "one" relationship. I need to assure that [Use] in a
given recordset can never contain more than one "CS", "UN" or "PT".
For example:

ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |

Does anyone have any ideas? Thanks for your consideration!

What recordset?

What is the relationship between the one table and the many
table?

Which table contains the ID and Use fields?

 

[jlute]

Hi, Marsh!

What recordset?

I may be using that term improperly. I meant a set of records that
relate to one record.

What is the relationship between the one table and the many
table?

The one (parent) table contains one record to the many (child) table.

Which table contains the ID and Use fields?

The child table contains them. The example I gave represents it:
ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |

For example CS, UN, PT can be assigned to no more than one ID. Many
ID's can have a Null [Use]. Otherwise I would simply make [Use] and
[ID] primary keys.

 

[Marshall Barton]

I may be using that term improperly. I meant a set of records that
relate to one record.

Does that mean all the records currently displayed in the
subform? If so, is that the complete set of records in the
many table?

The one (parent) table contains one record to the many (child) table.

Come on, that's what one and many means. I am trying to
help, but you really should give some clues about what
identifies a child record as "belonging" to a parent record.
If we could use real table and field names here it would
eliminate all this fuzzy phraseology.

The child table contains them. The example I gave represents it:
ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |

Which of those fields is the foreign key field?

Does the subform control specify anything in the Link
Master/Chiid Fields properties?

For example CS, UN, PT can be assigned to no more than one ID. Many
ID's can have a Null [Use]. Otherwise I would simply make [Use] and
[ID] primary keys.

Your example has a different ID for each record so I don't
see how that demonstrates what you are asking about.

Have you tried setting a compound index on those two fields
with the Ignore Nulls property set to Yes? If so, why is it
insufficient?

If that won't do it and from what I can tell, your question
does not have a simple generic answer. I really need to
understand the details of how a parent record links to its
child records so I can try to come up with an approach the
fits your situation.

If the subform records might be filtered, the solution may
be different than if **all** of the parent record's child
records are guaranteed to be displayed in the subform.

 

[jlute]

Does that mean all the records currently displayed in the

subform?  If so, is that the complete set of records in the
many table?

Big 10-4.

Come on, that's what one and many means.  I am trying to
help, but you really should give some clues about what
identifies a child record as "belonging" to a parent record.
If we could use real table and field names here it would
eliminate all this fuzzy phraseology.

Allow me to shave the fuzz however I'd prefer not to use the real
field names.

tblParents
ParentID (PK)

tblParentsKids
ParentID (PK)
KidID (PK)
Use

Resulting in:
KidID      | Use
123        | CS
224        |
344        |
459        | UN
578        | PT
666        |

Does the subform control specify anything in the Link
Master/Chiid Fields properties?

The Master/Child fields are ParentID.

For example CS, UN, PT can be assigned to no more than one ID. Many
ID's can have a Null [Use]. Otherwise I would simply make [Use] and
[ID] primary keys.

Your example has a different ID for each record so I don't
see how that demonstrates what you are asking about.

Have you tried setting a compound index on those two fields
with the Ignore Nulls property set to Yes?  If so, why is it
insufficient?

I'm not sure exaclty what you mean by compound index.

If that won't do it and from what I can tell, your question
does not have a simple generic answer.  I really need to
understand the details of how a parent record links to its
child records so I can try to come up with an approach the
fits your situation.

I hope the above is less fuzzy!

 

[Marshall Barton]

Does that mean all the records currently displayed in the
subform?  If so, is that the complete set of records in the
many table?

Big 10-4.

Come on, that's what one and many means.  I am trying to
help, but you really should give some clues about what
identifies a child record as "belonging" to a parent record.
If we could use real table and field names here it would
eliminate all this fuzzy phraseology.

Allow me to shave the fuzz however I'd prefer not to use the real
field names.

tblParents
ParentID (PK)

tblParentsKids
ParentID (PK)
KidID (PK)
Use

Resulting in:
KidID      | Use
123        | CS
224        |
344        |
459        | UN
578        | PT
666        |

Does the subform control specify anything in the Link
Master/Chiid Fields properties?

The Master/Child fields are ParentID.

For example CS, UN, PT can be assigned to no more than one ID. Many
ID's can have a Null [Use]. Otherwise I would simply make [Use] and
[ID] primary keys.

Your example has a different ID for each record so I don't
see how that demonstrates what you are asking about.

Have you tried setting a compound index on those two fields
with the Ignore Nulls property set to Yes?  If so, why is it
insufficient?

I'm not sure exaclty what you mean by compound index.

If that won't do it and from what I can tell, your question
does not have a simple generic answer.  I really need to
understand the details of how a parent record links to its
child records so I can try to come up with an approach the
fits your situation.

I think your many to many junction table, ParentKids, can
use the compound (multiple field) index as I suggested.
Just set it's Ignore Nulls property to Yes.

That might(?) not be an acceptable primary key, so, if it
matters (I don't think it does), then create an autonumber
field and use it as the PK.

 

[jlute]

I think your many to many junction table, ParentKids, can

use the compound (multiple field) index as I suggested.
Just set it's Ignore Nulls property to Yes.

Thanks, Marsh. When you first mentioned it I explored it in the Help
file but couldn't find it. Where do I find this property...? It's news
to me and I can't seem to locate it.

That might(?) not be an acceptable primary key, so, if it
matters (I don't think it does), then create an autonumber
field and use it as the PK.

Yuck. The Ignore Nulls property seems an easier/cleaner solution if I
could find it!

 

[Marshall Barton]

Thanks, Marsh. When you first mentioned it I explored it in the Help
file but couldn't find it. Where do I find this property...? It's news
to me and I can't seem to locate it.


Yuck. The Ignore Nulls property seems an easier/cleaner solution if I
could find it!

It's not an either/or. I think using Ignore Nulls could be
the answer to your question. Whether you have a primary
key, autonumber or something else, is not part of the
current question.

Maybe you are not looking in the right Help. Most
everything beyond basic actions is explained in VBA Help.

 

[jlute]

Ok. I'm on track now. I changed the property in the table index but
I'm not getting the desired results.

Going back to my original statement:
I need to assure that [Use] in a given recordset can never contain
more than one "CS", "UN" or "PT".
For example:
ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |

In other words, an ID's [Use] can be Null or CS, UN, or PT but there
can never be more than one ID as a CS, UN, or PT. [Use] serves as a
descriptor and "marker" in that a certain query will return other info
associated to an ID that has a [Use] value.

I see now that the IgnoreNulls doesn't directly address the challenge.
It will ignore nulls but it won't assure that only one ID is a CS. I'm
not suer that the autonumber field resolves this, either...?

Maybe write code that creates a string to check for existing [Use]
values...?

For the sake of curiosity here's the SQL for the subform. Not sure if
it helps.

SELECT tblPKWeightCalcs.Group, InStr("Product Unit Case UnitLoad",
[Category]) AS Cat, InStr("UN CS PT",[Use]) AS Us,
tblPKWeightCalcs.PKID, tblPKWeightCalcs.Category,
tblPKWeightCalcs.Use, tblProfiles.Description,
tblPKWeightCalcs.SubUnitQty, tblPKWeightCalcs.UnitQty,
tblPKWeightCalcs.txtProfileID,
Round(DLookUp("gConv","qryPKWeightsConvg","ID='" & [PKID] & "'"),4) AS
IDWtg, tblPKWeightCalcs.Value, tblPKWeightCalcs.UOM,
tblPKWeightCalcs.Weightg, tblPKWeightCalcs.Comments, Nz([IDWtg],
[Weightg]) AS WtNz, IIf(IsNull([UnitQty]),[Weightg],[IDWtg]*[UnitQty])
AS TotalWtg
FROM tblPKWeightCalcs LEFT JOIN tblProfiles ON tblPKWeightCalcs.PKID =
tblProfiles.txtProfileID
ORDER BY tblPKWeightCalcs.Group, InStr("Product Unit Case UnitLoad",
[Category]), InStr("UN CS PT",[Use]) DESC , tblPKWeightCalcs.PKID;

 

[Marshall Barton]

After rereading all your posts, I was confused. I think
itwas because your example had only an ID field, when you
really have a KidID and ParentID fields.

What I should have said is to make another compound index on
the ParentID and Use fields and set its Unique and Ignore
Nulls properties to Yes.

Your existing primary key is the compound index ParentID and
KidsID and has NOTHING to do with this question so leave it
the way you had it.

 

[jlute]

What I should have said is to make another compound index on

the ParentID and Use fields and set its Unique and Ignore
Nulls properties to Yes.

I'm sorry. I'm not clear on what you mean by making another compound
index. Do you mean to open the table indexes and add [Use]? If so,
I've tried that and setting the Ignore Nulls is permitted but setting
the Unique to yes results in "duplicate records". I double checked
things and don't have any duplications of [Use]. Every set of child
records has only one child that's either UN, CS, or PT.

Thanks for your patience. I know this is a trying one!

 

[Marshall Barton]

What I should have said is to make another compound index on
the ParentID and Use fields and set its Unique and Ignore
Nulls properties to Yes.

I'm sorry. I'm not clear on what you mean by making another compound
index. Do you mean to open the table indexes and add [Use]? If so,
I've tried that and setting the Ignore Nulls is permitted but setting
the Unique to yes results in "duplicate records". I double checked
things and don't have any duplications of [Use]. Every set of child
records has only one child that's either UN, CS, or PT.

I think Access is making the easy things so easy you are not
learning how it works. Your primary key is a compound (two
fields) index. I am suggesting that you create another
index with two fields. I gather that you created the PK by
just selecting the ParentID and KidsID and clicking the
convenient Edit menu's Primary Key item.

The "real" way to create indexes is to use the Indexes
window. Select an empty row and enter an arbitrary name for
the new index and select the ParentID field. On the next
row, without entering an index name, select the Use field.
Then click on the index name and go to the index properties
section of the window and set Unique and Ignore Nulls to
Yes.

 

[jlute]

I think Access is making the easy things so easy you are not

learning how it works.

 Your primary key is a compound (two
fields) index.

Well, "compound index" is either your own personal term or it's an
Access one that I couldn't find in the Help file. Sorry, it's a new
term to me and I wasn't sure what you meant. Access calls this a
"multiple-field index" and I'm very familiar with that as my database
has over 300 hundred tables.

 I am suggesting that you create another
index with two fields.  I gather that you created the PK by
just selecting the ParentID and KidsID and clicking the
convenient Edit menu's Primary Key item.

Yes. And I would guess that that's perhaps how most of us typical,
every day users do it.

The "real" way to create indexes is to use the Indexes
window.

Then maybe Access should remove the convenient Edit menu's primary Key
item as it's contributing more to the ignorance of users than it is to
its intended convenience

 Select an empty row and enter an arbitrary name for
the new index and select the ParentID field.  On the next
row, without entering an index name, select the Use field.
Then click on the index name and go to the index properties
section of the window and set Unique and Ignore Nulls to
Yes.

At first I was still unclear after I read this. I understood "Then
click on the index name..." as clicking on the one for Use which, of
course results in no properties as it doesn't have an index name.
After some tinkering I realized that you meant to click on the index
name of the ParentID. I did so and tested the subform and....it works!

Thanks, Marsh. I'll consider doing things the "real" way next time!

This really was an eye-opener. I had no idea that an additional index
could be made like this.

 

[Marshall Barton]

I'm not so sure that I should be insulted by that <g>

Don't be. I was only trying to add a little levity to my
frustration ;-)

Well, "compound index" is either your own personal term or it's an
Access one that I couldn't find in the Help file. Sorry, it's a new
term to me and I wasn't sure what you meant. Access calls this a
"multiple-field index" and I'm very familiar with that as my database
has over 300 hundred tables.

You're right, it's not an Access term. I've heard lots of
folks use it and just fell into the habit too.

Uh oh. Three hundred tables is more than a little scary.
Makes me think you have an incredibly complex process to
model or those tables could use a whole bunch of
normalization.

Yes. And I would guess that that's perhaps how most of us typical,
every day users do it.

Sure, me too ;-)

Then maybe Access should remove the convenient Edit menu's primary Key
item as it's contributing more to the ignorance of users than it is to
its intended convenience

Let's not get carried away now ;-) But it's still a good
idea to know how to do more than click on a menu/tool bar
item ;-)

At first I was still unclear after I read this. I understood "Then
click on the index name..." as clicking on the one for Use which, of
course results in no properties as it doesn't have an index name.
After some tinkering I realized that you meant to click on the index
name of the ParentID. I did so and tested the subform and....it works!

That is good news.

Thanks, Marsh. I'll consider doing things the "real" way next time!

Only when needed. Convenient time saver shortcuts are a
good thing ... (unless they get in the way like most
wizards).

This really was an eye-opener. I had no idea that an additional index
could be made like this.

Indexes are an essential and powerful part of any decent
database. Well worth the time to understand where and why
they should be used. Your problem is a great example since
it was solved with only a few clicks and without any
complicated form, query or programming, And, because this
data rule is part of the table, it can not be bypassed
regardless of how/where the data is entered. Also keep in
mind that indexes can have an astonishing impact on
performance.

 

[jlute]

Uh oh.  Three hundred tables is more than a little scary.

Makes me think you have an incredibly complex process to
model or those tables could use a whole bunch of
normalization.

Believe it or not I just did a bunch of normalizing. I eliminated over
50 tables. It's a database that contains a few complex processes. I
may or may not have everything as eloquent as they could/should be but
it works very well and captures all of the environment.

But it's still a good
idea to know how to do more than click on a menu/tool bar
item  ;-)

No doubt! Thanks for the tutorial!

Indexes are an essential and powerful part of any decent
database.  Well worth the time to understand where and why
they should be used.  Your problem is a great example since
it was solved with only a few clicks and without any
complicated form, query or programming,  And, because this
data rule is part of the table, it can not be bypassed
regardless of how/where the data is entered.  Also keep in
mind that indexes can have an astonishing impact on
performance.

Great points. The only thing is that when an entry in the form
violates the index Access prompts one of those long and cryptic error
messages. If a user doesn't know Access then the message will throw
them. I'm going to leave things as they are at the moment however and
address that in the future should it become a problem.

Thanks again for your help!