Sequential Autonumbering ? 1

[DugyWugy]

Hi,

I know it is possible to start auto-numbering at a defined number (ie. start numbering from 1000).

Is it possible to auto-number starting from the auto-number in another table?
For example:

I have some Owners which own many Apartment Buildings which have many Apartments in each building.
Is it possible to begin with auto-numbering the owners at 100? Then in the Apartment Building table have the auto-numbering begin at 100; to give me an ID number of 101101 for the first Apartment Building belonging to the first owner. (The first three numbers indicate the owner ID and the last three indicate the Apartment Building ID) and so on...
Does this make any sense?

Any help would be appreciated.
Thank-you

 

[kathryn]

Actually, this is what relationships are made for.

tblOwner
-------
OwnerID (Autonumber PK)
OwnerName
etc....

tblApartmentBuildings
---------------------
ApartmentBuildingsID (autonumber PK)
OwnerID (foreign key to tblOwner)
AparmentBuildingsAddress
etc...

You would create the Owner/Apartment Building code by concatenating the two IDs in tblApartmentBuildings. You could do this in a query and base all your forms and reports on it.

Hope this helps. If you need more explanation, just ask.



Kathryn

 

[DugyWugy]

Kathryn,

I was wondering if you could expand on your explanation of how to attach two primary keys. One Primary key using another from another table for its construction.

Thanks
Doug

 

[kathryn]

When you have created your two tables, go to Tools...Relationships on the menu. This is where you define your table relationships. Right click on the empty area and add your two tables.

Click and drag from the OwnerID in tblOwner to OwnerID (must be a number of long integer type) in tblApartmentBuilding.

A dialog box will pop up, to define the relationship. It should say One-to-many and you want to click all the little check boxes about cascase updates, deletes, etc. Click OK and you're done.

Now if you create a form, using the form wizard, and you choose the owner info from tblOwner and the Apartment info from tblApartmentBuilding, Access will create the form and subform for you, in other words, you will have a main form with owner info and a subform containing info on each apartment building that owner has. If you enter info on a new apartment building, it will automatically be associated with the owner on the main form.

Hope this helps.

Kathryn