Index problem

[Reez]

Hello,
I have this table of 4 columns ( service id, service name, subservice id, subservice name)
ex.
service id= BRA
service name= Broadcasting
Subservice id= BRA
Subservice name=FM Broadcasting

now subservices cannot have the same name, but different subservice can have the same service. So I'm trying to set the subservice id to be the primary key, but its not working, access is telling me there are duplicates and whatever. On the indexed section of the design view, the setting is indexed:yes,no duplicates. BUT I DONT have duplicate subservice names.
help please

 

[MrQ]

If your index is againstthe subservice id, that's where your duplicate will be, not in the subservice name.

Q

 

[Reez]

I'm sorry I meant they can't have either same subservice ID or subservice name

 

[MrQ]

Hi Reez,
Bit unclear on this. Can you post a sample of your database and I'll have a look?

Q

 

[Reez]

The problem is in the Subservices Table, the database couldnt be uploaded,so i attached a screenshot.
another thing is I don't want the columns to ordered in a way that the serviceid and subserviceid are the first 2 columns, i keep on moving them but access just gets them back in the other order.

 

[MrQ]

Hi Reez,
Can't see your screenshot.

 

[Reez]

sorry, forgot to attach it first time, it's attached now

 

[MrQ]

Hi Reez,
Again, remember that the table you have here is not a spreadsheet. The order of your columns bears no real relevance to the storage of your data.
Also, it looks like you'd be better off with two table. One for your Service fields and a second for your subservice fields with a third field in your subservice table, linking the service id to you subervice id and name.
I've posted another sample which I hope will help.

Q

 

[Reez]

Hey MrQ
I did have 2 tables at first, but then I thought it's better to just merge them all, i mean for the users. Anyways I think I'll just remove all indexes and not set a primary key. because it's not even accepting a primary key.
Thanks alot,

 

[MrQ]

Reez,
It may be worth you searching the forum for an explanation of normalisation. A database is very pliable and user friendly as long as it is not treatied like a spreadsheet. Once you go down the road of placing all data into a single table, the point of the database is lost.
Q

 

[Rabbie]

Reez,
It may be worth you searching the forum for an explanation of normalisation. A database is very pliable and user friendly as long as it is not treatied like a spreadsheet. Once you go down the road of placing all data into a single table, the point of the database is lost.
Q

See this link for some help on Relational Database design. It will help you follow MrQ's excellent advice.

If you want a spreadsheet use Excel. If you want a database use Access.

 

[Reez]

Thank you guyz, both, you've been really helpful.

 

[MrQ]

Thanks Rabbie.
Exile in Wiltshire eh. I used to be a Bristolian some time ago