INSERT INTO query problem

[stephengrenfell]

I have an orders database in Access 2007 with several tables (tblCust, tblInv and tblInvDetail) containing customer information, order information and order details respectively. I want to add orders which are imported via a temporary table (Import_VmOrderListFile) using several queries.
The first query adds the customer details into tblCust from Import_VmOrderListFile if the customer number Import_VmOrderListFile.user_id does not exist in tblCust.CustNum .
If an order has multiple items then there is a row in Import_VmOrderListFile for each item on the order. I can’t change the format of the import table. Hence I do not want the query to add the customer details into tblCust more than once.
Below is the code for the query. However where there are multiple entries for an order in Import_VmOrderListFile the query tries to add the customer details into tblCust for each entry.
What am I doing wrong ?. Any help would be appreciated.

Thanks

INSERT INTO tblCust ( CustNum, CustLName, CustAddr1, CustAddr2, CustZip, CustCity, CustState, CustPhone, CustEmail, CustNote, LivLName, LivAddr1, LivAddr2, LivCity, LivZip, LivState, LivPhone )
SELECT DISTINCT Import_VmOrderListFile.user_id, Import_VmOrderListFile.BT_name, Import_VmOrderListFile.BT_address_1, Import_VmOrderListFile.BT_address_2, Import_VmOrderListFile.BT_zip, Import_VmOrderListFile.BT_city, Import_VmOrderListFile.BT_country, Import_VmOrderListFile.BT_phone_1, Import_VmOrderListFile.user_email, Import_VmOrderListFile.customer_note, Import_VmOrderListFile.ST_name, Import_VmOrderListFile.ST_address_1, Import_VmOrderListFile.ST_address_2, Import_VmOrderListFile.ST_city, Import_VmOrderListFile.ST_zip, Import_VmOrderListFile.ST_country, Import_VmOrderListFile.ST_phone_1
FROM Import_VmOrderListFile
WHERE Import_VmOrderListFile.user_id NOT IN (SELECT CustNum FROM tblCust);

 

[boblarson]

I could be wrong but I think you need to group on the fields for the subquery select so that only one per name show up.

 

[stephengrenfell]

Hi Bob,

Thanks for your reply.

If I add a GROUP BY stateemnt after the WHERE like this :

INSERT INTO tblCust ( CustNum, CustLName, CustAddr1, CustAddr2, CustZip, CustCity, CustState, CustPhone, CustEmail, CustNote, LivLName, LivAddr1, LivAddr2, LivCity, LivZip, LivState, LivPhone )
SELECT DISTINCT Import_VmOrderListFile.user_id, Import_VmOrderListFile.BT_name, Import_VmOrderListFile.BT_address_1, Import_VmOrderListFile.BT_address_2, Import_VmOrderListFile.BT_zip, Import_VmOrderListFile.BT_city, Import_VmOrderListFile.BT_country, Import_VmOrderListFile.BT_phone_1, Import_VmOrderListFile.user_email, Import_VmOrderListFile.customer_note, Import_VmOrderListFile.ST_name, Import_VmOrderListFile.ST_address_1, Import_VmOrderListFile.ST_address_2, Import_VmOrderListFile.ST_city, Import_VmOrderListFile.ST_zip, Import_VmOrderListFile.ST_country, Import_VmOrderListFile.ST_phone_1
FROM Import_VmOrderListFile
WHERE Import_VmOrderListFile.user_id NOT IN (SELECT CustNum FROM tblCust)
GROUP BY Import_VmOrderListFile.user_id, Import_VmOrderListFile.BT_name, Import_VmOrderListFile.BT_address_1, Import_VmOrderListFile.BT_address_2, Import_VmOrderListFile.BT_zip, Import_VmOrderListFile.BT_city, Import_VmOrderListFile.BT_country, Import_VmOrderListFile.BT_phone_1, Import_VmOrderListFile.user_email, Import_VmOrderListFile.customer_note, Import_VmOrderListFile.ST_name, Import_VmOrderListFile.ST_address_1, Import_VmOrderListFile.ST_address_2, Import_VmOrderListFile.ST_city, Import_VmOrderListFile.ST_zip, Import_VmOrderListFile.ST_country, Import_VmOrderListFile.ST_phone_1;

Then it still add entries in tblCust where there are duplicates in Import_VmOrderListFile .

If I do not list all the fields used in the SELECT DISTINCT after the GROUP BY I get an [FONT=&quot]"You tried to execute a query that does not include the specified expression as part of an aggregate function. (Error 3122)" [/FONT]Message.

Any suggestions ?.

Thanks.
[FONT=&quot][/FONT]

 

[stephengrenfell]

I think I hit the Submit Reply button too soon..

Perhaps I am still getting duplicates becuase some of the fields listed after the GROUP BY may be null . I can't do anything about these null values since some fields are optional they may be left blank creating a null value.

Is there a way to make the GROUP BY ingore any null values ?.

Or is there a way not to have include all the columns after GROUP BY ?.

In fact shouldn't the SELECT DISTINCT ignore any duplicate rows ?. Or is this also caused by the null values ?.

Sorry if this too obvious / simple. I am not an expert of SQL.

Thanks again for your help.

Stephen

 

[boblarson]

Nulls would be grouped too, if the rest of the fields are the same. I would remove the Select Distinct and just use SELECT with the Group By and see how that goes.