Stop Me Please

[Guest]

I need to stop the following query from running if entries already exist.
This is an append query that takes names from the table "Employee" and
inserts them into another table, "Activities". The query also calculates the
last day of the previous month and inserts that with each name. If an entry
already exists in the Activities table for a particular person's name and the
last day of the previous month, then I don't want the query to run. For
example, if the Activities table already contains an entry for "John Doe"
dated 12/31/2006, the query should abort. If John Doe has no entry for
12/31/2006, then it would run. Am I making sense?

INSERT INTO activities ( employee, officerNumber, [month] )
SELECT Employees.Full_Name, Employees.officerNumber,
DateSerial(Year(Date()),Month(Date()),0) AS [Month]
FROM Employees
WHERE (((Employees.Status)="active" And (Employees.Status)="active" And
(Employees.Status)="active"));

I need to know how to modify this query so it won't run if the conditions I
described above exist. Your help is greatly appreciated!

GwenH
Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

 

[John Vinson]

I need to stop the following query from running if entries already exist.
This is an append query that takes names from the table "Employee" and
inserts them into another table, "Activities". The query also calculates the
last day of the previous month and inserts that with each name. If an entry
already exists in the Activities table for a particular person's name and the
last day of the previous month, then I don't want the query to run. For
example, if the Activities table already contains an entry for "John Doe"
dated 12/31/2006, the query should abort. If John Doe has no entry for
12/31/2006, then it would run. Am I making sense?

INSERT INTO activities ( employee, officerNumber, [month] )
SELECT Employees.Full_Name, Employees.officerNumber,
DateSerial(Year(Date()),Month(Date()),0) AS [Month]
FROM Employees
WHERE (((Employees.Status)="active" And (Employees.Status)="active" And
(Employees.Status)="active"));

I need to know how to modify this query so it won't run if the conditions I
described above exist. Your help is greatly appreciated!

Well, you should certainly be checking for duplicate officerNumber
values, not Full_Name (and storing OfficerNumber rather than the
employee's name into the activities table. Names are NOT unique; I
know three guys named Fred Brown. If the officerNumber uniquely
identifies a person, then you should probably not store the Full_Name
at all, just look it up by joining to the Employees table.

You should also consider changing the name of the field [month] -
that's a reserved word, and likely to cause trouble; as well as being
actively misleading, since it contains a full date, not a month name
or month number.

You can prevent the records from being appended by creating a unique
Index on the three fields whcih constitute a duplicate. You'll get a
warning message (which can be suppressed) that "x records were not
added due to key violations".

You can also base the APPEND query on an "unmatched" query to select
only records which have not been added.

John W. Vinson[MVP]

 

[Guest]

I appreciate all your tips about improving the query design. I am actually
looking at the officer number and month, not the officer name and month ...
that was a typo I made in my haste to get this posted. On the other
improvements you noted, yes that would be the better way to do it, but
because technically challenged individuals will be looking at these queries,
I have to do it this way. I won't bore you with the details on that.

So how do I create a unique index?

I need to stop the following query from running if entries already exist.
This is an append query that takes names from the table "Employee" and
inserts them into another table, "Activities". The query also calculates the
last day of the previous month and inserts that with each name. If an entry
already exists in the Activities table for a particular person's name and the
last day of the previous month, then I don't want the query to run. For
example, if the Activities table already contains an entry for "John Doe"
dated 12/31/2006, the query should abort. If John Doe has no entry for
12/31/2006, then it would run. Am I making sense?

INSERT INTO activities ( employee, officerNumber, [month] )
SELECT Employees.Full_Name, Employees.officerNumber,
DateSerial(Year(Date()),Month(Date()),0) AS [Month]
FROM Employees
WHERE (((Employees.Status)="active" And (Employees.Status)="active" And
(Employees.Status)="active"));

I need to know how to modify this query so it won't run if the conditions I
described above exist. Your help is greatly appreciated!

Well, you should certainly be checking for duplicate officerNumber
values, not Full_Name (and storing OfficerNumber rather than the
employee's name into the activities table. Names are NOT unique; I
know three guys named Fred Brown. If the officerNumber uniquely
identifies a person, then you should probably not store the Full_Name
at all, just look it up by joining to the Employees table.

You should also consider changing the name of the field [month] -
that's a reserved word, and likely to cause trouble; as well as being
actively misleading, since it contains a full date, not a month name
or month number.

You can prevent the records from being appended by creating a unique
Index on the three fields whcih constitute a duplicate. You'll get a
warning message (which can be suppressed) that "x records were not
added due to key violations".

You can also base the APPEND query on an "unmatched" query to select
only records which have not been added.

John W. Vinson[MVP]

 

[John Vinson]

I appreciate all your tips about improving the query design. I am actually
looking at the officer number and month, not the officer name and month ...
that was a typo I made in my haste to get this posted. On the other
improvements you noted, yes that would be the better way to do it, but
because technically challenged individuals will be looking at these queries,
I have to do it this way. I won't bore you with the details on that.

Well, if you only let your non-techie users see properly designed
Forms (and keep them out of table and query datasheets), then
fieldnames and the minutae of table relationships will be invisible to
them anyway...

So how do I create a unique index?

Open the Table in design view. Select the Indexes icon on the toolbar
(looks like lightning hitting a datasheet). Type an index name in the
left column, and three fieldnames in the right, one under another:

IDXUnAppointment OfficerNumber
[Month]

will give you a two-field index, and you can check the Unique
checkbox.

John W. Vinson[MVP]

 

[Guest]

YES! Thank you ~ that did the trick.

And I can't keep my non-techie users out of the tables and queries 'cause
they already learned how to get into them before I started working at this
company. If I tried to block the users out of the tables and queries (one of
whom is a senior VP), I'd be asked to *unblock* the tables and queries.
Sometimes you just gotta work with what you got.

I appreciate all your tips about improving the query design. I am actually
looking at the officer number and month, not the officer name and month ...
that was a typo I made in my haste to get this posted. On the other
improvements you noted, yes that would be the better way to do it, but
because technically challenged individuals will be looking at these queries,
I have to do it this way. I won't bore you with the details on that.

Well, if you only let your non-techie users see properly designed
Forms (and keep them out of table and query datasheets), then
fieldnames and the minutae of table relationships will be invisible to
them anyway...

So how do I create a unique index?

Open the Table in design view. Select the Indexes icon on the toolbar
(looks like lightning hitting a datasheet). Type an index name in the
left column, and three fieldnames in the right, one under another:

IDXUnAppointment OfficerNumber
[Month]

will give you a two-field index, and you can check the Unique
checkbox.

John W. Vinson[MVP]

 

[John Vinson]

YES! Thank you ~ that did the trick.

And I can't keep my non-techie users out of the tables and queries 'cause
they already learned how to get into them before I started working at this
company. If I tried to block the users out of the tables and queries (one of
whom is a senior VP), I'd be asked to *unblock* the tables and queries.
Sometimes you just gotta work with what you got.

Yep... that can be tough! About the only solution is to provide forms
that are SO easy to use and natural that there's no incentive to
bypass them... and since you have a diverse audience to please, that
can be impossible. Good luck!

John W. Vinson[MVP]