DEFINING COLUMN UNIQUENESS IN AN ACCESS DATABASE

[TNN]

I have an Access 2000 database and am trying to set up multi column uniqueness on a per employee number basis.

Each record includes the following fields:

EmpNo WageNo RateOfPay
1575 8 >> 5.25
1575 10 6.25
1575 8 7.25
1575 8 >> 5.25 (Don't want this to
occur. That is same
RateOfPay for same
WageNo for same
employee.)
Table holds numerous different EmpNo's.
Column uniqueness doesn't seem to apply. Keys or indexes don't seem applicable.

What kind of integrity can I apply to achieve the above by way of MS SQL.


Any Help Appreciated
TNN, Tom TNPAYROLL@AOL.COM



[sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[jnicho02]

So what you are saying is that you have a table where each combination of EmpNo/WageNo/RateOfPay is unique.

Is that not then the primary key of the table? [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[TNN]

Jnicho02
Thank You for your reply.

The primary key is the field EmpNo. Related table name is PERS.
one..........to........many relationship
PERS-------------------EARN (tables)
EmpNo(primary key) EmpNo (foreign key)
WageNo
RateOfPay

Can You Help ??
Thank You, TNN
[sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[foxdev]

If you don't want to define those three columns as the primary key as jnicho02 suggested, you could create a Unique constraint on the combination. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TNN]

Thank You Foxdev but:
Can't make SQL statement work. Get syntax error.
My SQL:
ALTER TABLE EARN
CONSTRAINT NODUP
UNIQUE (EmpNo, WageNo, RateOfPay)

Can You Help ?? [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[TNN]

FOXDEV
Robert,
Got It, SQL CONSTRAINT, UNIQUE WORKS GREAT. THANKS

Now have dups somewhere in my tables. How do I select up my dups to be able to delete them.
TNN, Tom [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[foxdev]

select EmpNo, WageNo, RateOfPay, count(*)
from MyTable
group by EmpNo, WageNo, RateOfPay
having count(*) > 1 [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TNN]

Thank You Robert(Foxdev),
That SQL statement worked great to find dups. Now I have to make sure I understand it. I am learning SQL as I build my Payroll Processing software.
Thanks Again,
TNN, Tom
TNPAYROLL@AOL.COM [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[foxdev]

select EmpNo, WageNo, RateOfPay, count(*)
Select the three columns, the combination of which you want to find dupes. The COUNT(*) function will yield a number (the &quot;*&quot; means include null values), and it is used in conjunction with the GROUP BY clause - in other words, the columns you are counting would normally also appear in the GROUP BY clause.

from MyTable

group by EmpNo, WageNo, RateOfPay
Depending on your database server, the GROUP BY items may also be required to be in the select list (first line).

having count(*) > 1
Rather than have a zillion rows returned, most with no dupes, this will only give us the ones that have 2 or more. A HAVING clause's condition is applied after the GROUPing has been done, unlike a WHERE clause, which is applied on the individual records. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TNN]

Thanks Again Robert(Foxdev)
I got it. I see the GROUP BY Clause is the key clause that
picks out the dups.
TNN, Tom
TNPAYROLL@AOL.COM [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]