Best way to set up access database with imported excel spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel spreadsheet for cable information including Origination and
Destination connection point info. The Origination and Destination grouped
fields have the same field headings. I wanted to import the spreadsheet into
2 access tables, tblCable and tblConnectionPoint linked by the CableNum
field. How can I do this, or is there a better way to set up the tables? I
mainly need to add,update,remove records and to sort on several fields. For
example, both Origination and Destination field groups have a RoomNum. I
need to be able to sort on RoomNum and have both Origination and Destination
RoomNums treated the same.
If the spreadsheet is imported as 1 table, I have sorting issues.
If the spreadsheet is imported as 2 tables, I can only import either the
Origination or the Destination fields into tblConnectionPoint.
If the spreadsheet is imported as 3 tables, there are still sorting issues.

I seem to be going around in circles. Please advise.
 
I'm not completely understanding what you have here, but assuming that you
just need to do this import once, as you're going to maintain the data in
ACCESS from now on, just import the spreadsheet into a single, temporary
table. Then use append queries to put the data where you want the data to be
in the two permanent tables.
 
Hi,

I feel your pain! I was involved in implementing the Transport Engineering
Database at ATT Wireless. There were several huge obstacles to overcome but
first and foremost was in gathering and cleaning and validating the data
that was supposed to be there in the first place. Excel spreadsheets were
the most common resource document but there were some Word tables, paper
databases, napkins, etc. There was no standard template used in any of that
stuff. Enough sea stories :-)

Part of the information in your Excel data is contiguity and placement.
That information won't translate directly into Access. There are lots of
issues from trying to get from a flat file into a relational database.

You've implied that other information exists that is included with each
Source/Destination record. One piece of information you haven't mentioned
but that might make things more feasible would be the connection board and
terminal information in each room where the end of a given cable terminates.
In other words, the cable tech doesn't just drag the end of the cable to a
room and dump it. I hope! He/she should connect it to something. In a
low-tech, high maintenance cost operation, it would come out of the wall and
connect directly to the terminating equipment. I hope you have some kind of
DXC instead

You're right about sorting issues but there may have to be some
pre-processing of the data before it goes to Access. I can understand why
you're going around in circles. It seems that there must be some magic way
to just "do it" and it's all over. No way.

That being said, getting it into Access will be well worth the bother. Be
aware there's going to be some bother and you may need to do or have done
some work in the real world (non-software).

HTH
 
Back
Top