Can't sort a table

[davbib]

Hi All,

Here is my problem...I have a bunch of code that inserts records into a temp table that I've created and I export that table for upload to an ERP system. I realized now that the data isn't sorted when it's exported to a CSV file.

I either want to sort the data in the table using docmd.runsql UPDATE tablename before I export it, or use docmd.runsql SELECT and ORDER BY and put it in a recordset and then export it, but I'm not successful in either approach. I was hoping someone could give me some guidance.

I can't believe I've gotten this far and now I'm stumped on something as simple as a sort!

Dave

 

[PHV]

Export a query having an order by clause.

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

 

[SkipVought]

You mean that
[tt]
Select * from YourTable Order By YourField
[/tt]
does not work???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[davbib]

Thanks for the response. I have 2, so here they are


PHV:
I can do a "DoCmd.TransferText and export a query, but for everything else in my program, I am not using any Access queries, just sql statements and temp tables. The program is complete when a csv file is created and exported to 'My Documents' and it's sorted properly.

skipvought:
The select statement and Order By works fine, but I can't get the data captured in that statement into a CSV file in the format that I need.

Hope that helps. I'm still stumped!

Dave

 

[PHV]

Sorry, I can't understand why you can't export a properly sorted query when your temp table is ready for the transfer ...

 

[davbib]

I can, but I want to do the whole thing in a module WITHOUT running a saved query. I've pasted the relevant section. I want to get rid of the saved query "CreateCSV" and do it entirely in the module. So, the line of code I'm unhappy with is:

DoCmd.TransferText acExportDelim, "pending export spec", "CreateCSV", filestrPending


Here is the paste of the relevant section of code:

PendingSQL4 = "INSERT INTO TempPendingTable ( Cost_Type, Org, Item, Cost_Element, Sub_Element, Basis, Amount )" & _
" SELECT DISTINCT 'Pending' AS Pending, '291' AS Org, Appended_Items.Item, 'Material Overhead' AS Material_Overhead, MOH.[MOH Header], 'Total Value' AS Total_Value, MOH.[MOH Rates]" & _
" FROM MOH INNER JOIN (Appended_Items INNER JOIN Pricing ON Appended_Items.Item = Pricing.[Part Number]) ON MOH.L5 = Appended_Items.L5" & _
" WHERE ((([Appended_Items.Item_Type]) Like '*Finished Good*'))" & _
" GROUP BY 'Pending', '291', Appended_Items.Item, 'Material Overhead', MOH.[MOH Header], 'Total Value', MOH.[MOH Rates]"

' Round the amount number to 4 digits
PendingSQL5 = " UPDATE TempPendingTable" & _
" SET Amount = Round(Amount,4)"

'Change the data type from number to text to avoid scientific notation on the output.
PendingSQL6 = "ALTER TABLE TempPendingTable ALTER COLUMN Amount Text (10)"

DoCmd.RunSQL PendingSQL4
DoCmd.RunSQL PendingSQL5
DoCmd.RunSQL PendingSQL6

DoCmd.TransferText acExportDelim, "pending export spec", "CreateCSV", filestrPending

DoCmd.RunSQL "DROP TABLE TempPendingTable"

DoCmd.Hourglass False

Beep
MsgBox "Pending.csv has been exported to your 'My Documents' folder"

 

[PHV]

What are PendingSQL1,2,3 ?
I guess you can populate TempPendingTable in an ordered manner.

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

 

[davbib]

Can't because I've got data coming from different places with similar records.

 

[PHV]

Even with an UNION query sorted by ordinal position ???

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

 

[davbib]

Can't use a union query because the tables are all very different. Everything in my TempPending table is perfect, other than the fact I can't export it sorted using just VB (no saved query).

 

[PHV]

Can't use a union query because the tables are all very different
???
You have several append queries populating the very same TempPending table, right ?
So, you may have a single union query sorted like you want.

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

 

[davbib]

perhaps I dismissed too quickly. OK...Let me try that. Thanks for the input. I appreciate your help and your patience with me!

Dave

 

[PHV]

Why not posting all your PendingSQL? strings ?

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

 

[boblarson]

Just a point of information here -

Tables in Access are not stored in any kind of sorted order. They are, DISPLAYED using a query (when you "open" a table you are just viewing a built-in query).

So, when you export you are using a saved query anyway, it just isn't exposed to you. You should create your own query/queries and order them like you wish and export.



Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[davbib]

Thanks Bob, but what I am trying to avoid is a situation where I can't see everything that is happening in my subroutine. I'd like to see all the logic in the subroutine itself so that I may be able to open it in the future and quickly see what is happening without having to bounce around between the subroutine and saved queries. Also, it makes explaining to others easier.