Hi, I've been using access for a long time, but have always used Macro builder etc..very little experience with SQL.
I had someone write a query for me that updates data in a table - (qry_CreatePriceAddTemplate) is actually a table..I plan on changing that name.
I am trying to add a new calculated field: XREFID: concatenate [Indirect Contract]&[NDC Full]
so that it will update/populate the existing field in the table (qry_CreatePriceAddTemplate).
Any help would be much appreciated!
strSQL = "DELETE * FROM qry_CreatePriceAddTemplate"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO qry_CreatePriceAddTemplate SELECT [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], '' AS Price, '' AS Effective, "
strSQL = strSQL & Chr(34) & "12/31/9999" & Chr(34) & " AS [End Date]"
strSQL = strSQL & " FROM tbl_NDCXREF, [tbl_Contract ID]"
strSQL = strSQL & " WHERE tbl_NDCXREF.[MATERIAL DESCRIPTION] IN (" & strSelectedProduct & ")"
strSQL = strSQL & " And [tbl_Contract ID].[Contract Description] IN (" & strSelectedContractDesc & ")"
strSQL = strSQL & " GROUP BY [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], ''," & Chr(34) & "12/31/9999" & Chr(34) & ", ''"
DoCmd.RunSQL strSQL
