Supertype / Subtype Autonumbers and Forms

[stanbridge]

Hi all,

I have setup a new "Birds" database. I am new to Access. So far have setup have setup the following Tables and Fields...

tblBIRDS (Supertype)
Bird_ID (PK)
Bird_Sex
Bird_Weight
Bird_Type (from ComboBox: Parrot, Budgie, Magpie)

tblBIRDS_PARROTS (Subtype)
P_Bird_ID (PK)
Beak_Size (from ComboBox: Large, Small)
Number_Of_Colours



tblBIRDS_BUDGIES (Subtype)
B_Bird_ID (PK)
Has_Crest (yes/no)
Number_Of_Colours

tblBIRDS_MAGPIES (Subtype)
M_Bird_ID (PK)
Continent_Of_Origin
Wing-Span

I am having trouble with applying an Autonumber Primary Key for tblBIRDS (Supertype) and having subtypes automatically use the next supertype autonumber when keying a new record through my form.

I have the PK (Bird_ID) in tblBIRDS set as an Autonumber. I have the PKs in the subtypes (eg: P_Bird_ID) set as a Number (Long Integer).

The PK in the supertype has a 1to1 relationship with the PK in each of the subtypes. Referential integrity is enforced and I have "Cascade Update Related Fields" turned on.

Ideally (for example), I want to have a Form that lets me enter a new instance of tblBIRDS_PARROTS. The PK number (P_Bird_ID) would automatically use the next available Autonumber from Bird_ID in tblBIRDS.

Can somebody show me how to do this?

Any help is very much appreciated! Thanks!


Regards,

Stanbridge

[jzwp11]

I am concerned with a couple of things. First, it is not a good idea to have list and combo boxes at the table level. They are best left for form. See this site for reasons why it is not a good idea to have list/combo boxes at the table level.

Now as to your tables. If a bird can only be of one type then you are correct in having the type field referenced in tblBirds, but that reference should go to 1 table that has the types


tblBirds
Bird_ID (PK)
Bird_Sex
Bird_Weight
fkBirdTypeID foreign key to tblBirdTypes

tblBirdTypes (3 records: Parrot, Budgie, Magpie)
-pkBirdTypeID primary key, autonumber
-txtBirdType

Now as to the other items such as number of colors, crest, beak size, etc., these are attributes of the bird and not necessarily the type. Do you have a list of these attributes that you are trying to capture for each bird? Can you provide that list?

[stanbridge]

Hi jzwp11,

Thanks for the reply!

Sorry, I guess I should have mentioned that I actually DO have the BirdType combobox being populated from a separate table. I didn't bother listing that part to avoid complicating the issue.

Also, I actually "made up" those attributes purely to show that each subtype has some unique attributes that need to be collected only for them. I should have made up some better attributes!

Anyway, for the purposes of this example, let's just assume that those attributes belong there in each of the subtypes.

My main problem is the autonumbers and subtypes issue mentioned in my original post. It's got me stumped! Any ideas how to make Subtype PKs inherit the next available Supertype PK autonumber?


Cheers,

Stanbridge

[jzwp11]

Regarding the autonumbers, I generally recommend that each table have an autonumber primary key field; then you would join these to a long number integer field in the related table (the foreign key). If you set up the relationships in the relationship window in Access prior to making forms, Access will automatically link the form and the related subform via the primary key-->foreign key relationship.

Back to your table structure, can an attribute apply to more than 1 bird type? If so, then you will most likely need a junction table to define the attributes for each bird type. Of course, the change in structure will impact any forms you currently have, so you may want to make sure your table structure is sound before spending a lot of time working on forms.

[stanbridge]

As per your recommendation, I have done this in the past (not with access, but with MySQL). That is, used autonumbers on all tables, then simply link tables by FK.

However, with this particular system, it is important that the Subtype PK actually uses the same PK from the Supertype.

So for example...

tblBIRDS.Bird_ID might have the following PKs...
1,2,3,4,5,6,7,8,9,10

tblBIRDS_PARROTS.P_Bird_ID might have the following PKs...
1,3,4,7,10

tblBIRDS_BUDGIES.B_Bird_ID might have the following PKs...
2,5

tblBIRDS_MAGPIES.M_Bird_ID might have the following PKs...
6,8,9

Using these particular examples, if I then opened my "frmBUDGIE_ENTRY" form, the PK of the next record entered would be 11.
So a new record would be created in tblBIRDS_BUDGIES containing the Budgie-Specific data (with PK 11), and a record would be created in tblBIRDS containing the generic atributes (also with PK 11).

(Note, at this stage I only have test forms, nothing set in stone! I won't spend too much time on forms until I can get this working)

Any attribute that I have stored in a subtype will ONLY be able to be applied to that subtype (that particular info will ONLY be collected for those specific bird types). For the duration of this system, this will never change.


Cheers,

Stanbridge

[jzwp11]

I have read via various websites that the primary key should have no significance to the user, by saying the following you are implying that there is significance:

..with this particular system, it is important that the Subtype PK actually uses the same PK from the Supertype.

Additionally, you will not be able to control the value using an autonumber because other records may get added and then your whole scheme will be out the window.

I think the best approach is that you assign the value yourself (via code), it technically will not be a primary key, but at least you will be able to control it and use it to join related information as necessary.

I'm not sure what your reasoning is behind this, but it seems to be almost on the verge of a bad practice, but I just don't know enough about your application to know for sure.

[stanbridge]

Hi jzwp11,

I come from a PHP/MySQL background, but had never even looked at Access prior to this. I completely agree with the following statement.

the primary key should have no significance to the user

Unfortunately, I was told to follow these rules. E-x-a-c-t-l-y!

I think I am going to have to go back and define the word "exactly".

Thanks very much for your help and persistence. This is useful info.


Cheers,

Stanbridge

[jzwp11]

I am curious to know how it works out for you; please keep us posted.