is there a limit on append queries?

[pduncan]

I use Access 2003 -
I am writing an append query on a table that has a lot of fields. I am using the append because I am taking data from other tables, making formatting changes and/or calculations based on the data, and then saving it in a different (empty) table.

When I was about 1/2 way through making the query, I ran out of space (i.e. places to define my query). So, I continued where I left off with a second append query.

Next I ran the two queries using a docmd.openquery for each. My results were not as desired - I now have two rows - each partially filled - when I wanted just one row -
Any ideas or suggestions?


Thanks, PDUNCAN

 

[PHV]

Merge the sql code of the 2 queries in once.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

I tried that but was not able to get it to work...
I think perhaps because of everything else I am doing in the queries?

Here is the SQL from #1

INSERT INTO tblZFIleList ( EDTN, DIVJ, EDDT, KCO, ADSC, VINV, TORG, CN, DKJ, EDLN1, EDLN2, EDBT, AN8, DDJ )
SELECT DISTINCT Right([PONo],Len([PONo])-5) AS NewPO, Format([InvDate],"mm/dd/yy") AS NewDate, Format((Date()),"mm/dd/yy") AS tranDate, "0"+[ARSLoc] AS A, "               " AS discAval, tblInvoice.InvoiceNo, "CWILSON" AS TransOrig, "                         " AS DocMatch, "        " AS CheckDte, "      1" AS Line1, "      2" AS Line2, "00000001"+"       " AS batch, "   29561" AS ven, DateAdd("d",30,(Format(tblInvoice!InvDate,"mm/dd/yy"))) AS DDJ
FROM tblInvoice
WHERE ((([Forms]![frmMain1]![lstReadyForZfile])=[tblInvoice]![InvoiceNo]));

Here is #2

INSERT INTO tblZFIleList ( DDNJ, ADSC, MCU1, MCU2, OBJ1, OBJ2, RMK, EXR )
SELECT DISTINCT "        " AS ddnj, "                " AS adsc, [ARSLoc]+"0202" AS mcu1, "91250202" AS mcu2, "5205  " AS obj1, "5206  " AS obj2, [PONo]+" Carrier" AS rmk, [PONo]+" Carrier" AS exp
FROM tblInvoice
WHERE ((([Forms]![frmMain1]![lstReadyForZfile])=[tblInvoice]![InvoiceNo]));

Could you help me merge these two?

FYI - the "spaces" in some of the fields are there because I will be taking this data and printing it to a text file (delimited) - thanks!

Thanks, PDUNCAN

 

[PHV]

Something like this ?
INSERT INTO tblZFIleList ( EDTN, DIVJ, EDDT, KCO, ADSC, VINV, TORG, CN, DKJ, EDLN1, EDLN2, EDBT, AN8, DDJ,
DDNJ, ADSC, MCU1, MCU2, OBJ1, OBJ2, RMK, EXR)
SELECT DISTINCT Right([PONo],Len([PONo])-5) AS NewPO, Format([InvDate],"mm/dd/yy") AS NewDate, Format((Date()),"mm/dd/yy") AS tranDate, "0"+[ARSLoc] AS A, " " AS discAval, tblInvoice.InvoiceNo, "CWILSON" AS TransOrig, " " AS DocMatch, " " AS CheckDte, " 1" AS Line1, " 2" AS Line2, "00000001"+" " AS batch, " 29561" AS ven, DateAdd("d",30,(Format(tblInvoice!InvDate,"mm/dd/yy"))) AS DDJ,
" " AS ddnj, " " AS adsc, [ARSLoc]+"0202" AS mcu1, "91250202" AS mcu2, "5205 " AS obj1, "5206 " AS obj2, [PONo]+" Carrier" AS rmk, [PONo]+" Carrier" AS exp
FROM tblInvoice
WHERE ((([Forms]![frmMain1]![lstReadyForZfile])=[tblInvoice]![InvoiceNo]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

Thank you - I will give this a try but have another issue - what do I do to get around all of the fields that are "quoted"?

Thanks, PDUNCAN

 

[JonFer]

Additional columns can be added in the query design window. If you double-click on a field in a table, a new column will be inserted automatically if needed.

John

http://www.interautomateconsulting.com

 

[JonFer]

If you double-click on a field in a table in the query design window, a new column will be added in the lower panel if needed.

John

http://www.interautomateconsulting.com