After Insert - Update Calculated Field Padded Count Within Project?

[misscrf]

I have a a form where users import a spreadsheet. The form highlights any data that is not valid or needs to be cleaned up. Once all the data is good, they can press a button to import that data into a normalized table structure. This is all done in vba. The records are added to a table, and when they are, I need to add/update a calculated field. It needs to string together 2 different fields with a counter padded to 5 digits like this: "123456_00001_ABC" That counter is not based on the whole table. It is based on the count of items in that table that have the same 6 digit prefix (the first part of the concatenated string).

I tried a sql trigger, but that updates after the entire insert, not after each record is added. I'm looking for other ways, maybe in vba, to accomplish this.

If anyone has any suggestions, I would love to try them out. I'm not sure if explaining more of the fields would help or not, but I figured I would start with the basic needs and see where we can get to.

Thanks for any ideas!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

I tried a sql trigger
In msaccess ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

the tables are all SQL and linked to access. Sorry! I use access for the gui - forms and reports.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

So, use a trigger FOR EACH ROW

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

wow, I didn't find that in my research. I will try that!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Thanks for your responses, PHV. I started looking this up and unfortunately I found this: The trigger can based on ROW in oracle,but only can base on TABLE in sql server. (

http://social.msdn.microsoft.com/Fo...ec44ce86/how-to-let-trigger-act-for-each-row)

This is what I tried to do:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER MyAssetTrigger
AFTER UPDATE ON tblChainofCustody
FOR EACH ROW
WHEN AssetTag IS NULL --AND MATTER = A.MATTER
BEGIN
	UPDATE tblChainofCustody 
	SET AssetTag = a.NewAssetTag
	FROM (SELECT LEFT(vwb.BillingGroupKey,6) + '_' + RIGHT(REPLICATE('0', 5) + CAST(count(coc.ID) AS VARCHAR(5)), 5) + '_' + amt.MediaTypeAbbrev AS NewAssetTag
	FROM tblChainofCustody coc (NOLOCK) 
	JOIN tblAssetMediaTypes amt (NOLOCK) ON coc.FKMediaType = amt.ID 
	JOIN vw_billinggroups vwb (NOLOCK) ON vwb.BillingGroupKey like '%' + CONVERT(VARCHAR, coc.matter) + '%' 
	INNER JOIN INSERTED i on coc.ID = I.ID  WHERE I.ID = coc.ID
	GROUP BY amt.MediaTypeAbbrev, 
	LEFT(vwb.BillingGroupKey,6)) a
END

It won't let me alter it, though. I get this error:

Msg 102, Level 15, State 1, Procedure MyAssetTrigger, Line 2
Incorrect syntax near 'AFTER'.

This is the trigger that "works" but not as desired:

USE [230585NuixBilling]
GO
/****** Object:  Trigger [dbo].[MyAssetTrigger]    Script Date: 08/08/2013 17:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyAssetTrigger] 
ON [dbo].[tblChainofCustody]
FOR INSERT
AS
UPDATE tblChainofCustody set AssetTag = a.NewAssetTag
FROM
(SELECT LEFT(vwb.BillingGroupKey,6) + '_' + RIGHT(REPLICATE('0', 5) + CAST(count(coc.ID) AS VARCHAR(5)), 5) + '_' + amt.MediaTypeAbbrev AS NewAssetTag
FROM tblChainofCustody coc (NOLOCK) 
JOIN tblAssetMediaTypes amt (NOLOCK) ON coc.FKMediaType = amt.ID 
JOIN vw_billinggroups vwb (NOLOCK) ON vwb.BillingGroupKey like '%' + CONVERT(VARCHAR, coc.matter) + '%' 
INNER JOIN INSERTED i on coc.ID = I.ID  WHERE I.ID = coc.ID
GROUP BY amt.MediaTypeAbbrev, 
LEFT(vwb.BillingGroupKey,6)) a

The other part of this that I am missing is that I need to make sure that CAST(count(coc.ID) is only within the records with the same LEFT(vwb.BillingGroupKey,6).

Let me know if I am missing something.





misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

I'd ask here:
forum183

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886