Re: Limit numbers in primary key
From: Micah Chaney (MicahChaney_at_discussions.microsoft.com)
Date: 02/04/05
- Next message: Karen Hart: "Runtime Installation problems on XP Service Pack 2 Machines"
- Previous message: minimalexperience via AccessMonster.com: "Re: Limit numbers in primary key"
- In reply to: minimalexperience via AccessMonster.com: "Re: Limit numbers in primary key"
- Next in thread: B. Comrie: "Re: Limit numbers in primary key"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 4 Feb 2005 15:05:03 -0800
OH!!! Well you didn't mention that. Access Tables only allow for one
autonumber field per table. I don't know if something can be done in SQL
(doubt it since it's the table..) But there's a work around. It's kinda
complex but check this out.
Assuming we're starting from scratch:
Create a TableA
In this table include a field [Sequence]. This should be an autonumber
field with the properties set as previously described.
Include all the fields that you're going to need in your table except your
other autonumber field.
Create a TableB
In this table include a field [Sequence] that is a number field and contains
the same properties as the corresponding field in TableA. (In respect to
Format length).
*Very important: Make sure you mark this field as Indexed: Yes (No Duplicates)
Include all the other fields from TableA with the same field names.
Add a field [PrimaryKey] or whatever your primary key field is and set it as
an autonumber field with the appropriate properties.
Create a Query.
Using TableA as the source, drop down all fields into the detail section.
>From the File Menu | Query | Append Query and choose TableB.
If you labelled each field correctly all the names should already match up.
(Notice you don't see your primary key field which is good.
When you run this Query it will take all the data in TableA and append it to
TableB. The records that are already in TableB will not be appended because
they would produce "duplicate" values in the "Count" field. You'll receive
that message everytime, but if you run the query from a Macro, you can Set
Warnings Off | Run Query | Set Warnings On. That way you or your users won't
see the message. The Query will continue to run despite this message. It
sounds complicated but it's not really.
So in summary, you'll have TableA being the table that accepts new records
into your database. This Table will assign a sequence number to each new
record. All new records will then be appended to TableB (which is what
you'll be doing your work off of) via the Append Query that you created.
This Table will assign it a PrimaryKey value while preserving the value in
the Sequence field from TableA.
Hope this helps.
"minimalexperience via AccessMonster.com" wrote:
> I am aware of the AutoNumber function, but I'm using that for my primary
> key. I need another field that is 5 digits long and unique to identify
> each customer record within the table. Upon entering the 5 digit customer
> ID, if it is a new number a new record will be added to the table. If it
> is a current record, the current record will be displayed. Again, any
> assistance is GREATLY appreciated as I'm struggling.
>
> --
> Message posted via http://www.accessmonster.com
>
- Next message: Karen Hart: "Runtime Installation problems on XP Service Pack 2 Machines"
- Previous message: minimalexperience via AccessMonster.com: "Re: Limit numbers in primary key"
- In reply to: minimalexperience via AccessMonster.com: "Re: Limit numbers in primary key"
- Next in thread: B. Comrie: "Re: Limit numbers in primary key"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
