Navigate Table

[LEXCERM]

Hi there,

I have attached an image of a table which contains order details which has been imported from a txt file.

I require the postcode (in the customer column) to be transferred to the postcode column next to the despatch date for each order. The address rows can vary but there is never a blank line in between an orders' address - only a blank line between each actual order. And the postcode will ALWAYS appear at the end of the address.

To finalize the process, I would query the data based on the Despatch date column (ISDATE function) to then filter the details to show one row only.

So basically, how can I navigate the table to transfer the postcode to the postcode column?

Thanks in advance,
Paul.

 

[neileg]

No attachment!

 

[LEXCERM]

Woops!. Sorry about that. My file exceeded the 100kb so had to make smaller.

 

[Ron_dK]

Create a query based on your table and insert :
Postcode: (Right([Customer address],5))

This will give you the last five characters of the Customer Address field.
I assume that the USA postcode is 5 characters. If not , modify the ,X
to meet the total number of your postcode characters.

 

[LEXCERM]

Thanks for replying Rak.

I have attached an example DB to explain the problem a bit more clearly.

TABLE1 is the imported data. In QUERY1 I have added your RIGHT expression and also an ISDATE expression which would summarize the data onto one line. I only need this information to appear, but the POSTCODE does not appear next to the date. Hence I require some form of navigation within the table to transpose the postcode next to the despatch date.

Thanks for helping,
Paul.

 

[neileg]

Your import routine has generated separate records for each line in the address. Its going to be very difficult to handle anything like this. It would be much easier to deal with the data with the address data all in one field, which is where rak's code would have worked.

 

[Ron_dK]

I agree with Neil.
Why not change your table to something like :

Table1 :
Field name...................Data type
Order number...............Number
Customer Name............Text
Customer address.........Text
Postcode.....................Text
Cases..........................Number
Units...........................Number
Despatch date..............Date/time

and ( assuming you import from an excel sheet) ensure that the import
data is entered into the relevant field.


Hth,
Ron

 

[LEXCERM]

The txt files are FTP'd from our stock control system and the data is displayed as per my example - with different rows of address lines.

I usually run an excel routine to fashion the data as I want it, however, I am only limited to 65,000 rows. Sometimes a report can be over 250,000 rows long and excel cannot deal with this - unless I extract the data from our stock control systems in sections, which then becomes tedious and time consuming.

I have created a routine which imports the txt files into Access, then running a query using the IsDste expression to give me nice one liners, but I need to somehow transpose the postcode onto the same line as well.

In excel I would use the following type of code to do this:

Range("e2").Select 
Do While ActiveCell.Row <> 65536
	ActiveCell.Offset(0, -3).Range("A1").Select
	Selection.End(xlDown).Select
	Selection.Copy
	ActiveCell.Offset(0, 3).Range("A1").Select
	Selection.End(xlUp).Select
	ActiveCell.Offset(0, 1).Range("A1").Select
	ActiveSheet.Paste
	Application.CutCopyMode = False
	ActiveCell.Offset(0, -1).Range("A1").Select
	Selection.End(xlDown).Select
Loop
Range("a1").Select

This basically navigates the spreadsheet and copies and pastes the data.

I thought some sort of DAO Recordset Append type routine might be able to do this.

Thanks again,
Paul.