AnsweredAssumed Answered

'Existing/Unique' field help

Question asked by TomGrasmeyer on Oct 5, 2010
Latest reply on Oct 5, 2010 by philmodjunk


'Existing/Unique' field help


I have a DB which I use for selling ads in a publication I'm designing. It manages what ad is sold to what advertiser. There are some ad positions that cannot be sold more than once in a publication (inside front cover, outside back cover, inside back cover). I'll call these 'unique positions.' All others can be sold multiple times (1/2 page, full page, etc.). So I want the DB to make sure that for each publication I have not sold these 'unique positions' more than once. I have two tables:

Table 1: Ad types - a list of all the possible ad types available for sale. Fields include: ad ID, ad description, ad size, and then a 'check box' for 'unique position' (on or off).

Table 2: ad bookings - the DB into which all the ad orders are entered. Fields include: booking ID, advertiser ID, ad ID and the various lookup fields to table 1 based on a relationship linking 'ad ID' field (portal).

So for 'ad ID' in table 2, I need to 'validate' the field on entry: I want it to look at the ad ID and determine if it's a 'unique position.' If it's a unique position, I want it to look through the DB and see if that space is already sold (to see if, for that ad ID, the number of times it appears = 1). If it IS already sold (so that ad ID already appears 1 time) it needs to come back to the user and say 'sorry, that ad is already sold.' If it it NOT already sold, it needs to allow the entry.

Any suggestions on how to do this?