RE: address book from excel to access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I would insert a column to the left of column A and enter 0 (zero) in the new
cell A1.
Then in A2 copy this --
=IF(B2=B1,A1+1,1)
As long as column B contents is the same as the row above it adds one to the
A column, else it starts with one.

Import into Access. I used column letters as fields - A, B, C, etc.
Use this query to put the data into a single record for each.

SELECT mhmaid.B, mhmaid.C AS Dr_Name, mhmaid_1.C AS Hospital, mhmaid_2.C AS
[Road no], mhmaid_3.C AS [Block no], mhmaid_4.C AS City, mhmaid_5.C AS
Country, mhmaid.D AS Phone
FROM ((((mhmaid INNER JOIN mhmaid AS mhmaid_1 ON mhmaid.B = mhmaid_1.B)
INNER JOIN mhmaid AS mhmaid_2 ON mhmaid.B = mhmaid_2.B) INNER JOIN mhmaid AS
mhmaid_3 ON mhmaid.B = mhmaid_3.B) INNER JOIN mhmaid AS mhmaid_4 ON mhmaid.B
= mhmaid_4.B) INNER JOIN mhmaid AS mhmaid_5 ON mhmaid.B = mhmaid_5.B
WHERE (((mhmaid.A)="1") AND ((mhmaid_1.A)="2") AND ((mhmaid_2.A)="3") AND
((mhmaid_3.A)="4") AND ((mhmaid_4.A)="5") AND ((mhmaid_5.A)="6"));

--
KARL DEWEY
Build a little - Test a little


"mhmaid" wrote:

hello everyone
my boss gave me an excel file which contain a list of addresses for
hospitals and doctos
the first column is a serial number , which is a merged cell , as each
address will take 5 to 6 row , i.e.for those 6 rose , there is one serial
number in the first column
second column is the address details

ie.
dr name
hospital
road no etc
block no
city
country

in this case , one address is taking 6 rows in the xl sheet.
the third column is telephone numbers, the the last column is fax numbers

now i am planning to copy this to my access db , and thought that the
correct way is that each address should have only one record in the db.ie
each line in the address details column should be stored in a separate field
in the same record in my db.

any suggestions on how to accomplish this thing will be highly appreciated.
thanks
.

Relevant Pages

  • address book from excel to access
    ... hospitals and doctos ... the first column is a serial number, which is a merged cell, as each ... each line in the address details column should be stored in a separate field ...
    (microsoft.public.access.queries)
  • RE: address book from excel to access
    ... "KARL DEWEY" wrote: ... hospitals and doctos ... the first column is a serial number, which is a merged cell, as each ... each line in the address details column should be stored in a separate field ...
    (microsoft.public.access.queries)
  • RE: VLOOKUP matches
    ... Since the number in the first column are in order 1, A1+1,... ... preformatting the cell to Text ... You can not tell by checking the Format. ... random numbers from 1 to the total current number of employees. ...
    (microsoft.public.excel.misc)
  • Re: finding within an only table column
    ... each cell a word... ... Dim oCell As Word.Cell ... The Find find terms within the first column, ... but find terms within de second column too. ...
    (microsoft.public.word.vba.general)
  • Word vba Table
    ... cell content of first column on first page and copying into second cell of ... first column on second page,I extended it to copy last non empty cell ... >> Goto first page and select non empty cell from last in the first column. ... > Dim rngCell1 As Word.Range ...
    (microsoft.public.word.vba.customization)