Setting Primary Keys with Duplicate References

[Julie van Heerden]

Hi

Please help , I am very new to Access 2010 and I am helping my daughter with her assigment , There is a Table for Warehousestorage Units which has several fields. On the plan they split this table in to two One called Warehouse the other called StorageUnits , in the Warhouse table they want the Warehouse ID set as the Primary Key and in the StorageUnits they want the StorageID and Warehouse ID to be set as a Primary key.

Problem is in the Warehouse table the WarehouseID have several Duplicate numbers and the StorageUnit Table has several duplicate numbers in both WarehouseID and StorageID

WarehouseID StorageID
W1 RT001
W1 RT002
W1 RT003
W2 RT001
W2 RT002
W2 RT003

No matter how I do it I cannot set either one as Primary key , is there anyway a Pimary key could read both coloums so that the Primary key would read W1 RT001 >

 

[Galaxiom]

Select both fields in the table design and make them the key. This is called a composite key.

 

[Julie van Heerden]

Select both fields in the table design and make them the key. This is called a composite key.

Thank you but the tables have duplicate data so it will not allow me to

Warehouse Table and Storage Unit Tables both have duplicates

WarehouseID must be set as Primary Key
W1
W1
W1
W2
W2
W2

Storage Table

WarehouseID StorageID (must both be set as primary keys)
W1 RT001
W1 RT002
W2 RT001
W2 RT002

Hope this makes better sense

Also if i try to change the index to allow duplicates the minute I select it to be the Primary key it changes back to No Duplicates , so when I set the Primary key it tells me Error in the Index Duplicate data exists ?

 

[Galaxiom]

Don't do it from the index window.

Just highlight both fields in the design view. Hold down the Shift and keep it down while you right click and select the Primary Key menu entry. (Or highlight the two fields and click the Primary Key button in the Design ribbon.)

Then you can have a look then and see how a Composite Primary Key is set up in the Index window. There will be three entries to define the Primary Key.

 

[Julie van Heerden]

Don't do it from the index window.

Just highlight both fields in the design view. Hold down the Shift and keep it down while you right click and select the Primary Key menu entry. (Or highlight the two fields and click the Primary Key button in the Design ribbon.)

Then you can have a look then and see how a Composite Primary Key is set up in the Index window. There will be three entries to define the Primary Key.

Hi there

I am work at present and only have the Access at home so cannot try until this evening , but I know that I did go into design window and highlighted both fields and set as primary keys when I want to save the table it gives me an error , will have another go at it this evening ,

as for the WarehouseID how do I set this has a PrimaryKey when it does not have a Unique number , This has really given me a hard time and I cant go any further with the assignment as these items need to be set as Primary Keys and I have tried it all , will give it another bash

Many thanks for all your help

 

[Galaxiom]

as for the WarehouseID how do I set this has a PrimaryKey when it does not have a Unique number

This field cannot be the Primary Key. If it needs one you could add another field.

 

[RainLover]

Do you have the assignment Question in writing.

There appears to be some misunderstanding.

Galaxiom has given correct advice, so perhaps the question is misinterpreted.

Could you post the original question as written.

 

[Julie van Heerden]

Hi

I am attaching the excel file and a PDF file as to the project please look at the Warehouse table in Excel and the tables in the PDF file how they want the table split the highlighted fields are the Primary Key

Thanks

 

[Julie van Heerden]

Do you have the assignment Question in writing.

There appears to be some misunderstanding.

Galaxiom has given correct advice, so perhaps the question is misinterpreted.

Could you post the original question as written.

The Assignment given has very poor instructions I have attached a spreadsheet and a PDF file on how the tables and the Links must be . In the excel spreadsheet Warehouse is One table but if you look at the database they want the Warehouse table split , problem is they want the WarhouseID to be a Primary Key in the Warehouse table but there is duplicates , I wanted to add my own Key which is what makes sense to me, but the Assignment clearly states this as a Primary key.

The other table Storage ID they want both the StorageID and WarehouseID as Primary keys both of these have duplicates

Hoping you guys can help

thanks

 

[RainLover]

You have entered the Data into the Warehouse Table incorrectly.

There are only Four Records.

W1
W2
W3
W4

 

[Julie van Heerden]

You have entered the Data into the Warehouse Table incorrectly.

There are only Four Records.

W1
W2
W3
W4

The data was given and exported from excel , I did split the two tables and delete the duplicate records in Warehouse and managed to set the Primary key in that table , But they ask for two primary keys in the Storage Unit table. The two fields Warehouse ID and Storage Unit ID together create a Unique number but on their own have duplicates , and I get an error when trying to set both primary keys , will work on it again this evening and send you a copy of the error . Maybe the question should be how do I make two Fields one Unique No?

Warehouse ID Storage Unit
W1 RT001
W1 RT002
W1 RT003

the two fields together make a Unique number
W1RT001
W1RT002
W1RT003

I dont know if I am explaining myself correctly

Thanks

 

[RainLover]

I thought Galaxiom explained correctly.

Delete current Primary Key. Save Table.

Select the two Fields that you want to be your Primary Key. Then create a new Key as per Galaxiom's instructions. I don't use 2007 or 2010 so it is a bit different to what I am used to.

If this doesn't work then your Table must have duplicate Combinations of the two Fields.

 

[RainLover]

Julie

There appears to be an error in the Data you were given.

Look at W3 RT013. There are two entries. One is Occupied and the other is available.

This is why you cannot create the Key. Perhaps it was put there as a test.

 

[Julie van Heerden]

Julie

There appears to be an error in the Data you were given.

Look at W3 RT013. There are two entries. One is Occupied and the other is available.

This is why you cannot create the Key. Perhaps it was put there as a test.

Thankyou I will certainly look at that , to be very honest the whole assignment gives very poor instructions , its really ridiculous as time constraints are an issue , also both my daughter and I have not worked with Access and the training provided is very basic. I am sure I will come right in the end.

Sorry if I came accross as being stupid but have no experiance with Access 2010 and its like the blind leading the blind with very little training material

I will let you know if I come right Thanks to all of you for your Help

 

[RainLover]

No need to appologise for asking for help.

Good luck with the assignment.

 

[Julie van Heerden]

No need to appologise for asking for help.

Good luck with the assignment.

Thanks I will let you know the outcome , I must say I love anything like this and can sit for hours my daughter on the other hand hates anything like this. When I first started I was overwhelmed but if I can get those primary keys set on that last table I will be a lot futher then will need to do all the the query reports , so may need your help again there as once again the instructions given are not very good and I am beggining to think they are just wanting to see if you use your head and think .

Thanks gain have a great day

 

[RainLover]

You are most welcome.