Database Design

[Ashbee]

I have a data entry screen comprising a Main Form linked
to "Lexicon_Table" a table containing names, dates of
birth and locality for all members of my organisation and
a subform requiring data entry of gender, dates of
attendance and information relating to the attendance. The
resultant combined data is stored in "Customer_Table".

A recent download of the "Lexicon_Table" from the parent
organisation contained a new field "gender" that rendered
the data entry field in the subform redundant. The text
field for 'gender' in the subform was deleted and a text
field created for 'gender' in the main form.

My problem is that the gender information is not being
automatically recorded in the "Customer_Table" - the other
main form data is accurately recorded. I have checked the
properties for 'gender' and these are identical for the
two data tables. Can anyone assist please.

 

[Jeff Boyce]

I may have missed something, but it sounds like the data download is making
into the table, not the form. What's the form based on? Sometimes, I
create a form based on a query, based on a table. If the table changes, I
have to change the query to "pick up" the change.

 

[Ashbee]

The form is based on the data table (Lexicon_Table). I
don't have a query based form. All data is appearing
accurately in the form. Only the 'gender' data is not
being collected with the other data from the form and
recorded in 'Customer_Table'.

 

[RSGinCA]

My problem is that the gender information is not being

automatically recorded in the "Customer_Table" - the other
main form data is accurately recorded. I have checked the
properties for 'gender' and these are identical for the
two data tables. Can anyone assist please.

I suspect that you need to determine how the other info from that Main Form is
being "automatically" recorded. My guess is that there is some programming
that does this and that you will need to modify that code.

Rick

 

[Jeff Boyce]

You've confirmed that Lexicon_Table has the field in it? Or the
Customer_Table? I'm confused about where you've added the "Gender" field,
and where you are trying to collect it.

 

[Ashbee]

The gender field is contained in Lexicon_Table and I am
trying to collect it in Customer_Table.

 

[Jeff Boyce]

Ashbee

Are you saying that you ONLY added to Lexicon_Table? If so, there's no
place in Customer_Table for it to be stored, right? What happens if you add
the field to Customer_Table, then update your form?

All this is without any idea of what your data/table structure looks like,
so it may not apply.

 

[Ashbee]

No - there is a place in the Customer_Table for data to be
stored - I just meant that the primary source to access
the information for the field is in Lexicon_Table.

 

[Jeff Boyce]

Ashbee

I'm having trouble visualizing your data. Please provide a description of
your table/data structure and relationships.

Also, if you have already recorded a fact in Lexicon_Table, why do you need
to redundantly store it in another table?

And I don't understand why you'd have name, dob, etc. information in one
table and a Gender in another?

More info, please..

Jeff Boyce
<Access MVP>

 

[Ashbee]

I'm not sure if the following from the
Tools/analyze/documenter will assist ...

