Can i use an 'OR' when created an index?i'm trying to prevent duplicat 1

[ruthcali]

i am using Access 97.

my table has the following fields:

Site1Name

Site1RowW1
Site1BayW1
Site1ShelfW1

Site1RowW2
Site1BayW2
Site1ShelfW2

Site1RowP1
Site1BayP1
Site1ShelfP1

Site1RowP2
Site1BayP2
Site1ShelfP2

Site2Name

Site2RowW1
Site2BayW1
Site2ShelfW1

Site2RowW2
Site2BayW2
Site2ShelfW2

Site2RowP1
Site2BayP1
Site2ShelfP1

Site2RowP2
Site2BayP2
Site2ShelfP2

The above are equipment locations and what i need is to make sure no locations can be duplicated. it doesn't matter if i enter the info under Site1 or Site2 and it doesn't matter if the info is entered under RowW1 or RowP2. i need an unique row/bay/shelf for the SiteNames.

So, the following would be an entry:
Site1Name: Building1
Site1RowW1:1
Site1BayW1:2
Site1ShelfW1:3

And the following could NOT be an entry:

Site2Name: Building1
Site2RowW1:1
Site2BayW1:2
Site2ShelfW1:3

Once SiteName: Building1/row:1/bay:1/shelf:1 is an entry, that combination cannot be entered for ANY other set of fields.

i went to the table and made a multiple index, but i could only figure out how to make sure noone entered the same data for site1. How can i make it so i don't care if the info is entered into Site1 or Site2, i want an unique set of records for the fields SiteName, SiteRow, SiteBay, SiteShelf.

hope i was clear. thanks in advance for your help!

 

[jimmythegeek]

To make a multiple index,

