Table Design Query

[Gary Belcher]

Hello,



I have Access 2000 and I'm trying to create a table to store domain names in
one field, along with another field for keeping track of the last time the
domain was added to the table (month and year), and another field for
keeping track of how many times in total it has been added to the table. My
problem is because the field that stores my domain name has to be unique
(i.e. no duplicates) when I test my table by adding the same domain name (to
see if the last added field and number of times added field update
correctly), the validation rule wont allow me to do it because I'm adding a
duplicate domain! Is there anyway around this? The only way I can think of
is to turn the no duplicates validation on the domain field off, but that
would obviously cause a lot of needless duplicates, not to mention a lot of
confusion! Any help would be greatly appreciated.

 

[Kris]

Just a thought:
allow duplicates, enter the duplicate domain, filter for
current date.

Set up another query to sum for number of changes.

 

[Gary Belcher]

Hi Kris,

Thanks for taking the time to reply. I guess I could do what you've
suggested, but I really want to avoid the duplicate domains in the domain
field if possible. See my reply to what Jeff wrote for more of an idea of
what I'm trying to do. Thanks!

 

[Tim Ferguson]

so in other words,
if I have microsoft.com in my table, and I import a list of domains -
one of which is microsoft.com - even though the validation on the
domain field stops microsoft.com being added to the table, I want the
table to record the fact that the attempt was made to add it to the
table, and how many times.

This is a straightforward one-to-many relationship:

Domains(*IPAddress, Name, Owner, etc.)

Additions(*IPAddress, *SourceCode, DateAdded, AddedByWhom)


To get the number of additions, you can do COUNT(Additions.*), to get the
most recent one, it's MAX(Additions.DateAdded) etc. etc.


HTH


Tim F

 

[Craig Hornish]

In reading your replies it sounds like you are trying to
do a group of the names at once. Your subject line also
gives you the answer. Use a query.

Have the list of names you wish to add in another table.

To add then new names:
Run an Append query - you can update the Date and the
number of times the name has been added, (now(),1) if your
table doesn't default this.
You can can either let the ones that are dups not be
added or your can add the 'target' table, and link the
names with the option "Inlcude all from (Added) .." and
the criteria for the Domain name in the target "Is Null".

To update the "Duplicates":
Use an Udate query - Include the two tables linked on
the name. You can update the date, and just add 1 to the
timesadded field. (#08/20/2003#,[TimesAdded]+1)

Hope this is a solution to your specific problem.

Craig