Append Won't/Copy'n'Paste OK...?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I must be missing something simple here. I have a table with a four-field
key. I built a query that's supposed to append a number of new records to my
table, but when I run it, it fails due to "key violations" even though I
can't see any -- the new records are completely unique. Here's the kicker:
if I set the query to "select query" so it generates the same set of records
as a dynaset, I can copy them and paste them into my table with no problem at
all. Huh?? Anybody know what's going on?
 
Post back with the SQL of the append query and identify the fields that make
up the primary key. I assume the key field you are talking about is the
primary key. If not, please so state.
 
The table has about two dozen fields, but the primary key involves four of
those fields: Operation, VS, YYYYWM, and YYYYWW. The idea of the append
query is to "create a new week" in the table by appending data to only these
four fields. I get the Operation and VS values from my table (i.e., I just
pull the same operations and VS's used for the previous weeks), the user is
prompted for the new week value, e.g., 200725, and I get the corresponding
month value, e.g., 200706, from a calendar lookup table. A new record,
therefore, will look like this:

Operation VS YYYYWM YYYYWW
MACHINING PREC WINDINGS 200706 200725

The operation and VS will duplicate existing values (intentionally), and the
month value MAY duplicate existing month values, but the week value is always
unique, therefore there shouldn't be any key violations.

Here's the SQL I attempted to use:

INSERT INTO tbl16MData ( Operation, VS, YYYYWM, YYYYWW )
SELECT DISTINCT tbl16MData.Operation, tbl16MData.VS, MasterCal.MMonthSort AS
YYYYWM, MasterCal.MWeekSort AS YYYYWW
FROM tbl16MData, MasterCal
WHERE (((MasterCal.MWeekSort)=[Add what workweek (YYYYWW)?]));
 
Back
Top