Access 2003 Append

[JOE WEST]

I want to append a new record in one table, if the field in another is equal
to one of the values in a third. e.g.
COLORLIST table contains "BROWN", "BLUE", "GREEN"
if the COLOR field in table 1 (MAIN TABLE) is equal to any of the colors in
Table 3-[COLORLIST] THEN append a record in table 2 with a default date of
date().

I TRIED A QUERY FOR THIS, AND WHILE THERE SHOULD HAVE BEEN 33 RECORDS IN THE
TABLE THAT QUALIFIED, IT SAID IT WAS SELECTING 192 RECORDS, WHICH I KNOW IS
WRONG. I DON'T KNOW WHAT I AM NOT DOING OR DOING THAT IS CAUSING THIS
PROBLEM. CAN ANYONE HELP ME OUT WITH THIS?

 

[Michel Walsh]

SELECT table3.*, Now( )
FROM table3 INNER JOIN colorlist
ON table3.color = colorlist.color



should return the records to be appended. If Colorlist.color has no
duplicated color value, that is.


If colorlist.color has duplicated color value, then, try:

SELECT table3.*, Now( )
FROM table3
WHERE EXISTS( SELECT *
FROM colorList
WHERE table3.color = colorList.color)



Vanderghast, Access MVP

 

[John Spencer (MVP)]

Based on your description, I would guess you wanted a query that looked like

INSERT INTO Table2 (DateField)
SELECT Date()
FROM [Main Table] INNER JOIN ColorList
On [Main Table].Color = ColorList.Color

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County