G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB Thursday,
25 March 2004
Table: Customer_Table Page: 1
Properties
Date Created: 25/03/04 11:06:24 Def.
Updatable: True
Last Updated: 25/03/04 11:13:23 OrderByOn:
True
RecordCount: 0
Columns
Name Type Size
mb_service_number Number (Long) 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Decimal Places: Auto
DisplayControl: Text Box
Ordinal Position: 0
Required: False
Source Field: mb_service_number
Source Table: Customer_Table
mb_surname Text 20
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 1
Required: False
Source Field: mb_surname
Source Table: Customer_Table
mb_given_names Text 30
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 2
Required: False
Source Field: mb_given_names
Source Table: Customer_Table
mb_rank_title Text 20
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 2
DisplayControl: Text Box
Ordinal Position: 3
Required: False
Source Field: mb_rank_title
Source Table: Customer_Table
mb_birth_date Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Ordinal Position: 4
Required: False
Source Field: mb_birth_date
Source Table: Customer_Table
mb_curr_ship_name Text 37
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2325
DisplayControl: Text Box
Ordinal Position: 5
Required: False
Source Field: mb_curr_ship_name
Source Table: Customer_Table
gender Text 1
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Format: >
Ordinal Position: 6
Required: False
Source Field: gender
Source Table: Customer_Table
referred_by Text 25
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Format: >
Ordinal Position: 7
Required: False
Source Field: referred_by
Source Table: Customer_Table
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 3
first_attendance Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Format: Medium Date
Ordinal Position: 8
Required: False
Source Field: first_attendance
Source Table: Customer_Table
last_attendance Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Format: Medium Date
Ordinal Position: 9
Required: False
Source Field: last_attendance
Source Table: Customer_Table
total_number_of_attendances Number (Long) 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Decimal Places: 0
Default Value: 0
DisplayControl: Text Box
Ordinal Position: 10
Required: False
Source Field: total_number_of_attendances
Source Table: Customer_Table
purpose Text 100
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 8070
DisplayControl: Text Box
Format: >
Ordinal Position: 14
Required: False
Source Field: purpose
Source Table: Customer_Table
service_provided_1 Text 50
AllowZeroLength: False
Attributes: Variable Length
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 4
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2955
DisplayControl: Text Box
Ordinal Position: 15
Required: False
Source Field: service_provided_1
Source Table: Customer_Table
service_provided_2 Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 16
Required: False
Source Field: service_provided_2
Source Table: Customer_Table
service_provided_3 Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 17
Required: False
Source Field: service_provided_3
Source Table: Customer_Table
service_provided_4 Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 18
Required: False
Source Field: service_provided_4
Source Table: Customer_Table
initial_status Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 27
Required: False
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 5
Source Field: initial_status
Source Table: Customer_Table
outcome_status Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 28
Required: False
Source Field: outcome_status
Source Table: Customer_Table
category Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 30
Required: False
Source Field: category
Source Table: Customer_Table
comments Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Format: >
Ordinal Position: 31
Required: False
Source Field: comments
Source Table: Customer_Table
service_provider Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 32
Required: False
Source Field: service_provider
Source Table: Customer_Table
report_printed Yes/No 1
AllowZeroLength: False
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 6
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1417
DisplayControl: 106
Format: Yes/No
Ordinal Position: 33
Required: False
Source Field: report_printed
Source Table: Customer_Table
Report_Date Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 15
Default Value: Date()
Format: Medium Date
Ordinal Position: 34
Required: False
Source Field: Report_Date
Source Table: Customer_Table
Table Indexes
Name Number of Fields
mb_service_number 1
Clustered: False
Distinct Count: 0
Foreign: False
Ignore Nulls: False
Name: mb_service_number
Primary: False
Required: False
Unique: False
Fields: mb_service_number, Ascending
Patient Treatments Tablesex 1
Clustered: False
Distinct Count: 0
Foreign: False
Ignore Nulls: False
Name: Patient Treatments Tablesex
Primary: False
Required: False
Unique: False
Fields: gender, Ascending
referred_by 1
Clustered: False
Distinct Count: 0
Foreign: False
Ignore Nulls: False
Name: referred_by
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Customer_Table Page: 7
Primary: False
Required: False
Unique: False
Fields: referred_by, Ascending
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Lexicon_Table_Copy Page: 8
Properties
Date Created: 25/03/04 11:05:53 Def.
Updatable: True
Last Updated: 25/03/04 11:13:50 OrderByOn:
True
RecordCount: 0 RowHeight: 285
Columns
Name Type Size
mb_prefix_code Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Description: redundant value from old lexicon
table required for lookup fx
DisplayControl: Text Box
Ordinal Position: 0
Required: False
Source Field: mb_prefix_code
Source Table: Lexicon_Table_Copy
mb_service_number Number (Long) 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Decimal Places: Auto
DisplayControl: Text Box
Ordinal Position: 1
Required: False
Source Field: mb_service_number
Source Table: Lexicon_Table_Copy
mb_rank_title Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 2
Required: False
Source Field: mb_rank_title
Source Table: Lexicon_Table_Copy
mb_surname Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Lexicon_Table_Copy Page: 9
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 3
Required: False
Source Field: mb_surname
Source Table: Lexicon_Table_Copy
mb_given_names Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 4
Required: False
Source Field: mb_given_names
Source Table: Lexicon_Table_Copy
mb_birth_date Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Ordinal Position: 5
Required: False
Source Field: mb_birth_date
Source Table: Lexicon_Table_Copy
mb_curr_ship_name Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1950
DisplayControl: Text Box
Ordinal Position: 6
Required: False
Source Field: mb_curr_ship_name
Source Table: Lexicon_Table_Copy
gender Text 1
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1170
DisplayControl: Text Box
Format: >
Ordinal Position: 7
Required: False
Source Field: gender
Source Table: Lexicon_Table_Copy
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Lexicon_Table_Copy Page: 10
function Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2190
DisplayControl: Text Box
Ordinal Position: 8
Required: False
Source Field: function
Source Table: Lexicon_Table_Copy
mb_work_phone Text 255
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DisplayControl: Text Box
Ordinal Position: 9
Required: False
Source Field: mb_work_phone
Source Table: Lexicon_Table_Copy
review_date Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Default Value: Now()
Format: Short Date
Ordinal Position: 10
Required: False
Source Field: review_date
Source Table: Lexicon_Table_Copy
Table Indexes
Name Number of Fields
lexicon_tablesex 1
Clustered: False
Distinct Count: 0
Foreign: False
Ignore Nulls: False
Name: lexicon_tablesex
Primary: False
Required: False
Unique: False
Fields: gender, Ascending
PrimaryKey 1
Clustered: False
Distinct Count: 0
G:\~\ADMINM~1\ADMINF~1\FBWHCD~1\~\DB\~_Data.MDB
Thursday, 25 March 2004
Table: Lexicon_Table_Copy Page: 11
Foreign: False
Ignore Nulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields: mb_service_number, Ascending

I am using Access97.

A report is printed out using the data in 'Customer_Table'.

Hoping the above info doesn't create more difficulty for
you but helps you understand how the database is being
held together.

 

[Jeff Boyce]

Thank you. This is what I asked for, but may be more than I can use...

Here's an example of what I am trying to understand:

3 tables:

tblPerson
PersonID
FName
LName
DOB

tblAddress
AddressID
DeliveryAddr
City
State/Province
PostalCode

trelPersonAddress
PersonAddressID
PersonID
AddressID
BeginDate
EndDate

Could you please provide a similar "level of detail" description of the data
you have in your tables?