Number of query values and destination fields are not the same

[johnmerlino]

Hey all, I get an error that says "Number of query values and destination fields are not the same" with this query:

INSERT INTO KeepThese (f_fullName,addresses,cities,us_states_and_canada, zip_code,Ex_fullName)
SELECT f.aname,f.addresses,f.cities,f.us_states_and_canad a,f.zip_code FROM PrepareForDuplicateCheck2 AS f
LEFT JOIN (SELECT t.aname,t.addresses,t.cities,t.us_states_and_canad a,t.zip_code FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname HAVING Count(t.aname)>=6) AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;



Basically I have a table called KeepThese which contains these fields:
f_fullName,addresses,cities,us_states_and_canada,z ip_code,Ex_fullName

And I have table called PrepareForDuplicateCheck2 with these fields:
aname, addresses,cities,us_states_and_canada,zip_code

Hence I just want to append data from PrepareForDuplicateCheck2 to KeepThese where the same person doesn't have more than 5 different addresses in PrepareForDuplicateCheck2 .

Thanks for response.

[weekend00]

since it says "Number of query values and destination fields ", let's check the Number of query values and destination fields :

INSERT INTO KeepThese (
f_fullName,addresses, cities,  us_states_and_canada, zip_code,Ex_fullName)
SELECT 
f.aname,  f.addresses,f.cities,f.us_states_and_canad a,f.zip_code 
FROM PrepareForDuplicateCheck2 AS f 
LEFT JOIN (SELECT t.aname,t.addresses,t.cities,t.us_states_and_canad a,t.zip_code FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname HAVING Count(t.aname)>=6) AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

What is the pink a? and the red Ex_fullname has no corresponding select field.

[johnmerlino]

The a is part of canada. When I copied the query into the post, apparently it got separated from canad. But in my actual query the a is properly part of canada. So I remove Ex_fullName and now get this:

You tried to execute a query that does not include the specified expression "addresses" as part of an aggregate function.

with this query:

INSERT INTO KeepThese ( f_fullName, addresses, cities, us_states_and_canada, zip_code )
SELECT f.aname, f.addresses, f.cities, f.us_states_and_canada, f.zip_code
FROM PrepareForDuplicateCheck2 AS f LEFT JOIN (SELECT t.aname, t.addresses, t.cities, t.us_states_and_canada, t.zip_code FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname HAVING Count(t.aname)>=6)  AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

[weekend00]

INSERT INTO KeepThese ( f_fullName, addresses, cities, us_states_and_canada, zip_code )
SELECT f.aname, f.addresses, f.cities, f.us_states_and_canada, f.zip_code
FROM PrepareForDuplicateCheck2 AS f LEFT JOIN (SELECT t.aname, t.addresses, t.cities, t.us_states_and_canada, t.zip_code FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname, t.addresses, t.cities, t.us_states_and_canada, t.zip_code HAVING Count(t.aname)>=6) AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

[johnmerlino]

Thanks but that causes a problem now where a person have more than 5 different addresses are being appended as well. For example, this guy should not be appended:

Barnes,Robert 1405 YELLOWHEART WAY HOLLYWOOD FL 33019-4859
Barnes,Robert 16563 NW 2ND LN PEMBROKE PINES FL 33028-1443
Barnes,Robert 2725 S BRIDGE RD COOPER CITY FL 33026
Barnes,Robert 3 FOSTER DAVIS DR *KINGSTON 6 CA JM
Barnes,Robert 430 SW 195 AVE PEMBROKE PINES FL 33029
Barnes,Robert 4880 SW 153 TER MIRAMAR FL 33027-3661
Barnes,Robert 727 JUNIPER LANE WESTON FL 33327-1838
Barnes,Robert 7620 HYANNIS LANE PARKLAND FL 33067-1672
Barnes,Robert 812 NE 21 DR WILTON MANORS FL 33305-2226
Barnes,Robert 812 NE 21ST DR WILTON MANORS FL 33305-2226
Barnes,Robert 8711 NW 47TH CT LAUDERHILL FL 33351-5440

because he has more than 5 different addresses.

[weekend00]

you need to decide which address for this person to be picked. if you don't care which one, use first() in select and remove address from group by:
SELECT f.aname, first(f.addresses) as addresses,
INSERT INTO KeepThese ( f_fullName, addresses, cities, us_states_and_canada, zip_code )
SELECT f.aname, first(f.addresses) as addresses,, f.cities, f.us_states_and_canada, f.zip_code
FROM PrepareForDuplicateCheck2 AS f LEFT JOIN (SELECT t.aname, t.addresses, t.cities, t.us_states_and_canada, t.zip_code FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname, t.cities, t.us_states_and_canada, t.zip_code HAVING Count(t.aname)>=6) AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

[johnmerlino]

Actually the guy should be removed altogether if he has more than 5 different addresses. I don't want to preserve a first address because he's supposed to be removed entirely from the append.

[weekend00]

So, you need to remove these guys first then run this query.

[johnmerlino]

I don't mean removed I mean he shouldn't be part of the append when the new records get appended to KeepThese.

For example, this old query below did not insert the guy if he had more than 5 different addresses into a new table called KeepThese2:

SELECT * INTO KeepThese2
FROM PrepareForDuplicateCheck2 AS f LEFT JOIN (SELECT t.aname FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname HAVING Count(t.aname)>=6)  AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

So I am trying to accomplish the same but rather than inserting into a table called KeepThese2, I am trying to append in KeepThese.

Thanks for response.

[weekend00]

I understand now.
try this:
INSERT INTO KeepThese ( f_fullName, addresses, cities, us_states_and_canada, zip_code )
SELECT f.aname, f.addresses, f.cities, f.us_states_and_canada, f.zip_code
FROM PrepareForDuplicateCheck2 AS f LEFT JOIN (SELECT t.aname FROM PrepareForDuplicateCheck2 AS t GROUP BY t.aname HAVING Count(t.aname)>=6) AS Ex ON f.aname=Ex.aname
WHERE Ex.aname Is Null;

[johnmerlino]

Thanks, it worked.