Go to the design of your table
Open the index window (View | Indexes) or (Button with lightining bolt)
Create a name you want to call this index (doesn't really matter what it is)
On the same line select the Building field
On the next row, leave the name blank, but select the Row field
Next row, Name blank, select Bay field
Next row, Name blank, select shelf field.

This will create a multiple index for all for fields meaning the combination of those four fields cannot be duplicated. I think this is what you are looking for (hope so anyway).

 

[jimmythegeek]

By the way, if there are duplicates in your table, already, it will not save the indexes. You must delete the duplicates first.

 

[christheprogrammer]

Or if you are just interested in creating a primary key, press ctrl while choosing several fields whose combination must be unique and then press the key button...
Chris

 

[ruthcali]

i already have a multiple index like you said above. it works if, for example, the user enters the following for record one:
Site1Name: Building1
Site1RowW1: 100
Site1BayW1: 200
Site1ShelfW1: 300

and then if he goes to record 2 and tries to enter the same info at the Site1 text boxes, Access doesn't let him (good).

BUT, if he tries to enter that info into the Site2 text boxes, he is allowed (not good).

i have a bunch of text boxes that all start with Site1, a bunch of text boxes that all start with Site2 and a bunch that all start with Site3. They are interchangeable. but once a bunch of values is entered, i need that to be unique.

how can i make it possible so that:
Site3Name: Building1
Site3RowW1: 100
Site3BayW1: 200
Site3ShelfW1: 300
will not be allowed.

That is a position in a building and once that specific position is occupied, noone else can use it.

doing an index like you mentioned above only works if you try to enter info into the same Site text boxes.

but i want to block the user from being able to enter that combination above in the Site 2 text boxes and also i want to prevent him from entering the combination above in the site 3 text boxes. the muliple index i have now only prevents him from entering duplicate combos in the Site1 text boxes.

Any ideas?

thanks

 

[jimmythegeek]

That can't be done at the table level, you are going to have to code that on the form. When a user finishes entering data for site 2, you will have to test all the values for site 2 against site 1, and if they are the same, alert the user, and make them change it.

My question is what happens if you need more than 3 sites. By setting up the form this way, aren't you kind of backing yourself in a corner?

I guess without proper understanding of your process and/or your application, it's hard to get a picture of what your looking for.

 

[ruthcali]

But how do i do the following?:

"When a user finishes entering data for site 2, you will have to test all the values for site 2 against site 1, and if they are the same, alert the user, and make them change it."

That is what i am trying to figure out!

If i need a site 4 or site 5, i can just add those fields to the table later.

 

[MichaelRed]

ruthcali,

You need to think about the structure of the database. The relational process usually ants the the structure to be "Deep", Not "wide". When you set up the Site1, Site2 ... you are making the database be "Wide". If you just have the Site as a field (enter the name), you have more records - but the number of fields decreases. When you follow the process to its logical conclusion, you have a database where you can restrict the entries by use of a Primary Key index.

I only see the need for four fields, [Site] [Row] [Bay] & [Shelf]. Each of these may be duplicated, however the combination must be unique (e.g. this is the primary key).

 

[ruthcali]

Michael,

Wow. Your advice would change my world around, but it makes sense! Please elaborate before i go changing the whole database.

So in my table i should have the fields: ID, site, row, bay, shelf only?

And then in my form, i can have the text boxes:
site, row, bay, shelf,
site, row, bay, shelf,
site, row, bay, shelf
?

I have basically two tables now. One called Circuits that contains the autonumber field ID and all the Site1Name, Site1RowW1...Site2Name, Site2RowW1....info.

Then i have another table called Channels because each piece of equipment can have 96 channels. Talk about Wide, i have 97 fields in that table: the field ID and then the fields: CH1, CH2, CH3, Ch4...CH96. the ID fields in the two tables have a one to one relationship.

This is a normal entry in my table:

all this would be ID: 1

site1Name: Building 1

Site1RowW1: 100
Site1BayW1: 05
Site1ShelfW1: 01

Site1RowW2: 101
Site1BayW2: 05
Site1ShelfW2: 01

Site1RowP1: 102
Site1BayP1: 05
Site1ShelfP1: 01

Site1RowP2: 103
Site1BayP2: 05
Site1ShelfP2:01

Site2Name: Building2

Site2RowW1: 103
Site2BayW1: 05
Site2ShelfW1: 01

Site2RowW2: 111
Site2BayW2:06
Site2ShelfW2:31

Site2RowP1:198
Site2BayP1: 47
Site2ShelfP1:32

Site2RowP2:912
Site2BayP2: 12
Site2ShelfP2:37


then, on my subform (Channels table) i would have
ID: 1
Ch1: AB48
Ch2: CE192
CH3: YT48
and so on until
Ch96: BF48

So, your way, on the Circuits table, i would have
ID: 1
row:100
bay:05
shelf:01

ID:1
row:101
bay:05
shelf:01

and so on

is that correct?

what should i do with my Wide Channels table?

Thanks so much for your input.

 

[jimmythegeek]

Michael is right!

Your tables should be set up as follows:

Table 1 (tblCircuits)
===========
CircuitID (Autonumber)
Bldg
Row
Bay
Shelf
Note: Composite index on the last four fields

Table 2 (tblChannels)
============
ChannelID (Autonumber)
CircuitID
Channel
ChannelDescr
Note: Composite index on last three fields

In your relationship window, you need to link the CircuitID in tblCiruits to the CircuitID in tblChannels using a One to Many relationship.


So it would look like this:

tblCircuits

CircuitID Bldg Row Bay Shelf
=========================
1 1 100 5 1
2 1 101 5 1
3 1 102 5 1
4 2 111 6 31
5 2 198 47 32
6 3 912 12 37


tblChannels

ChannelID CircuitID Channel ChannelDescr
===============================
1 1 1 AB48
2 1 2 CE192
3 1 3 FF123
4 2 1 BB456
5 4 1 TT888
6 4 2 GG999
7 6 1 ZZ93
8 6 2 HR224
9 6 3 AE55
10 6 4 AC14


Using this structure, you do not limit yourself in either table. You can have as many circuits as you want, and as many channels for each circuit as you wish. Now you need to create a form with the circuit information on it, and a subform with the Channel information in it. Then link the CircID fields as the Master, Child, and you are ready to rock and roll, with the flexibility to grow.

 

[MichaelRed]

ruthcali,

I am not in a position to 'pass judgement' on the whole process, as I only know what you have posted. What I do see appears to be 'correct' - according to my deep vs wide concept. For the Channels, they look like the 'correct' organization of this information (deep really refers to the unuque organization of the information - not a

 

[ruthcali]

Thanks so much for your help.

i'm not sure if i was clear, but for one ID, i will always have two buildings.

So ALL of the data below must be the same ID Number

i'm using ID=1 just for an example.

all this would be ID: 1:
site1Name: Building 1

Site1RowW1: 100
Site1BayW1: 05
Site1ShelfW1: 01

Site1RowW2: 101
Site1BayW2: 05
Site1ShelfW2: 01

Site1RowP1: 102
Site1BayP1: 05
Site1ShelfP1: 01

Site1RowP2: 103
Site1BayP2: 05
Site1ShelfP2:01

Site2Name: Building2

Site2RowW1: 103
Site2BayW1: 05
Site2ShelfW1: 01

Site2RowW2: 111
Site2BayW2:06
Site2ShelfW2:31

Site2RowP1:198
Site2BayP1: 47
Site2ShelfP1:32

Site2RowP2:912
Site2BayP2: 12
Site2ShelfP2:37

all of the above, as well as all of the below, would be ID=1

then, on my subform (Channels table) i would have
ID: 1
Ch1: AB48
Ch2: CE192
CH3: YT48
and so on until
Ch96: BF48

Since autonumbers can't repeat, how could i show that on my Circuits table?

what i would need is:
CircuitID Bldg Row Bay Shelf
=========================
1 1 100 5 1
1 1 101 5 1
1 1 102 5 1
1 1 103 5 1
1 2 103 5 1
1 2 111 06 31
and so on.
but if circuit Id is an autonumber field, it can't repeat.
Right?

Help!

 

[Guest_imported]

it seems to me that you are looking for a code generator. Why not write such a beast in whatever coding language you are most familiar with. You could generate your unique three digit codes checking them in a sequential file. Having done this you would then pop them and via string handling use them in your database

 

[jimmythegeek]

Is this what you are looking for? It is good practice to have an ID autonumber field. The red constitutes a composited index.


Circuits table
CircID Site Bldg Row Bay Shelf
=============================================
1 1 1 100 5 1
2 1 1 101 5 1
3 1 1 102 5 1
4 1 1 103 5 1
5 1 2 103 5 1
6 1 2 111 06 31


Channels table
ChanID Site Channel Descr
===============================
1 1 1 AB48
2 1 2 CE192
3 1 3 FF123
4 2 1 BB456
5 2 2 TT888
6 1 4 GG999
7 2 3 ZZ93
8 2 4 HR224
9 1 5 AE55
10 1 6 AC14

 

[Robertd]

Hi Ruth,

welcome to relational database design,

you might be interested in a book that i have found very helpful

Database Processing by David M. Kroennke

my copy is the 6th edition a bit dated but, it will explain the concept Jim took the design "schema" through.

ok i'm promoting,..
not the product but the concept i hear horror stories re access failing but i think more often it's the database design that fails ;-)


RobertD

 

[aug4570]

hey robertd, is that book still available in bookstores? what's your copyright date?

 

[Robertd]

Hi
ok it's 1998,
isbn 0-13-737842-2
this text has been revised since, there are as always other good texts. the version i have is not all "access" based but is rdbm based.

its a good read

see ya

RobertD

 

[HaroldH]

The database design term to look at is "normalization." The tip-off that your tables are not normalized is the nameing of fields as Site1, Site2, etc. The terminal digit in those names is actually data. Field names should never contain data.

...Harold

 

[ruthcali]

Thank you everyone for your input. Looks like I jumped too quickly when creating my database. it is so nice that there is a place to turn to (Tek-tips and all you nice people!) that is willing to give time and advice to a struggling Access colleague.

Regarding the Database Processing book, is there a more recent book that you would recommend? I get nervous reading a book 2 years old because you know how quickly things change.

thanks!