A
AFKAFB
Hi
I'm building a database, maximum number of records will only ever be
approx 500. Each record has initially about 15 fields associated with
it, the transaction reference is unique and is the primary key - this
data appears in one table (Table A).
Each record is initially subject to 20 questions (Table B) and
depending on result of these 20 initial questions the record will be
stamped with a method type. There are four method types and a
transaction can only have one record type. There are approximately
another 80 questions split between the four methods, creating four
tables (Table C,D,E,F). Once the data is assigned a method stamp each
record is appended to one of the tables.
Apart from the original data for the 15 fields all data is captured via
forms.
The 100 questions are not flexible and no changes to this are possible.
On the one hand all 300 records could appear in one single table but
this table would then have the initial 15 fields and the 100 question
results fields - this would create redundant data so therefore it
makes to sense to have separate tables. The Unique ID is the primary
key on every table.
The only unique ID is the transaction reference number, many of the
questions are YES/NO. Nothing else in the DB is unique. So my
relationship structure looks very simple where I join 'Table A' to
'Table B' and 'Table B' to Tables 'C'/'D'/'E'/'F' all on unique ID.
Each join type is a one to one and Referential Integrity has been
switched on and Cascade Update and Cascade Related Fields.
I now have a Table 'G' which is fed by positions appearing in Tables
'C' & 'F' only via an append query.
The join type is again the same as above.
My questions are as follows:
1. I think my design could be poor but I'm not sure why?
2. When I create a form using the values in Tables 'A'/'B'/'C' or
'A'/'B'/'D' there are no problems but if I try to create a form using
Tables 'A'/'B'/'C'/'G' Access crashes, advises me there is an error and
reboots. Why is this??? Why would Access just not return an error
message saying its bad.
3. I'm concerned that when Accees crashes dlike this it could be
damaging the DB and possibly corrupting it.
Any help would be well appreciated.
If you need more information please shout.
Ultimately the results of the questions assigns a value which
facilitate the calculations.
Regards
Chris
I'm building a database, maximum number of records will only ever be
approx 500. Each record has initially about 15 fields associated with
it, the transaction reference is unique and is the primary key - this
data appears in one table (Table A).
Each record is initially subject to 20 questions (Table B) and
depending on result of these 20 initial questions the record will be
stamped with a method type. There are four method types and a
transaction can only have one record type. There are approximately
another 80 questions split between the four methods, creating four
tables (Table C,D,E,F). Once the data is assigned a method stamp each
record is appended to one of the tables.
Apart from the original data for the 15 fields all data is captured via
forms.
The 100 questions are not flexible and no changes to this are possible.
On the one hand all 300 records could appear in one single table but
this table would then have the initial 15 fields and the 100 question
results fields - this would create redundant data so therefore it
makes to sense to have separate tables. The Unique ID is the primary
key on every table.
The only unique ID is the transaction reference number, many of the
questions are YES/NO. Nothing else in the DB is unique. So my
relationship structure looks very simple where I join 'Table A' to
'Table B' and 'Table B' to Tables 'C'/'D'/'E'/'F' all on unique ID.
Each join type is a one to one and Referential Integrity has been
switched on and Cascade Update and Cascade Related Fields.
I now have a Table 'G' which is fed by positions appearing in Tables
'C' & 'F' only via an append query.
The join type is again the same as above.
My questions are as follows:
1. I think my design could be poor but I'm not sure why?
2. When I create a form using the values in Tables 'A'/'B'/'C' or
'A'/'B'/'D' there are no problems but if I try to create a form using
Tables 'A'/'B'/'C'/'G' Access crashes, advises me there is an error and
reboots. Why is this??? Why would Access just not return an error
message saying its bad.
3. I'm concerned that when Accees crashes dlike this it could be
damaging the DB and possibly corrupting it.
Any help would be well appreciated.
If you need more information please shout.
Ultimately the results of the questions assigns a value which
facilitate the calculations.
Regards
Chris