Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Customizing Primary Key


(!)

VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
05-Oct-2010, 12:49 PM #1
Customizing Primary Key
I did read previous threads but its not helping me. I have access 2007 microsoft office xp. I work in a government office and keep track of businesses for tax purposes. The unique identifier for these businesses is called a control and/or parcel number with a format of xx-x-xxxxx. How can I format it as a primary key as well as a foreign key?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
05-Oct-2010, 12:56 PM #2
VANESSA1, welcome to the Forum.
It will depend on the structure of the Control and whether or not you have control over the numbering, it may be possible to do it using a Formatted Autonumber. Other than that you have to use a VBA Recordset to do so.
But ask yourself why you need to use it as the Key and foreign key.
You can just as easily use a simple Autonumber field for the Key Field and a Number type Long for the foreign keys, that is what they are for.
__________________
OBP
I do not give up easily
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
05-Oct-2010, 01:05 PM #3
Huh?
The format is a number given by the main server. The business may be out of state which has assets in my jurisdiction; so ABC Company in GA may have assets as DEF company. Both companies report different assets so they file their own return for assets they own. Also, since I am such a newbie at this I don't know what a VBA is. I'm sorry to be so naive at this
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
05-Oct-2010, 01:17 PM #4
Well providing you have the control and/or parcel number field in the table it is always possible to find one.
If you use a simple autonumber Key field you find any related data using that instead of the control and/or parcel number. It is how Access is usually structured.

VBA Code is the basis of Macros in Access, Excel, Word, Powerpoint and Outlook to automate tasks in the background.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
05-Oct-2010, 01:24 PM #5
By the way the Users do not normally see an Autonumber key field as it is not necessary for them to do so.

Also the control and/or parcel number field can be Formatted using the Format Property.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
05-Oct-2010, 01:26 PM #6
Do you type in the control and/or parcel number or is the Access Database linked to the Server?
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
06-Oct-2010, 07:16 AM #7
Good Morning from Indiana
The access database I am trying to create is not tied to the main server, although it gives the unique identifier for the businesses. I keep up with the data that is not in the main server, more detailed & specific information. Anyhoo, getting to the format, would that be in the table under design view. I tried doing it with a hyperlink but i still have to put in the hyphens. Geez I feel like an idiot this should be easier or I'm making it more complicated than it needs to be
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
06-Oct-2010, 08:36 AM #8
You should put it in the Table's Field in Design view, second item in the lower section in Access 2003 and in the Form as well.
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
08-Nov-2010, 02:58 PM #9
Hey I'm so frustrated. So are you saying that the autonumber can be stand alone and I can add a field with the formatted control number? So how will this work out when I'm trying to set up relationships? Also, is there a way I can send the file so you can look at it?

I can make tables and forms fine. But its the relationship(s) and queries that I'm struggling with
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
09-Nov-2010, 05:04 AM #10
Vanessa, it can be very tough learning Access when trying to do a real world database for your job.
What bosses & managers usually fail to understand is how Access works best. ie the Parcel number does not need to be the key field and Key fields are only there to create "Unique" identifiers that Access uses to further create the Relationships between the tables. These are then used by Queries to bring the data in the various tables back together for Input or presentation purposes on Forms/Subforms & Reports.
I will private mail you my email address so that you can send me a copy of what you have.
It does not need to use real data, just some data in the tables to work with.
You will have to reformat it as Access 2000-2003 for me to work with.
The easiest way to achieve this is to use the blank database that I will attach to this post, download & unzip it, open it and then Import the tables and queries from your database. Rezip it and then send it to me.
Attached Files
File Type: zip VAN1.zip (4.0 KB, 59 views)
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
09-Nov-2010, 10:57 AM #11
Forgive me I have a zipfile in the access 2000-2003 format. trying to figure out how to send it to you.
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
09-Nov-2010, 11:22 AM #12
Smile Attachment from Vanessa
I figured it out here ya go
Attached Files
File Type: zip VAN1.zip (26.4 KB, 76 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
09-Nov-2010, 01:20 PM #13
Vanessa, can I suggest that you remove the database now that I have a copy.

Now to the database, which comes first the Asset, the Maillocation or the Physical Location?
You could use the PID to link the tables, but I would prefer to use an Autonumber where possible, linked to a Number type long field in the other tables, which means that you would not need to duplicate the PID format and number.
VANESSA1's Avatar
Computer Specs
Member with 1,855 posts.
THREAD STARTER
 
Join Date: Oct 2010
Location: Elkhart, Indiana
Experience: Beginner
09-Nov-2010, 02:43 PM #14
Hey, well the maillocation would be first then the physical location if different from mailing location and any assets which may be reported at a physical location not necessarily at all of them. While waiting to hear from you I did play with the relationships that seems to work. Its not logical to me but Access seems to like it. I set up different autonumber fields for each table. Mailloc-Link; Physloc=ID and tblasset=id. I linked "Link to Physicalloc and ID in Physcloc to Id in asset. It set up the one to many relationship in the mail location to the physcal location table which is okay but not in the asset table and i'm not understanding that cause i can have many assets at one location. Now based on that, just working with two table Mailloc and physloc can I set up a query when I ask if mail location is different than physical location to open the physical location table/form? I would also like this same logic for the physical location to assets. Now here is the interesting part. I'm trying not to type duplicate data because physical location is most of the times a mailing location also for different companies. So an example description is Garfield Inc has assets at Xavier Ltd and Xavier Ltd has its own assets to report. So how do I cut down on typing duplicate information?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,667 posts.
 
Join Date: Mar 2005
Location: UK
10-Nov-2010, 05:14 AM #15
Vanessa, I am glad that you are making some progress, can you post what I have by re-using the blank 2003 database?
Can you have more than one Physical for one emaillocation?
One thing that you do not want to do is use Autonumber for both Key Field and Sub Key Field as you can only have a one to one relationship between 2 Autonumber fields.
. The main tabel (email location) should have an Autonumber, but a sub table should have an ordinary Number type Long field
The sub tables can have their autonumber fields for "joining" to other Sub tables or if they are used for general data, like a table of City & State names that can be used with the Physical location.

As to not duplicating data, that is the main reason for using the Key Field, once you join a sub table, none of the main table data needs to be in the Subtables except for the subkeyfield foe joining.

The way to control the data input is to use a Mainform with "Tabbed" or untabbed Subforms for entering the sub table data. The "Master/Child" link between the Main & sub forms takes care of the Key field linking.

Similarly once the data is in the tables queries bring it together using the Key/subkey fields for displaying.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Access 2007 Custom Primary Key DemonDriverDan Software Development 3 15-Dec-2009 01:28 PM
SQL Primary Key In Access GUSMAN Business Applications 3 11-Mar-2005 04:05 PM
Primary Key and AutoNumber Access Question PincivMa Business Applications 3 08-Jan-2005 10:48 AM
Access Primary Keys Spu Business Applications 1 02-Apr-2002 09:24 PM
{SOLVED} Access: Autonumbering Lost in Primary Key Field ltyson Archive: Business Applications 2 08-May-2001 10:25 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2