Add a (sequential not autonumber) number in append query

D

DIH

OK,

I have an append query that will send data to an as400 system. There is
a field in the as400 table that simply wants a number for each record
(we'll call this field batchnum). Somehow I need the append query to
write this batchnum into each record that get appended. The field name
in the as400 table is called INTRN. For my use now, everything is still
in Access.

For example (only a few of the real data fields are listed):

DATEprod ACT THEO batchnum
8/10/09 500 750 1
8/11/09 530 750 2
8/12/09 570 750 3
etc 4
etc 5

Again, DATEprod, ACT, THEO (and a bunch of others)are real data. It's
the batchnum that needs to get generated somehow.

Here is the current sql:

INSERT INTO BPCSUSRF_NINW ( INPROD, INWHSE, INLOC, INQTY, INREF, INDATE,
INTRAN, INREAS )
SELECT [DAILY SHIFT].CODE, "42" AS Ware, [DAILY SHIFT].LOCATION,
IIf([palletconfig]=True,([actual])/([units per layer]*[layers per
pallet]),[actual]) AS ACT,
IIf([palletconfig]=True,([theoretical])/([units per layer]*[layers per
pallet]),[theoretical]) AS THEO, Format([DATEprod],"yyyymmdd") AS DATE1,
"MK" AS MKTrans, [DAILY SHIFT].[WORK CENTER]
FROM [product codes] INNER JOIN [DAILY SHIFT] ON ([product codes].[Line
#] = [DAILY SHIFT].LINE) AND ([product codes].Code = [DAILY SHIFT].CODE)
WHERE ((([DAILY SHIFT].DATEprod)=[forms]![frmBPCS]![txtDate]) AND
(([DAILY SHIFT].SentToBPCS)=No))
GROUP BY [DAILY SHIFT].CODE, "42", [DAILY SHIFT].LOCATION,
IIf([palletconfig]=True,([actual])/([units per layer]*[layers per
pallet]),[actual]), IIf([palletconfig]=True,([theoretical])/([units per
layer]*[layers per pallet]),[theoretical]), Format([DATE],"yyyymmdd"),
"MK", [DAILY SHIFT].[WORK CENTER]
HAVING (((IIf([palletconfig]=True,([theoretical])/([units per
layer]*[layers per pallet]),[theoretical]))<>0));

Any help is greatly appreciated!

Dave
 
D

DIH

The destination table is on an as400 (of which I know basically
nothing). The table in Access does contain an autonumber but it can't be
used because there are two append queries which writes the same data
twice (with a couple of small variations). This would then put the same
autonumber twice in the as400 table.

Unless, you're talking about putting the autonumber in the table on the
as400. Is that possible? I don't really know anything about the system
I'm writing to.
 
R

Rick Brandt

The destination table is on an as400 (of which I know basically
nothing). The table in Access does contain an autonumber but it can't be
used because there are two append queries which writes the same data
twice (with a couple of small variations). This would then put the same
autonumber twice in the as400 table.

Unless, you're talking about putting the autonumber in the table on the
as400. Is that possible? I don't really know anything about the system
I'm writing to.
Click to expand...

If you don't have too old a version of the operating system on the AS400
then its database does support IDENTITY numeric fields. A little
different from AutoNumber but the behavior is similar enough for what you
want.

That you would be able (allowed) to alter the AS400 table first has to be
determined. Programs on the AS400 that use that table would (in most
cases) need to be recompiled if the table is altered. It's generally a
much bigger deal to alter table designs in an AS400 environment than in
other server database systems.
 
J

John Spencer

If the number just has to be unique (not sequential) then just add an
offset number to the second query that will make the number larger than
the largest sequential number

First query
INSERT INTO xxx
SELECT AutonumberField as SeqNumber, ....
FROM AccessTable

SecondQuery
INSERT INTO xxx
SELECT AutoNumberField + 100000 as SeqNumber, ....
FROM AccessTable

If you were really creative you might be able to get the Max value of
the autonumber field and add that

INSERT INTO xxx
SELECT AutonumberField + (SELECT MAX(AutoNumberField) FROM AccessTable),
....
FROM AccessTable

Another option would be to use a temporary table with an autonumber
field and dump your data into it and then export to the AS400 from the
temporary table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Top