Monthly Invoice

[popeye]

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.

 

[neileg]

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.

 

[popeye]

GR8 Forum with gr8 minds

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....

 

[popeye]

Default Date Format for Records

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

 

[neileg]

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.