Retrieving data question

[Kenneth Ho]

Hi all,
I have a question about retrieving records, hope you may help.

I have a table which has 11 fields: One Name field ( for linkage ) and
10 Yes/No fields.
I have a form to let users to key in the Name and then put a tick on
those Yes/No fields as necessary.

My question is, if I want to have another form which only shows "tick"
fields of such record, how can I do that?
For example, Record 1: Name is AAA, I "tick" 3 Yes/No fields.
How may I only show that 3 fields in another form?

Any suggestions are very much appreciated. Thanks!

Regards,
Kenneth Ho

 

[John W. Vinson]

Hi all,
I have a question about retrieving records, hope you may help.

I have a table which has 11 fields: One Name field ( for linkage ) and
10 Yes/No fields.
I have a form to let users to key in the Name and then put a tick on
those Yes/No fields as necessary.

My question is, if I want to have another form which only shows "tick"
fields of such record, how can I do that?
For example, Record 1: Name is AAA, I "tick" 3 Yes/No fields.
How may I only show that 3 fields in another form?

Any suggestions are very much appreciated. Thanks!

Regards,
Kenneth Ho

I strongly suggest that you reconsider your table design. What will you do if
you need to add an eleventh field? Redesign your table, all your forms, all
your reports, all your queries?

A better design would be a many to many relationship using three tables:

People
PersonID <don't use names for primary keys, they're not unique>
LastName
FirstName
<other bio information>
<or, if your "names" are unique names of some other entity, a table with one
record for each such entity; still use a numeric or autonumber primary key for
linking>

Attributes
AttributeID
Attribute <what you're currently using as your yes/no fieldnames>

PeopleAttributes
PersonID <who has the attribute>
AttributeID <what attribute they have>

Use a Subform to select attributes. This will show you just the selected
values.


John W. Vinson [MVP]

 

[Kenneth Ho]

Thanks John. Actually I have about 30 Yes/No fields. What I think is, if
the fields are in one table, I can arrange them all in one form, e.g. 10
fields in one column and totally 3 columns. So, users may tick what they
want on one screen.

On the contrary, if I put the Yes/No fields in the "Attributes" table, users
need to select one by one, and you know, 30 selections, which is not easy to
do.

I think if there is a way to arrange all 30 records in "Attributes" table on
one screen, the problems could be solved. But I have no idea on how to do.
Because Access doesn't let me arrange records like this in parrallel format:
[Record 1] [Record 2] [Record 3]
[Record 4] [Record 5] [Record 6]
.......

So, John, what would you recommend? Thanks so much!

Regards,
Kenneth Ho

 

[John W. Vinson]

I think if there is a way to arrange all 30 records in "Attributes" table on
one screen, the problems could be solved. But I have no idea on how to do.
Because Access doesn't let me arrange records like this in parrallel format:
[Record 1] [Record 2] [Record 3]
[Record 4] [Record 5] [Record 6]
......

So, John, what would you recommend? Thanks so much!

You could do it with three continuous subforms, or three multiselect listboxes
(with a bit of code to move the data into the table), or with thirty
checkboxes (and some more complex code to move the data).

John W. Vinson JVinson *at* Wysard Of Info *dot* com