File Merge Help

[Guest]

Hi,

I am trying to merge txt files in access but when i am opening 2nd file
system replaces the 1st one. I want to merge 20 txt files in same table.
ie. 001.txt containing 100 records
002.txt containing 100 records

I want single access file of the 200 records

Can anyone help,

Thanks in advance

Aarif

 

[Guest]

Aarif:

If you use the import wizard to import the data (Files|Get External
Data|Import on the main database menu) select the option to create a new
table when importing the first file, then to append the data to an existing
table when importing the second file, selecting the table you created when
importing the first file. The rows from second file will then be appended to
the table.

Ken Sheridan
Stafford, England

 

[Guest]

Dear Ken,

Thank you very much for helping me i know excel but to work with access is
very difficult because i did not learn access, i am just doing it at mine own
efforts so i asked my question to Access Database New User Community. I know
that this is a silly question but will you help me again.

Your suggestion is working thanks again. I do have a text file of a bank
which data is in 4 seperate columns seperated by | ie.

Cheque no|Acount No| Amount | Path
003456|002405012345|0000001100000|01082006_S1_R07\4397556.tif

I want to know is

Amount is 11000.00 only but in text it comes in 13 digits i want to know
that when i import this file how to convert this text to Number with 2
desimal points.

Is there any website where i can get basic fundas of access so i do not need
to ask for such simplethings, let me know the website name.

Thank you again for helping me,

Aarif

 

[Guest]

Aarif:

Open the Access table in design view and change data type of the Amount
column to Currency. By default this will show the values in the system's
local currency format, but this can be changed if necessary in the table or
in a form or report by setting the Format property of the field. For example
100 in my local currency format (GBP) would be £100.00, but if I am working
with US data I can always change this to US Dollars by setting the Format
property to $#,##0.00. This would show the values as dollars with commas as
the thousands separator characters, e.g. $1,000,000.00 for a million dollars.

As regards websites a Google search for "Microsoft Access Tutorial" will
throw up a number of tutorial sites, but Access's own built in Help system
will give you pretty well everything you need. Don't hesitate to post any
questions you need help on here though, no matter how simple they might seem.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken,

Thank you for being so much helpful but i still not got the answer of my
question is that amount in my txt file is 0000001100000 i want it like
11000.00 in excel i can change formatting of cell into number and devided by
number gives me 11000.00 amount but how to do it in access is the real
problem for me.

So what should i do in access to format cell and devide by 100.

Thanks again,

Aarif

 

[Guest]

Aarif:

It depends whether you want to change the underlying value stored in the
field by dividing it by 100 or keep the underlying value but just show it
divided by 100.

To change the underlying value first change the field's data type to
Currency and then run an update query like so:

UPDATE YourTable
SET Amount = Amount/100;

If you just want to show it divided by 100 then you can do this in a query:

SELECT [Cheque No], [Account No],
FORMAT(Amount/100,"0.00"), Path;

or in a form or report you can set the ControlSource of a text box to:

=Format([Amount]/100,"0.00")

Ken Sheridan
Stafford, England