address book from excel to access

[mhmaid]

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

 

[KARL DEWEY]

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]

Fprgot to say autofill column A with the A2 formula.
--
KARL DEWEY
Build a little - Test a little

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

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

 

[mhmaid]

thank you for reply..and sorry for delay in my response as i was on leave
I have tried that but i got the following error msg:
data type mismatch in criteria expression
when i imported the data , columns one and two imported as number fields ,
the rest as text
I also tried changing the first two to text , the error msg stoped , but
unfortunatley i am getting a blank sheet (I can see the new headings but no
records)
what could be the problem
thanks

 

[KARL DEWEY]

I have tried that but i got the following error msg: data type mismatch in
criteria expression
At what point did you get the error message, what action were you doing?
The first two are numbers and rest text.

You can try coping the Excel data and Paste Append to Access.