Problem with Primary Key

[Armine]

Hi everyone

I am importing data from excel and I want to put a primary key on a column but it doesnt let me saying "primary key cant contain a Null value", coz I have a "NO" or "O" value. But i dont understand why is it a problem as I have specified the field as text not number.
Also is it necessary for the table to have a key ?

Tnx
Armine

 

[Mr Smin]

You don't have to have a primary key on a table, but if you do have one then all the values must be unique and cannot be null.

Access will offer to add a primary key when you import, but you can say no.

 

[Armine]

But i thought that a table MUST have a primary key ?

Also do you know if I can connect 2 primary keys? pls see the dtb attached and advise how would you connect the tables Import and year? Also Partner and Import table relationship I thought that "Source_code " in Partner must be key and be connected 1 to many to the "Partner _ code" in Import but it doesnt let me put a key.

P.s. the table values are imported from excel.

TNX

 

[GinaWhipp]

Primary Keys...
It is suggested that every table have a Primary Key but it is not mandatory.

You cannot join a Primary Key (Autonumber) to another Primary Ley (Autonumber). In linking tables to one another it's Primary Key to Foreign Key.

Tables/Field Names...
Having a tables with field names of 2010, 2011, 2012, etc... means your table is not properly normalized. You need to rethink your data structure. And since I do not understand what you are trying to accomplish I can't make any suggestions.

You have field names that are Reserved Words. This is going to give you problems down the road. For a complete list see...
http://allenbrowne.com/AppIssueBadWord.html

I'm also going to suggest some reading...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…
Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

And, remember, Access is not Excel!

 

[Armine]

