Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the last record
2 questions - Text File example:
Account,Status,Val 001,NB,0 001,PI,50 001,PI,100 001,CE,100 002,PE,500 002,PI,250 002,PU,0 002,PU,0 003,PE,100 003,CE,100 I would like to pick up ALL records of any account where there is NO CE status and write it out to a separate file i.e. 002,PE,500 002,PI,250 002,PU,0 002,PU,0 I would like to pick up the LAST record of any account where PU = 0 002,PU,0 and write it out to a separate file It is easy to do this using a database but a need has arisen to do it upfront i.e before importing the data into the database and I thought a simple VBA app will suffice. I'm not looking for anything intricate - just a simple example of how to code this. Hil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the last record
Can anyone help out?
"Hilton" wrote in message ... 2 questions - Text File example: Account,Status,Val 001,NB,0 001,PI,50 001,PI,100 001,CE,100 002,PE,500 002,PI,250 002,PU,0 002,PU,0 003,PE,100 003,CE,100 I would like to pick up ALL records of any account where there is NO CE status and write it out to a separate file i.e. 002,PE,500 002,PI,250 002,PU,0 002,PU,0 I would like to pick up the LAST record of any account where PU = 0 002,PU,0 and write it out to a separate file It is easy to do this using a database but a need has arisen to do it upfront i.e before importing the data into the database and I thought a simple VBA app will suffice. I'm not looking for anything intricate - just a simple example of how to code this. Hil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select the last record
"Hilton" wrote ...
Text File example: Account,Status,Val 001,NB,0 001,PI,50 001,PI,100 001,CE,100 002,PE,500 002,PI,250 002,PU,0 002,PU,0 003,PE,100 003,CE,100 I would like to pick up ALL records of any account where there is NO CE status and write it out to a separate file i.e. It is easy to do this using a database but a need has arisen to do it upfront i.e before importing the data into the database As you are database proficient, you will no doubt appreciate that a sql solution is simplest. A text file can be queried as a data source using a Jet provider/driver. Therefore, execute this Jet syntax sql against a Jet datasource e.g. a connection to a .mdb or .xls (but not a text file connection), in a query object in the MS Access app, etc: SELECT Account,Status,Val INTO [Text;Database=C:\MyFolder\;].MyNewTextFile#txt FROM [Text;Database=C:\MyFolder\;].MyExistingTextFile#txt WHERE Status<'CE' ; 002,PE,500 002,PI,250 002,PU,0 002,PU,0 I would like to pick up the LAST record of any account where PU = 0 002,PU,0 and write it out to a separate file 'Last row' has no meaning in sql. If you had a timestamp column, we could determine which is the most *recent* row. In other words, you need to specify a sort order before we can determine which is the 'last'. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to select one record of each from a column | Excel Discussion (Misc queries) | |||
How to select one record of each from a column | Excel Worksheet Functions | |||
Select last record for each account | Excel Discussion (Misc queries) | |||
How do I select the correct record? | Excel Discussion (Misc queries) | |||
list box record select | Excel Programming |