Multifield indexes

[relewis]

Hello, this is my first question to Tek-Tips.

I have made multifield indexes for a table called Table1 like so:

[tt]Site1Index EventID
Site1
Site2Index EventID
Site2
Site3Index EventID
Site3
Site4Index EventID
Site4[/tt]

Everything works fine, unless I want to repeat the EventID over several records which may be any combination of EventID with a site.

Thus, Access will not allow me to have data like this in the table

[tt]EventID Site1 Site2 Site3 Site4
1 0 14 0 0
2 0 0 27 0
1 17 0 0 0[/tt]

It requires me to have unique EventIDs, even though EventID field is not a primary key in this table and I have allowed duplicates in all fields. The primary key for Table1 is called Table1ID and is autonumber.

All I want to do is not allow data where the combination of EventID and Site1, Site2, Site3 or Site4 is duplicated. I do not want to allow

[tt]EventID Site1 Site2 Site3 Site4
1 14 0 0 0
1 14 0 0 0[/tt]


I DO want to allow:

[tt]EventID Site1 Site2 Site3 Site4
1 14 0 0 0
1 0 27 0 0[/tt]

Why won't Access allow me to do this?

 

[tlbroadbent]

You indicate that all indexes allow duplicates but you get a message that forces you to enter a unique EventID. If one or more of the indexes is coded to not allow duplicates then you would get that message. For example, if EventID=1 and Site1=0 then another row could not contain that same combination regardless of the content of Site2 - 4.

Remove the current indexes and put a primary key on EventId, Site1, Site2, Site3, Site4. That will provide what you want. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Rhonin]

Terry has it right. Just hi-lite the EventId and all four Site fields and click on the primary key. As long as the EventId and all four Sites are not duplicated you should have what you need.