Hi, I have Three tables
the first is a ContractTable
the second is a Monthly Invoice Details Table
and the third is a Detailed Payments table

Contract 1 -> Many Monthly Invoice 1 -> Many Detailed Payments

what I would like to do is ensure that there arent any duplicated Months for a Contract in the Monthly Invoice table

in other words For Contract 00023 I NEVER want it to have more than one invoice record for the month of JUNE 2006....

It may be easy but it has me all tied up, please assist.

If you have a field that identifies the month and year (not just an invoice date), you can create a two field index on the invoice month and the contract number and set it to no duplicates.

i was just browsing through some posts and found one about creating a Compound Primary Key where the ContractId can be repeated in the Invoice tabkle (which is needed) and the Invoice Month can be repeated (Which is also needed) but not a combination of both

so i could have multiple records for contract 000112 because that contract would have multiple months

and i could have multiple Jan 2006 for different Contract ID but i couldnt have More than one record that has 000112 and Jan 2006...

So what im goin to do is combine that with your suggestion of using a Month field and a year field instead of just a date (although i could just change the format of my date field to only record "Jan,2006")


Thanks a lot. If i run into any difficulty i'll post back, BUT MUCCCCCCCH THANKS....

From in my table how can i format the date to show "Jan, 2006"

What i would want it to do is like do a datepart thing something like

datepart("M",(CurrentDate))&","& datepart("YYYY",(CurrentDate)))

but im not sure how to do that so that the default value of the new record in the Date field of my table woud be that

Two points.

Don't make a compound primary key. Keep an autonumber as a PKJ and use a compound index.

You can't do what you suggest at a table level but you can in a form.