Creating relationships with imported data - Please help I'm a newbie

[Kosmos]

Hey :) hopefully someone can help me with this...I decided to take on the task of programming an access database for my legal co-op/internship...I'm studying law and music production on the side...most of the background I have in programming has to do with music production...

The program I'm creating pulls data from an excel sheet (with defined fields) and brings them into an access database. The data being pulled is the following:

FirstName|LastName|AssetTag|SerialNumber1|SerialNu mber2|SerialNumber3

From here the Asset Tags must correspond to a serial number (there are three different serial numbers because we get them from different locations...but they should all be the same)

The Serial Numbers contain the Asset Tags in between letters such as WAL0100020003LT. To remove the letters I wrote a module which I used in an SQL based query to remove the letters and place the revised Serial Number in a new table in a new column.

The First Name and Last Name are always entered with the Serial Number so they correspond in their rows.

I did not originally understand how Access works so when I was inputing the data where everything in the same row corresponded (including serial numbers), I thought I had a working program.

The program checks for null cells and when any row has a null cell there is a check value at the end of the row by which is used to arrange the results in ascending order so problems show up on top.

When I actually put the program to the test, when the rows with serial numbers did not match to the rows with asset tags (and first and last names), my world fell apart...the rows stayed together...it did not arrange based on relationships.

I am beginning to think that perhaps relationships are not the way to do this, but if relationships do not work in such a way I fear I will be in way over my head. Do relationships only work across tables?

If there's anyone who could offer me some insight, it would be greatly appreciated...I want to get this project completed before it is considered time lost or a wasted effort. In return if you ever need some electronic atmospheric music for a program you're writing, I'm your man.

Cheers,

Kosmös

[NeoPa]

Normally, if you create a query in Access, it will recognise relationships and automatically join the tables in a similar way.
Beyond that, no - only tables have the relationships.

[MMcCarthy]

To create a fully cascading relationship in Access you must do the following using Table1 and Table2 as examples:


Table1
=====
T1_ID (PK must be unique)
Name

Table2
=====
T2_ID (PK must be unique)
Name
T1_ID (foreign key reference to PK of Table1)


There cannot be any data in Table2's T1_ID that is not present in Table1's T1_ID

Both fields must be of the same data type

[Kosmos]

in response to this and the PM I sent you: Thanks for getting back to me but the problem is that if I create a primary key with autonumber I will run into a problem because I don't have every field arranged exactly the same way which is why I was suggesting adding a 0 to the asset numbers where there are missing numbers but now realize I will have more than one 0 which means I will run into problems with the primary key...maybe I could insert an autonumber in the empty rows instead? since the asset numbers are big numbers like 1983859384 and usually there won't be more than a couple of numbers missing, if any. Then for my later tests I could check if the number is less than 1000 then it is considered a missing value? but then again are there any other options you can think of because I wouldn't like to limit the user to anything although at the amount of missing asset tags currently, this company would have to be about 500 times bigger for this to be a problem.

[Kosmos]

To create a fully cascading relationship in Access you must do the following using Table1 and Table2 as examples:


Table1
=====
T1_ID (PK must be unique)
Name

Table2
=====
T2_ID (PK must be unique)
Name
T1_ID (foreign key reference to PK of Table1)


There cannot be any data in Table2's T1_ID that is not present in Table1's T1_ID

Both fields must be of the same data type

Re: Creating relationships with imported data - Please help I'm a newbie
in response to this and the PM I sent you: Thanks for getting back to me but the problem is that if I create a primary key with autonumber I will run into a problem because I don't have every field arranged exactly the same way which is why I was suggesting adding a 0 to the asset numbers where there are missing numbers but now realize I will have more than one 0 which means I will run into problems with the primary key...maybe I could insert an autonumber in the empty rows instead? since the asset numbers are big numbers like 1983859384 and usually there won't be more than a couple of numbers missing, if any. Then for my later tests I could check if the number is less than 1000 then it is considered a missing value? but then again are there any other options you can think of because I wouldn't like to limit the user to anything although at the amount of missing asset tags currently, this company would have to be about 500 times bigger for this to be a problem.

[MMcCarthy]

Thanks for getting back to me but the problem is that if I create a primary key with autonumber I will run into a problem because I don't have every field arranged exactly the same way

Kosmos

I don't understand what you mean. The Autonumber primary key is an independent identifier of each record in a table. It is not dependent on the data in that record. If you are talking about a sort order on the table then tables are not designed for that and it usually takes place in a query.

which is why I was suggesting adding a 0 to the asset numbers where there are missing numbers but now realize I will have more than one 0 which means I will run into problems with the primary key

Once you have an independent primary key you can use a default of 0 for the asset number. However, if you simply want to check for missing asset numbers I would suggest changing the data type to a text field and running a query based on something like:

 
SELECT * FROM TableName
WHERE AssetNumber Is null OR AssetNumber= "";
 
OR
 
SELECT * FROM TableName
WHERE Len(AssetNumber) < 2;