[VANESSA1]

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]

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.

[VANESSA1]

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]

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]

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]

Do you type in the control and/or parcel number or is the Access Database linked to the Server?

[VANESSA1]

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]

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]

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]

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.

[VANESSA1]

Forgive me I have a zipfile in the access 2000-2003 format. trying to figure out how to send it to you.

[VANESSA1]

I figured it out here ya go

[OBP]

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]

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]

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.