Access "batch" question 1

[finbib]

My company is transitioning business software. In the interim, we are forced to transfer data from an IBM uniVerse database to a SQL 2000 database. I have been using a combination of Excel/ODBC and DTS to transfer the data. It works well, but it is a several step process.

I've begun to transition the transfer process using Access to pull the data using a SQL pass-thru query, then an append query to add it to the SQL database.

In the A/R and A/P queries, there is a batch number field used to aggregate all of the transactions for a day. However, there is no batch number field in the G/L query.

Is there a way to gather a day's G/L transactions together and assign it a batch number? And then add that batch number in the correct place in the append query?

Here is the append query:

INSERT INTO SQLtable ( JEBNR, JEPLT, JECOMP, JEPDAT, JESRCE, JEREV, JERDAT, JEENT1, JEENT2, JEACCT, JESUB, JEUNIT, JEPROJ, JECMNT, JEICCO, JEREF, JEJECU, JETRAN)
SELECT qsGLTable.JEBNR, qsGLTable.JEPLT, qsGLTable.JECOMP, qsGLTable.JEPDAT, qsGLTable. JESRCE, qsGLTable.JEREV, qsGLTable.JERDAT, qsGLTable.JEENT1, qsGLTable.JEENT2, qsGLTable.JEACCT, qsGLTable.JESUB, qsGLTable.JEUNIT, qsGLTable.JEPROJ, qsGLTable.JECMNT, qsGLTable.JEICCO, qsGLTable.JEREF, qsGLTable.JEJECU, qsGLTable.JETRAN
FROM qsGLTable
WHERE ((qsGLTable.JEREF)='POSTING SUMMARY');

qsGLTable is the name of the SQL pass-through query.

TIA,

Bob

 

[Golom]

Here's a general pattern

INSERT INTO SQLtable ( [red]BATCHNUMBER, [/red]JEBNR, JEPLT, JECOMP, JEPDAT, JESRCE, JEREV, JERDAT, JEENT1, JEENT2, JEACCT, JESUB, JEUNIT, JEPROJ, JECMNT, JEICCO, JEREF, JEJECU, JETRAN)
SELECT [red]'123' As [BatchNumber], [/red]qsGLTable.JEBNR, qsGLTable.JEPLT, qsGLTable.JECOMP, qsGLTable.JEPDAT, qsGLTable. JESRCE, qsGLTable.JEREV, qsGLTable.JERDAT, qsGLTable.JEENT1, qsGLTable.JEENT2, qsGLTable.JEACCT, qsGLTable.JESUB, qsGLTable.JEUNIT, qsGLTable.JEPROJ, qsGLTable.JECMNT, qsGLTable.JEICCO, qsGLTable.JEREF, qsGLTable.JEJECU, qsGLTable.JETRAN
FROM qsGLTable
WHERE ((qsGLTable.JEREF)='POSTING SUMMARY');

You will obviously need to adjust that depending on exactly where you are keeping the batch number (i.e. in a table; in a variable; etc.)

 

[finbib]

Golom,

Your response sparked some excellent thoughts in my head, and I have been able to get this worked out.

Star for you.

Cheers,

Bob