Importing 2 columns from excel into access

[Fost]

Hi,

I was using Excel, but the data got to long…

We do transportation, I have two columns, let's call them
Address a and address b… we usually do round trips
Which means 2 Records… One is from address a to address b
And another from address b to address a… it's not always like that
The return can be to anywhere… I want to make one table called 'locations'
I can set the relationships to both fields using that table… or so I think…
But what about the last five years worth of data I can't seem to split my data correctly…

How would I go about indexing something like that… Most of the addresses
Are the same… So it will be a big savings to combine it

Thank you

 

[pbaldy]

I'm not clear on the question. You could certainly have a locations table that related to your trips table. In relationships or queries you'd include it twice. One instance would join to the from location, the other to the to.

 

[DennisOJensen]

[FONT="Courier New"]
Locations Table
     LocationId As Long  <Key>
     Street     As String
     City       As String
     State      As String

Trips Table
     TripId As Long  <Key>
     From   As Long  <Foreign Key>
     To     As Long  <Foreign Key>
[/FONT]

Okay that should give you the basic structure of what you seem to be looking for.

Note you can use AutoNumber if you want but I have found those to be problematic over the years and thus avoid them like the plague as a little extra effort upfront on my part usually saves me a load of headaches later on.

 

[JLCantara]

pbaldy gives you the solution for Access usage. For the Excel data, I would import them into an Access table. Not knowing the structure of Excel data I can't go further but there is a solution to this issue...

By the way, I am using Access for 20 years and I never had any problems with AutoNumber.

 

[DennisOJensen]

Yep you might not ever encounter the issues behind autonumbers but I get to work on systems that border on or need to move to a bigger database as access does have its limits and the headache of autonumbers has been one of the issues I have to deal with for more than one customer. Which can easily have been avoided by not using them to begin with. Autonumbers are really just lazy coding -- if you need a unique value and do not want to use your data (as it should be) then use a uniqueidentifier that is what it is for but most often than not Autonumbers and uniqueidentifiers for that matter get way over used and used wrongly because one does not have to put any thought into implementing them to use them. Of course this is just my opinion, and right or wrong others will do what they want to do regardless.

 

[JLCantara]

Well M. KnowsItAll, can you give an example of AutoNumber failure?
I think you are just an arrogant bubble mouth...