difficulties with forms and subforms

[istoica]

Hello everyone!
I searched the MSAccess help file and the internet and still no result. Till I finally found this forum from which I think i'll learn a lot.
But I am writing this thread because I have a problem...
I have like 2000 scanned land deeds that I want to put into an access database but i am having troubles in designing the relationships and the forms.
The typical deed has:

• a deed number
• one or more owners
• one or more ancesters from which the owner(s) inherited the land
• the name of the home town and the home county for the owners
• the name of the home town and the home county for land's position
• the date the deed was issued
• the type of land attribution (given/ given back/ etc)
• the zone in which the land is situated (within city limits/ outside city limits)
• the description(s) of the parcel(s) which consists in: land usage | map number| parcel number | area | north neighbour | east neigbour | south neighbour| west neighbour | Memo

So, I created the tables as it follows:

• land usage: a list of 7 land usage categories
• ancesters: only a FullName field
• home county: a list of the counties here in my area
• parcels: (lookup) land usage | map number| parcel number | area | north neighbour | east neigbour | south neighbour| west neighbour | Memo
• Owners: only a FullName field
• Home Town: a list of towns here in my area
• Position town: a list of towns here in my area
• Position counties: a list of counties here in my area
• Zoning: one column, the two types of zones i mentioned earlier
• and finaly: Deeds, with the followig structure:

ID (autonumber)

Deed Number: number, 0 decimals, the number of the deed
Owner(s)
Ancester(s)
owner's home town (lookup in table)
owner's home county (lookup in table)
Land area (number, 0 decimals)
Land position town (lookup in table)
Land position county (lookup in table)
Land attribution type (lookup in table)
Deed's issue date (date)
Parcel(s)

I want to create a form for the Deeds table, with three subforms for Owner(s), Ancester(s) and Parcels but whenever i input a new deed i want to be able to input several records in each of the three subforms but still having them related with only one deed.

Which are the relationships I need to create and how do I get to have such a form?

Much obliged.

 

[Kafrin]

For starters, the two fields:

owner's home town (lookup in table)
owner's home county (lookup in table)

...should really be in the Owners table, since they apply to the owner, not the deed.

 

[Rabbie]

You are also going to need a junction table to model the Many to many relationship between Deeds and Owners. After all an Owner can own several Deeds and aDeed can have several owners.

 

[WIS]

Hello everyone!
I searched the MSAccess help file and the internet and still no result. Till I finally found this forum from which I think i'll learn a lot.
But I am writing this thread because I have a problem...
I have like 2000 scanned land deeds that I want to put into an access database but i am having troubles in designing the relationships and the forms.
The typical deed has:

• a deed number
• one or more owners
• one or more ancesters from which the owner(s) inherited the land
• the name of the home town and the home county for the owners
• the name of the home town and the home county for land's position
• the date the deed was issued
• the type of land attribution (given/ given back/ etc)
• the zone in which the land is situated (within city limits/ outside city limits)
• the description(s) of the parcel(s) which consists in: land usage | map number| parcel number | area | north neighbour | east neigbour | south neighbour| west neighbour | Memo

So, I created the tables as it follows:

• land usage: a list of 7 land usage categories
• ancesters: only a FullName field
• home county: a list of the counties here in my area
• parcels: (lookup) land usage | map number| parcel number | area | north neighbour | east neigbour | south neighbour| west neighbour | Memo
• Owners: only a FullName field
• Home Town: a list of towns here in my area
• Position town: a list of towns here in my area
• Position counties: a list of counties here in my area
• Zoning: one column, the two types of zones i mentioned earlier
• and finaly: Deeds, with the followig structure:

ID (autonumber)

Deed Number: number, 0 decimals, the number of the deed
Owner(s)
Ancester(s)
owner's home town (lookup in table)
owner's home county (lookup in table)
Land area (number, 0 decimals)
Land position town (lookup in table)
Land position county (lookup in table)
Land attribution type (lookup in table)
Deed's issue date (date)
Parcel(s)

I want to create a form for the Deeds table, with three subforms for Owner(s), Ancester(s) and Parcels but whenever i input a new deed i want to be able to input several records in each of the three subforms but still having them related with only one deed.

Which are the relationships I need to create and how do I get to have such a form?

Much obliged.

I assume that each deed has a unique number - ie no two deeds can have the same number. You don't need an ID number, the deed number will be the ID. Set the deed number as Long, No Duplicates and make it a PK. (I would call it DeedID with the Caption Deed Number).

The Owner(s), Ancester(s) and Parcels tbls will also have the deed number as well. Set the deed number (DeedID) as Long, Duplicates Allowed in these 3 tbls.

The deed number (DeedID) links all the data together.

 

[istoica]

Thank You all for your replies
To be honest with you... I did not understand exactly what I have to do to reach the result I want. Therefore I designed using InfoPath the form I want to use to input the data. I uploaded the form (InfoPath template). The basic thing would be... How do I get to have this type of database structure and form in MS Access without having the tables filled with data (except the ones that are dropdown lists in the InfoPath form) and to have a form in Access that coul fill me all the tables at once, in several tables even creating multiple entries?

 

[istoica]

I assume that each deed has a unique number - ie no two deeds can have the same number.

Well... Your assumption is wrong . There are Deeds that were duplicated (various reasons: they were lost, destroyed) and they kept the same number. So I am asking now... in the deeds table am I allowed to have the PK with double entries?

 

[WIS]

Well... Your assumption is wrong . There are Deeds that were duplicated (various reasons: they were lost, destroyed) and they kept the same number. So I am asking now... in the deeds table am I allowed to have the PK with double entries?

No - a Primary key can only have one value per table ie no duplications. You now seem to have a problem. If the deeds are duplicated do the duplicates have diff data entered against each deed - if so is there any other fld in the deeds tbl that is unique.

 

[KathyL]

Well... Your assumption is wrong . There are Deeds that were duplicated (various reasons: they were lost, destroyed) and they kept the same number. So I am asking now... in the deeds table am I allowed to have the PK with double entries?

Tables are not mandated to have Primary keys. You can establish indexes on one or more fields, and an index can be "no duplicates allowed', or "duplicates allowed".

You can relate one table to another by fields which are indexed.