Hi Tony,
There's probably a snazzy visual basic way of doing it, but this would be my
solution:
1. In your table properties, change your unique ID field in each table from
Autonumber to Number, long integer, no default value. This helps get round the
Autonumbers going out of synch if a table has a record missing.
2. On your forms, go to the properties of your unique ID field (presumably a
text box). Set the defauly value as:
=DMax("[UniqueID]","Questionnaire1")+1
where "Questionnaire1" is the name of your table that stores the responses,
and [UniqueID] is whatever your unique ID field in the table is called. The
function searches in that field of the table for the maximum value previously
entered, and adds 1, then puts this value in for your new record. Set the
Locked property to YES so the user can't change it, or even hide it by setting
Visible to NO (if you need the user to enter some other ID info, use a
different field).
To get all 3 tables to contain the same value is quite lengthy to explain by
email. Are you familiar with linking multiple tables on forms? Using Master
and Child subforms? If you are using Access 2000 or above you could have a
master form that contains the ID info, and have a tab for each of your 3 sub
forms with the questionnaire responses on them, linked by the ID field. If
you've not done it before, the Microsoft Help files are uncharacteristically
useful!
Also, where you say you have 120 items rated on three scales - are the 3
scales the possible responses (sorry, I'm not familiar with the
questionnaire). If it is, you could use a control on the form, such as the
check boxes or option buttons to only allow the user to click one response per
item, and the corresponding value (e.g. 1, 2, or 3) is entered in the field
for that item in the underlying table - so you only have one field per item,
which may mean you can fit all 120 items in one table.
I hope some of this helps. Feel free to email me direct if you think it may
not be relevant to the list any more.
Cheers,
Brian Saxby
Institute for Ageing and Health
Newcastle University
>===== Original Message From "Moss, Antony C" <[log in to unmask]> =====
>Hi,
>
>
>
>I'm currently working on creating an electronic version of the Alcohol
>Expectancy Questionnaire in Microsoft Access (2003). I'd appreciate
>any advice on the following problem- I'll begin by trying to (briefly)
>describe what the database looks like at the moment, and then
>hopefully the problem will become clearer!
>
>
>
>The questionnaire (in our current version) contains 120 items, each of
>which are rated on three separate scales. As an Access table will
>only allow a maximum of 255 columns, this meant I had to spread the
>360 response columns across three separate tables (I chose three
>rather than two as this made it all a bit easier to manage). I then
>went on to produce three forms which allow participants to enter their
>responses, which are then collected across the three tables. The
>problem I am having with the database is identifying each of the 3
>records as belonging to one participant- in the first form (referring
>to table 1), participants enter a unique 'identifier'- I have tried
>creating relationships between this field and the other associated
>fields in the next two tables, but this doesn't then propagate the
>value into the fields. Any suggestions? I've tried append queries,
>cut and paste macros... It is a tricky one! In case anyone suggests
>this, the Auto Number function isn't the answer as we've had problems
>if people only fill in half of the questionnaire and so on.
>
>
>
>To state the problem directly: I want to know how you tell Access to
>'spawn' a value entered in one field of one table into fields in other
>tables which have relationships to the original field.
>
>
>
>If that made any sense (or if you think you can help but need a
>clearer explanation), please get in touch and I'll be forever
>grateful!
>
>
>
>Cheers,
>
>
>
>Tony
>
>
>
>Tony Moss <http://www.lsbu.ac.uk/psycho/staff/moss.shtml>
>
>Graduate Teaching Assistant
>
>London South Bank University <http://www.lsbu.ac.uk/>
>
>Department of Psychology <http://www.lsbu.ac.uk/psycho>
>
>103 Borough Road
>
>London SE1 0AA
>
>Room E344
>
>
>
>Tel: 020 7815 5869
>
>Fax: 020 7815 5775
>
>
>
>Email: [log in to unmask]
|