473,379 Members | 1,380 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Create a Sequencing PK without using AutoNumber field

I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.

Dec 27 '06 #1
3 2015

rdemyan wrote:
I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.
I've had timing issues when I've used this approach with multiple users
running import routines.

if you're importing via vba on a record by record basis I'd just
implement a counter.
Another key I sometimes use is a time stamp with microseconds and/or a
counter stored as a string.

Cheers

Keith

Dec 27 '06 #2
On 27 Dec 2006 08:25:41 -0800, "rdemyan" <rd*****@hotmail.comwrote:

Unsure what problem you're trying to tackle.
You say you must use the data as is, then you say you could assign a
number in your import code. That reeks of your ability to add to the
data - perhaps an additional column.
What import code are you speaking of? DoCmd.TransferDatabase? We may
need to see some code.
Once the table is in Access, why not alter the table and add a PK?
Why require a PK? For pseudo-uniqueness? You already said the data has
duplicate rows. I would start there. What's the significance of having
two rows with the exact same values in all fields? It may be bogus and
could be filtered out, or perhaps it is significant. We may need to
know more about the data.
Once you have this unitue number, what would you do next?

Perhaps you could address these questions. It would narrow down the
next set of them.

-Tom.
>I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.
Dec 28 '06 #3
The data is imported from a spreadsheet using a custom procedure. With
the way things are currently done, you can legitimately have duplicate
rows.

I just want to add a field so that I can for the purposes of my
database be able to find a unique row. For example, if the duplicate
rows are shown in a listbox along with other non-duplicate rows and a
user wants to delete one of the duplicate rows from the table, how
would he do it without a PK. Without a PK wouldn't the delete
operation delete all duplicate rows. Plus I would have to specify a
compound key of all fields in order to even just get the duplicates of
that specific instance.

Besides, I thought it was good practice to have PKs. Upsizing to SQL
Server requires that each table have a PK doesn't it.
Tom van Stiphout wrote:
On 27 Dec 2006 08:25:41 -0800, "rdemyan" <rd*****@hotmail.comwrote:

Unsure what problem you're trying to tackle.
You say you must use the data as is, then you say you could assign a
number in your import code. That reeks of your ability to add to the
data - perhaps an additional column.
What import code are you speaking of? DoCmd.TransferDatabase? We may
need to see some code.
Once the table is in Access, why not alter the table and add a PK?
Why require a PK? For pseudo-uniqueness? You already said the data has
duplicate rows. I would start there. What's the significance of having
two rows with the exact same values in all fields? It may be bogus and
could be filtered out, or perhaps it is significant. We may need to
know more about the data.
Once you have this unitue number, what would you do next?

Perhaps you could address these questions. It would narrow down the
next set of them.

-Tom.
I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.
Jan 2 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet,...
3
by: Gekko . via AccessMonster.com | last post by:
I want to create a query that the first field should be an autonumber. For example I want the name and last name from the Emp table, but I want that the first field is an autonumber that identifies...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
4
by: adolph | last post by:
I created 2 tables, each with an autonumber primary key. Fields are: ID (autonumber Primary key) Number (single) Color (Text) FName (text)in one table and LName (text)in the other What I'm...
0
by: Tom Houston via .NET 247 | last post by:
I have built vb.net application that runs on my SBS2003 thatconsists of 20 plus form and menus. I am having problems with aweb form using textboxes. There are also three buttons,back/exit/save. The...
9
by: minjie | last post by:
I need to upgrade a MS Access database (Version 2002) with a script only, i.e., via SQL statement, not via Access GUI, and I'm having trouble defining an AutoNumber field. I got an exception error...
2
by: micksitup | last post by:
Without using autonumber, how can i generate the next available record number to appear in the primary key field on a data entry form? For example, i want to add a new supplier, but the primary...
2
by: krmcdonald | last post by:
I realize this has been covered a billion times, I've read them all and still can't get it. My table looks like this: DOCUMENT-SER.NO MC006343 -SST-24569 MC006344 -SST-27209 ...
1
by: sanju4kk | last post by:
I am attempting to create a module that I can call to create a new field in my temp table that will be an autonumber, and then assigned that new field as the primary key. I have been search high and...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.