table query......

[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.
Table 'G' is linked to Table C & F by a join.
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

 

[Baz]

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.
Table 'G' is linked to Table C & F by a join.
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

Hi again Chris.

On the face of it, there is nothing wrong with having all 100 question
fields on the transaction record. It doesn't break any rules of database
design, and the overhead of having a bunch of empty fields on the records is
really not worth worrying about. You would NOT be creating redundant data,
because the unused fields would be, well, unused!

I would definitely prefer to have all the question fields on the transaction
table rather than split across other tables as you suggest.

HOWEVER, when I hear someone say something like "The 100 questions are not
flexible and no changes to this are possible", my invariable response is to
chuckle quietly to myself. The issue is not WHETHER things will change,
it's merely a question of WHEN! A much more flexible design would be
something along these lines:

transactions table:

txion_ref
method_type

questions table:

question_id
method_type
question

txion_questions table:

txion_ref
question_id
question_response

Although, of course, there is a potential problem here with the data type of
question_response, but you haven't given enough information about the kinds
of questions and answers.

But, if you want to stick to the inflexible approach (i.e. whoever told you
that "no changes are possible" has signed a declaration in blood and had it
witnessed by a high court judge!), then if I were you I would definitely put
all the questions on the one record and not worry about all the empty
fields.

 

[Baz]

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.
Table 'G' is linked to Table C & F by a join.
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

Oh, I forgot about the crashing: I'm afraid this suggests that the database
is ALREADY corrupt! The first thing to do is to make a backup copy of the
database, before it gets worse. Then follow the usual steps for fixing a
corrupt database. A good guide can be found here:

http://allenbrowne.com/ser-47.html

 

[AFKAFB]

Baz

That's great.

I'm tempted to take your first idea and leave it all in the one
table.

I should have been clearer and I can see where it might cause problems
but the 100 fields only record the answers to the questions.

The question only appears as a label for either a text box or combo
box. I can see your thinking is a lot clearer and elegant than mine.

If I were to stay with the one table as a solution I've another
question.

My first form asks 20 initial questions and assigns a method type stamp
at some stage during the 20 questions. Say at Q3 a method stamp is
assigned.

My options then are to carry onto the next record and answer the
initial questions for the remaining transactions or a new form is
loaded based on the answer to Q3 - this should then activate a new form
for questions 21-40. As all the answer fields are bound to the one
table what problems will this cause (if any). Will this second form be
a sub-form of the first form? Ideally if the user is at transaction
reference '10024', when they answer Q3 it should show record
'10024' in the second form.

I originally had all the records in one table as you suggested but when
I was activating the second form I seemed to be having problems saving
the info recorded in the second form back to the one bound table.

Do I need to make a parent and child relationship between form 1 and
form 2-5 (each of the forms 2-5 will be activated at some stage when
answering the initial questions).

Chris

 

[Baz]

Baz

That's great.

I'm tempted to take your first idea and leave it all in the one
table.

I should have been clearer and I can see where it might cause problems
but the 100 fields only record the answers to the questions.

The question only appears as a label for either a text box or combo
box. I can see your thinking is a lot clearer and elegant than mine.

If I were to stay with the one table as a solution I've another
question.

My first form asks 20 initial questions and assigns a method type stamp
at some stage during the 20 questions. Say at Q3 a method stamp is
assigned.

My options then are to carry onto the next record and answer the
initial questions for the remaining transactions or a new form is
loaded based on the answer to Q3 - this should then activate a new form
for questions 21-40. As all the answer fields are bound to the one
table what problems will this cause (if any). Will this second form be
a sub-form of the first form? Ideally if the user is at transaction
reference '10024', when they answer Q3 it should show record
'10024' in the second form.

I originally had all the records in one table as you suggested but when
I was activating the second form I seemed to be having problems saving
the info recorded in the second form back to the one bound table.

Do I need to make a parent and child relationship between form 1 and
form 2-5 (each of the forms 2-5 will be activated at some stage when
answering the initial questions).

Chris

Hi Chris.

I generally find with Access that bound forms work best when they stick
closely to your table design. So, in your case, you have one table, so you
have one form: no subforms or popups.

What I would do is to put a tab control onto the form, and arrange your
alternative sets of questions on different pages of the tab control. If you
set the "Style" property of the tab control to "None", it will not look like
a tab control, and there will be no tabs for users to click on (it's best to
leave the Style as "Tabs" while you are building the thing, and only change
it to "None" when you are happy with all the pages).

Also, you should set the "Visible" property to "No" on every page, so that
none of the alternative sets of questions is visible in the first instance.
Then, when you reach the point in the initial questions where you know how
to proceed, you simply use a bit of code to make the appropriate page of the
tab control visible.

'Change the visible property of a tab page:
tabMain.Pages(0).Visible = True

 

[AFKAFB]

Baz
Blinding idea
Quick question though - does each tap pahe have to be the same sixe
(width and height - my forms are all different and i'm having trouble
sizing them.
how do you size a page also.
regards
chris

 

[Baz]

Baz
Blinding idea
Quick question though - does each tap pahe have to be the same sixe
(width and height - my forms are all different and i'm having trouble
sizing them.
how do you size a page also.
regards
chris

Hi Chris,

The size of the pages on a tab control is determined by the size of the tab
control - you can't have different sized pages in the same tab control. You
can of course have more than one tab control, each with a different size.

 

[AFKAFB]

Bax

In fact i'll end up with three tables bound to tab controls.

If I use tab controls is it still possible to restrict user access
rights.

So if a tab control has say six pages can i restrict say user1 to only
view pages 1-6, user2 to view all pages but edit pages 2/3 and so on.

if so where cann i restrict access

regards

chris

 

[AFKAFB]

Baz
I assume if you created a form based on two or more tables the output
cannot be viewed on a tab control.

A messy workaround would be to create a new table from two or more
tables and base the form control off that new table.
Obviouly i'll have to run the query to make the table each time which
is not great but there are only five hundred records so its not a huge
problem.

I omitted to say that in fact i could have as many as 300 fields not
the original 100 and as a table can only have 255 fields i gues ill
have to use more than one table and if i do use tab controls to make
tables each time.

regards

chris

 

[Baz]

Bax

In fact i'll end up with three tables bound to tab controls.

If I use tab controls is it still possible to restrict user access
rights.

So if a tab control has say six pages can i restrict say user1 to only
view pages 1-6, user2 to view all pages but edit pages 2/3 and so on.

if so where cann i restrict access

regards

chris

You can't bind tables to tab controls, a tab control is merely a way of
organising and presenting other controls, and for your purposes it provides
a convenient way of arranging and presenting your alternative "sets" of
fields. In any case, I thought you had decided on having all your
alternative fields on just one table?

You can't restrict user access by means of tab controls. As I said, all a
tab control does is to give you somewhere to put other controls. If you
have a form bound to a table, then if a user can open the form then he/she
can access all the controls on that form, regardless of how they are
organised. However, in your code that displays the appropriate page of your
tab control, you could first check who the user is. The CurrentUser()
function will tell you who the current user actually is.

 

[AFKAFB]

Baz
Just to clarify the first point - the tab control 'sits' on a form and
the form is bound to a table. is that correct.

i'm using a series of bound forms to tables but have realised that i
could use one table bound to different forms.

chris