Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Sparky787

69 posts

Master Geek


#116793 10-May-2013 09:35
Send private message

I hate to admit that I am using MS Access - however it is what is being taught to us right now in class for an introductory business/ICT paper on Databases.

Is it possible to enforce a unique value on the Primary Key

Have imported data from Excel - The PK is P1 through to P40

How can I enforce any new data to automatically know that the PK should be P41 for a new record?

Any suggestions?

Cheers Guys!

Create new topic
freitasm
BDFL - Memuneh
68826 posts

Uber Geek

Administrator
Trusted
Geekzone
Lifetime subscriber

  #815029 10-May-2013 09:42
Send private message

Is it a number or string? Are the values "P1" or "1"?





 

 

These links are referral codes

 

Geekzone broadband switch | Eletricity comparison and switch | Hatch investment (NZ$ 10 bonus if NZ$100 deposited within 30 days) | Sharesies | Mighty Ape | Backblaze | Coinbase | TheMarket | My technology disclosure


sittingduckz
618 posts

Ultimate Geek


  #815031 10-May-2013 09:43
Send private message

I think I understand correctly, make the PK field AutoNumber with a format \P#.
With each new record it will count up P1 P2 P3...

Or drop the P and just stick with Integers




I'm not a complete idiot, I still have some parts missing.


 
 
 
 


networkn
23448 posts

Uber Geek

Trusted
Lifetime subscriber

  #815033 10-May-2013 09:50
Send private message

I thought the best practices way was to use a guid.

sittingduckz
618 posts

Ultimate Geek


  #815036 10-May-2013 09:54
Send private message

networkn: I thought the best practices way was to use a guid.


But it sounds like he is importing data that already has existing ID values, changing to a guid would mean having to correctly change all the related tables as well?




I'm not a complete idiot, I still have some parts missing.


Sparky787

69 posts

Master Geek


  #815287 10-May-2013 14:58
Send private message

Hi Guys

I imported the data from excel. The Alpha-numeric was to make it able to identify between the three different tables.

http://www.databasedev.co.uk/reset_autonumber.html

Found the above link, it looks like it might work, but need to test it. 

Cheers Guys

sagasu
1 post

Wannabe Geek


  #815532 10-May-2013 23:10
Send private message

If you cannot make the primary key field unique it means that you must have imported a duplicate value. A primary key must be unique no matter what database management system you're using.

ShadyG
26 posts

Geek


  #815629 11-May-2013 09:43
Send private message

As Sagasu says, If you can't make the key field unique, then you've got some duplicate values in there. You need to inspect the table to find what they are.
If you absolutely must use P1... Px for the Pk, then what you have to do is this (C# pseudocode):

1. get the maximum value from the ID column. As all the values in it start with 'P' then it will return the max value of the trailing integer.

2. Split the trailing integer from the leading string, by reading the (length - 1) right hand characters and converting to an integer.

3. Add 1 to the number you get from step 2, convert it to a string, and add 'P' to the front of it.

4. Use the resulting string as your index.

OR...

set a new column in your Access table as ID (or somesuch), and set its Data type to auto number. That will take care of all your ID issues.

Create new topic





News »

Nanoleaf enhances lighting line with launch of Triangles and Mini Triangles
Posted 17-Oct-2020 20:18


Synology unveils DS16211+
Posted 17-Oct-2020 20:12


Ingram Micro introduces FootfallCam to New Zealand channel
Posted 17-Oct-2020 20:06


Dropbox adopts Virtual First working policy
Posted 17-Oct-2020 19:47


OPPO announces Reno4 Series 5G line-up in NZ
Posted 16-Oct-2020 08:52


Microsoft Highway to a Hundred expands to Asia Pacific
Posted 14-Oct-2020 09:34


Spark turns on 5G in Auckland
Posted 14-Oct-2020 09:29


AMD Launches AMD Ryzen 5000 Series Desktop Processors
Posted 9-Oct-2020 10:13


Teletrac Navman launches integrated multi-camera solution for transport and logistics industry
Posted 8-Oct-2020 10:57


Farmside hits 10,000 RBI customers
Posted 7-Oct-2020 15:32


NordVPN starts deploying colocated servers
Posted 7-Oct-2020 09:00


Google introduces Nest Wifi routers in New Zealand
Posted 7-Oct-2020 05:00


Orcon to bundle Google Nest Wifi router with new accounts
Posted 7-Oct-2020 05:00


Epay and Centrapay partner to create digital gift cards
Posted 2-Oct-2020 17:34


Inseego launches 5G MiFi M2000 mobile hotspot
Posted 2-Oct-2020 14:53









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.