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
76852 posts

Uber Geek

Administrator
ID Verified
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"?





Please support Geekzone by subscribing, or using one of our referral links: Dosh referral: 00001283 | Sharesies | Goodsync | Mighty Ape | Backblaze

 

freitasm on Keybase | My technology disclosure

 

 

 

 

 

 


 
 
 

You will find anything you want at MightyApe (affiliate link).
sittingduckz
677 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
Networkn
30595 posts

Uber Geek

ID Verified
Trusted
Lifetime subscriber

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

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



sittingduckz
677 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 and reviews »

Synology Introduces BeeStation
Posted 23-Feb-2024 14:14


New One UI 6.1 Update Brings Galaxy AI to More Galaxy Devices
Posted 23-Feb-2024 10:50


Amazon Echo Hub Available in New Zealand
Posted 23-Feb-2024 10:40


InternetNZ Releases Internet Insights 2023
Posted 20-Feb-2024 10:31


Seagate Adds 24TB IronWolf Pro Hard Drives for Multi-user Commercial and Enterprise RAID Storage Solutions
Posted 19-Feb-2024 16:54


Seagate Skyhawk AI 24TB Elevates Edge Security Capacity and Performance
Posted 9-Feb-2024 17:18


GoPro Releases Quik Desktop App for macOS and Introduces Premium+ Subscription Tier
Posted 9-Feb-2024 17:14


Ring Introduces New Ring Battery Video Doorbell Pro
Posted 9-Feb-2024 16:51


Galaxy AI Transforms the new Galaxy S24 Series
Posted 18-Jan-2024 07:00


D-Link launches AI-Powered Aquila Pro M30 Wi-Fi 6 Mesh Systems
Posted 17-Jan-2024 20:02


Newest LG 4K Lifestyle Projector Doubles as Art Objet
Posted 9-Jan-2024 15:50


More LG Smart TV Owners Set To Enjoy the Latest webOS Upgrade
Posted 9-Jan-2024 15:45


Panasonic Announces the Z95A and Z93A With Fire TV Built In
Posted 9-Jan-2024 15:30


Amazon Echo Pop Review
Posted 8-Jan-2024 14:22


Samsung Tab S9 FE Review
Posted 17-Dec-2023 08:26









Geekzone Live »

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



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.







GoodSync is the easiest file sync and backup for Windows and Mac