Query to create 3 columns from 1?

[missangie]

I have very limited knowledge of Access queries compared to most of you so I’m hoping you can give me some ideas to achieve this result. I have one column of data to begin with.
Like this -

XY487
A
IMG_0012.jpg
IMG_0013.jpg
XY897
B H F
IMG_0014.jpg
IMG_0015.jpg
IMG_0016.jpg
IMG_0017.jpg
XY002
A B
IMG_0018.jpg
IMG_0019.jpg


I need to turn that one column into 3 columns like this -

ID # Order #s Image
XY487 A IMG_0012.jpg
XY487 A IMG_0013.jpg
XY897 B H F IMG_0014.jpg
XY897 B H F IMG_0015.jpg
XY897 B H F IMG_0016.jpg
XY897 B H F IMG_0017.jpg
XY002 A B IMG_0018.jpg
XY002 A B IMG_0019.jpg


Right now I am removing all the carriage returns & using “find & replace” to re-sort the data in Word. It’s definitely a pain in the rear. Please let me know if you have another solution I can try. Thanks in advance! Angie

 

[oharab]

Yuck!
You can't do this in a straight query, you will need some jiggery pokery with code to do what you are asking.
How regular is the format? Are all ID's XY*, are all images IMG*?
Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[OhioSteve]

I can probably help you, but I will need clearcut characteristics which identify the three types of data. For example, your sample conforms to these rules:

1) ID# always starts with "XY"
2) image always ends with ".jpg"
3) order# does not start with "XY", and does not end with ".jpg"

Does all of the data conform to those rules? If not, can you provide a better set of rules?

How many records do you have?

 

[Crowley16]

this column of data, what format is it in?
a simple text document?
field in a table in your database?
excel spreadsheet?

Procrastinate Now!

 

[missangie]

Thanks for responding. Here is some more info.

ID# will always start with either XY, YXZ, XX, SLT, YXZW
Image will always start with IMG and end with .jpg
Order number will always be A, B, C, D, E, F, G, H, I, or some combination of those letters.

In general there will be about 1200 records.

The data comes from a basic text document.

I hope this helps, I think I see where you are going but I don’t think I know how to get there : )

 

[JonFer]

If you import the records into a table with an Autonumber field, you can probably generate the desired results with 2 queries assuming the record order is always ID, Order, Image (1 or more).

The first query should give you the autonumber for each ID field plus the autonumber for the next ID field (which would be Null on the last record).


John

http://www.interautomateconsulting.com

 

[missangie]

Shameless bump... I still can't figure this one out.
Cheers,
Angie

 

[OhioSteve]

I have been unable to logon for several days.

I suspect that you could make an excel spreadsheet that resembled your desired output. Then you could put the cells onto the clipboard, and use paste special to paste the results into another worksheet. Then it would be easy to import the records into Access.

I don't know exactly how to make the spreadsheet, but I suspect that excel's "if()" function would be helpful

 

[oharab]

Missangie,
The easy answer to your question, is there is no easy answer. You cannot open it into a query and have it fix itself. You will need to read the text file a line at a time and parse the data into a table.
Download the file at

http://www.mvps.org/access/modules/mdl0057.htm

and import in into your database. This is just an easy way of reading a text file.
Create a table in your database with the 3 fields you need:
ID,OrderNo,Image
Call it tblOrders

Now you will need to create a new module for the code. It will look a little like this:

Dim tf As New clsTextFile
Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim iStatus As Integer 'to store where in the file we are
Dim strID As String 'to store the ID
Dim strOrderNo As String 'to store the order no

iStatus = 0 'we are at the start of a record
With tf 'it's only 2 letters, but will save some time
    .FileName = "C:\MyTextFile.txt" 'The path to the file
    If .cfOpenFile = 0 Then  'if it opens ok - 0=no errors
        Set db = CurrentDb
        Set rst = db.OpenRecordset("tblOrders") 'the table we are going to read into
        .csGetALine 'get the first line of the file
        Do Until .EndOfFile 'keep on reading till you reach the end
            'Depending what's on the line, we need to do different things
            If iStatus = 0 Then 'we are at the start of a record
                    strID = .Text 'save the ID
                        'if we have read in the ID, the next line must be the order no.
                    .csGetALine 'read the next line
                    strOrderNo = .Text
                    iStatus = 1
                        'now we must read in all the images
                    .csGetALine 'get the next line, which should now have an image entry
                  Do Until iStatus = 0
                    'save the record using the variables we stored earlier
                    rst.AddNew
                        rst("ID") = strID
                        rst("OrderNo") = strOrderNo
                        rst("Image") = .Text
                    rst.Update 'save the record
                    If Right(.NextLine, 4) <> ".jpg" Then
                        'if the next line is not an image, we need to start again
                        iStatus = 0
                    End If
                    .csGetALine
                  Loop
                    
            End If
        Loop
        tf.cfCloseFile 'Close the text file
        rst.Close
        db.Close
    End If
End With


Set rst = Nothing
Set db = Nothing
Set tf = Nothing

I've run it against the sample you gave above and it seems ok. Let me know how you get on.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a

 

[JonFer]

missangie - Do you have an autonumber field in the table you're importing to? If not then add one and do an import.

Write a query like this to get the autonumber values for the rows representing the ID fields and the following ID field:

Select AutoNum, (Select Min(a.AutoNum) from YourTable as a where a.AutoNum>YourTable.AutoNum And a.FieldName Not Like "*.jpg" and a.FieldName Not Like "* *" and Len(a.FieldName)>2) as NextAutoNum
From YourTable
Where YourTable.FieldName Not Like "*.jpg" and YourTable.FieldName Not Like "* *" and Len(YourTable.FieldName)>2

The results should be two columns - the first has the autonumber value for the records with IDs in them and the second the autonumber for the next ID. Post some example records if you plan to do this and I'll give you the next query that will put everything how you want it.


John

http://www.interautomateconsulting.com