Query - Append

[pat]

Hello:

I am running the following query:

INSERT INTO w_releaseln_edi ( RELEASENO, PRODNO,
PROD_DESC, BOXES, [CONTAINER], LOAD_NO, PO_NO, QTY )
SELECT DISTINCT First(w_releaseln.RELEASENO) AS
FirstOfRELEASENO, w_releaseln.PRODNO, First
(w_releaseln.PROD_DESC) AS FirstOfPROD_DESC, Sum
(w_releaseln.BOXES) AS SumOfBOXES, First
(w_releaseln.CONTAINER) AS FirstOfCONTAINER,
w_releaseln.LOAD_NO, w_releaseln.PO_NO, Sum
(w_releaseln.QTY) AS SumOfQTY
FROM w_releaseln
GROUP BY w_releaseln.PRODNO, w_releaseln.LOAD_NO,
w_releaseln.PO_NO
HAVING (((First(w_releaseln.RELEASENO))="004118"));

which is fine - my problem is that there is one other
field called seq which needs to be a consecutive number
starting with one by release number. Ex:

release prod Qty seq
004118 ABC 100 1
004118 DEF 300 2
004118 ghi 250 3

004119 ABC 50 1
004119 DEF 100 2

I need to add to my append the seq or do an update after
the append to get my seq.

Thanks in advance for your help.

Pat

 

[John Vinson]

which is fine - my problem is that there is one other
field called seq which needs to be a consecutive number
starting with one by release number.

Try using a custom VBA increment function: air code, untested, but it
should work:

Public Function SeqNo(vRelease As Variant) As Integer
Static OldRelease As Variant ' default to NULL
Static iNext As Integer
If IsNull(OldRelease) Then OldRelease = vRelease
If OldRelease = vRelease Then iNext = 0
iNext = iNext + 1
SeqNo = iNext
End Function


INSERT INTO w_releaseln_edi ( RELEASENO, PRODNO,
PROD_DESC, BOXES, [CONTAINER], LOAD_NO, PO_NO, QTY, SEQ)
SELECT DISTINCT First(w_releaseln.RELEASENO) AS
FirstOfRELEASENO, w_releaseln.PRODNO, First
(w_releaseln.PROD_DESC) AS FirstOfPROD_DESC, Sum
(w_releaseln.BOXES) AS SumOfBOXES, First
(w_releaseln.CONTAINER) AS FirstOfCONTAINER,
w_releaseln.LOAD_NO, w_releaseln.PO_NO, Sum
(w_releaseln.QTY) AS SumOfQTY, SeqNo([RELEASENO])
FROM w_releaseln
GROUP BY w_releaseln.PRODNO, w_releaseln.LOAD_NO,
w_releaseln.PO_NO
HAVING (((First(w_releaseln.RELEASENO))="004118"));

 

[pat]

Hi John:

Thanks for your quick response.

Can you give me a little help with this, I'm not very
efficient with vb.

I created a form based on my table. I thought that I
would need to run the vb process from the form level.

I selected code builder then current. I inserted your
code. Then I press run and it asks me for macro name???

I know I am missing something. I think if I can get pass
this it will work.

Thanks in advance for your assistance.

Pat

-----Original Message-----

which is fine - my problem is that there is one other
field called seq which needs to be a consecutive number
starting with one by release number.

Try using a custom VBA increment function: air code, untested, but it
should work:

Public Function SeqNo(vRelease As Variant) As Integer
Static OldRelease As Variant ' default to NULL
Static iNext As Integer
If IsNull(OldRelease) Then OldRelease = vRelease
If OldRelease = vRelease Then iNext = 0
iNext = iNext + 1
SeqNo = iNext
End Function


INSERT INTO w_releaseln_edi ( RELEASENO, PRODNO,
PROD_DESC, BOXES, [CONTAINER], LOAD_NO, PO_NO, QTY, SEQ)
SELECT DISTINCT First(w_releaseln.RELEASENO) AS
FirstOfRELEASENO, w_releaseln.PRODNO, First
(w_releaseln.PROD_DESC) AS FirstOfPROD_DESC, Sum
(w_releaseln.BOXES) AS SumOfBOXES, First
(w_releaseln.CONTAINER) AS FirstOfCONTAINER,
w_releaseln.LOAD_NO, w_releaseln.PO_NO, Sum
(w_releaseln.QTY) AS SumOfQTY, SeqNo([RELEASENO])
FROM w_releaseln
GROUP BY w_releaseln.PRODNO, w_releaseln.LOAD_NO,
w_releaseln.PO_NO
HAVING (((First(w_releaseln.RELEASENO))="004118"));




.