Dear Gina
I will try to explain it in details and see attached the partial sample files.
The file Romania is one of the app. 20 countries I have data spreadsheets for which I need to import into excel, normalise create the relationships so I can use it afterwards. For all this spreadsheets the values of the cell can be found in the sample file "List" which I am attaching but it doesn't have to contain all the values that List file has.So in the file u can see several columns : Direction which is taken from "Directions" sheet of the List file , Goods which is coming from the "Goods" sheet of List , Partner_Name and Partner_code are coming from "Partner" sheet, Port_coast_Name and Port_coast_Code are coming from "county details" sheet. Then there are the values for each year.
So for the unique combination mentioned in each row of these criterias there are the values 2000-2012 and there were NA values which I replaced with -1 (just didn't know else what to do ).

I dont know kind of were to start even. The most problem for me is that I don't imagine at all what to do with the values of years 2000-2012 coz the way I have in the spreadsheets I think I cant use for upcoming queries or reports etc.

Will be waiting for questions or any help .
Thanks A LOT
Armine

 

[Mr Smin]

I think you've set a hard task moving a spreadsheet to a database.

I'm guessing your Year table contains totals of activity per year, taken from a spreadsheet. Do you still have the raw shipping movements data?

In a database you want to aim for separation of raw data from calculated reports/summaries, and 1 table should generally model 1 'thing' with each row being an instance of the thing.

Example would be:
tableContainers
fields:
containerID (primary key) (the unique ID for each container)
containerSize

tableContainerJourneys
fields:
containerID (foreign key, joined 1 at tableContainers to many at tableContainerJourneys)
journeyOrigin
journeyDestination
journeyStartDate
journeyEndDate


With this sort of structure, you then write queries to generate what I think is summary data in your Years table.


You can create relationships between Import and Partner but you will need to specify indexes in the table properties.

 

[GinaWhipp]

What is the spreadsheet that contains Years? All I see is a spreadsheet with years with data that means nothing. What Business are you tracking? Shipments? Import/Export?

 

[GinaWhipp]

Armine...

Please resist the urge to crosspost...
http://www.access-programmers.co.uk/forums/showthread.php?t=252615

You take time away from others seeking answers. Remember, we are volunteers trying to help as many as we can in our spare time.

 

[Armine]

Hi

Sorry for crossport I am kind of in a panic nothing works out

Gina, ya it is transportation so in the file Romania for example the columns represent certain criteria like the country it comes from where it goes etc. and then there is the amount of goods transported in 2000-2012. You can see attached the database approximately what I wanted to do I dont know how to connect this criterias and the time series for 2000-2012 to get a normalized form. find attached approximately what I wanna do, partially.
So you can see in the database attached tables "Sheet1" and "years" are imported from excel and I need to connect them but dont know how. So the unique combination of the criterias of the row 1 relates to the row one of the table Years.

Please ask questions if you dont understand what I wanna do in the database or if I am not explaining it correctly

Tnx for your time guys.

P.S. When I made once a simple database it was easier but now when I need to import it from excel maybe this confuses me

 

[GinaWhipp]

And, now I see and yet another post (http://www.access-programmers.co.uk/forums/showthread.php?t=251782) in which you are receiving help but failed to answer the questions... Some of which are the same questions I was about to ask. You may be in a panic but without providing the requested information no one can help no matter how many times you post this request.

I am also starting to think you don't completely understand Access. You are putting the total of goods transported when there should actually be a record per item for goods transported with date of transport along with any other pertinent information. Then your reports could provide the totals by location, year, etc...

Now, if that is the way you get the data, flat file format, then it is going to be more difficult to get what you want but part one is to get the tables correct which I see Mihail is trying to do in the thread above. So, please answer his questions.

 

[Armine]

Dear Gina

I thought I gave a detailed explanation in the post below actually These are not totals but exactly number just for one good , out port of the country, one partner country , one direction from 2000-2012.
For ex. in the file Romania Good named TT was imported (direction I), from the partner country (CX) to the port AAX of the country ROU. And all this codes are coming from the appropriate tables which I have put in the access attached above.
So the total by location in this case will be for example the total of the country for one good (es. TT) for all ports (AAX+A2Q) or all partner etc. And about the dates I dont have this kind of information, the file Romania is what I have and i dont need dates I need the annual data thats mentioned in the file. I understand that it isnt a nice file for importing into access but this is what I have I didnt make it myself.

I am really sorry if I still dont answer your questions, it would be very kind of you if you would say exactly which part is not clear yet.
Tnx a lot
Armine

 

[GinaWhipp]

Well, after dome time I figured out the spreadsheets and determined that if you're getting the data as shown in Romania then you really have little need to the data in Lists.

But to get get Totals? What are you waning to Total? And if you want Totals on the Years columns then you need to get rid of those -1's, better those fields should be empty. However, unless you just want Totals per year this is going to be difficult. Access Totals down not across. You'd need to create UNION queries to get any meaningful data.

 

[Armine]

Hi,
Before I had NAs in Excel and in order to replace it with something but not 0 (to know that i had a missing data there) i put -1. I dont know yet what totals am i going to need probably all kinds I just mentioned it to answer to your post. Now I am still trying to figure out how to connect sheet1 and Years in db, and actually how to make them more correct. And about the Lists which i imported in db for each criteria, why i dont need them ? for ex. if I dont have that table for the partners i will not now that AL mean Albania, or do you mean some other way to do this ? I will read about UNION queries I have no idea , tnx a lot.

 

[GinaWhipp]

1. Not sure I understand.. the Sheet1 that I have shows Years. Connect it to what?
2. Oops, I missed Partners! I looked at the Lists and pretty much saw the values there... but you are correct you can create Relationships to the Master sheet.
3. For learning, here's a good place to start...
http://www.functionx.com/access/index.htm

 

[Armine]

Oh sorry Gina for confusion I meant "sheet1" table in the database "Armine" I have attached above. Please have a look only on Armine and Romania files attached in the same thread above. I dont know how to connect "Sheet1" and "Years" tables. Tnx

 

[GinaWhipp]

That is because you can't in its present state. There is nothing that ties those two things together. You are looking for a quick solution and not seeing that Access is not Excel on steriods. You need to read the excellent advice you have been given (in all 3 of your threads) and set up your database so you can get the information you want because you can't import the spreadsheets and go from there. Your tables must be properly normalized.