Value List Displaying only Some Data from a Field

[MariaAux]

Hi all, Hope yo had a great weekend!

I've got a report card writing Database and been trying to get a value list working all weekend, but just can't seem to do it.

I've got a table with the fields (and a few examples below) as follows;

COMMENTS table

--------------------------

ENGLISH CATEGORY

He is smart Positive Comments

He is hard-working Positive Comments etc...

He is lazy Negative Comments

He needs to focus Negative comments etc...

His reading ability is good Reading comments

His Reading is improving Reading comments etc..

...and so on

I've got another table where I am trying to display these comments in a drop down list. Creating a value list from the field Comments::English is easy, but it display ALL the comments, and I have 100 comments covering 10 categories.

What I am trying to achieve is for each particular value list to only display the comments from the 'Positive Comments' Category and the next value list to only display the comments from the 'Negative Comments' category etc.

I've tried creating a self-join table and then specifying the option 'Include only related values starting from' and relating the self-join table by way of category. Still no luck.

I would be so thankful for any ideas or advice.

Much appreciation in advance,

Maria

[comment]

How about something like this?

PosNegRemarks.zip

[MariaAux]

Hi There,

Thanks so much for you sample file. I studied it all morning and I think I understand the nuts and bolts of it. Setting up your calculation cTrue=1 limited the value list results based on your PositiveRemarks and NegativeRemarks tables. The initial information of which Value List data to show came from the data in the 'Subject' field.

In my database, my value list does not need to be based on the data in a related field. I simply need to limit a long list of data from a field filtered by two other criteria, and after hours of tampering, I just can't get my value list to display my data. I don't even feel as if I am close. I either display all the data in the field, receive a <No Values Defined> or get a 1 and 0, which I have no idea where it comes from.

I have deleted most of my database to reduce the size leaving only the necessary Tables and Fields. If you have the time, would you please be so kind to have a look and tell me if what I am trying to do is simply Undoable? I could always go the long-winded route and create a new table for each set of comments making the value lists easier, but the database unnecessarily complicated.

Thank you so much for your time and your help, I really appreciate it.

Thanks in advance,

Maria

Value Lists based on Related Field.zip

[comment]

In my database, my value list does not need to be based on the data in a related field.

A value list can either show all values (i.e. values from all records in the table) or related values only. If you want to show only values in certain category, you must have a relationship based on category.

I could always go the long-winded route and create a new table for each set of comments

I don't think that would be a good idea (to put it mildly). However, I believe you are missing a table between ReportCards and the remarks; this is the Assessments table in my example file. One report card has many assessments (one for each category) and cramming them all into the report cards table is not such a good idea either.

[comment]

BTW, here's your file with the value list working as you wanted. Hopefully you will see that this is not practical for more than a handful of categories.

Value Lists based on Related Field.zip

[MariaAux]

Thanks for your great help,

I was just sending you my file. I think I did almost exactly what you have done and suggested me to do. I Created an intermediary table and related both the 'Category' and 'Validate' fields to the new table and yes, I got the same result as you. I believe I would need to create a new table for each category multiplied by 2 to divide them up between Validate 1 & 2? Am I correct in my assumption?

If I am, then yes, as you so rightly mention, this solution is totally impractical and we might as well start with a table for each category. Or is there something I am missing?

Thanks in advance and much appreciated!

Maria

Value Lists based on Related Field2.zip

[MariaAux]

Ok, retract my last comment. Whereas I created a new table you have just duplicated the English_Spanish Table, obviously a more elegant solution.

Then my question is, why is it not a practical solution. For 8 Categories (x2 for Validate 1 & 2), is it bad to duplicate a table 16 times?

Thanks again

[comment]

I believe I would need to create a new table for each category multiplied by 2 to divide them up between Validate 1 & 2?

Not a new table, only a new occurrence of the same table. Still not practical, IMHO.

we might as well start with a table for each category.

No, that is not necessary. But if each assessment (or score, if you prefer) is a record of its own and has a category attribute, it can then filter the remarks table based on a single relationship for all categories.

[MariaAux]

Not a new table, only a new occurrence of the same table. Still not practical, IMHO.

Ok, got you.

No, that is not necessary. But if each assessment (or score, if you prefer) is a record of its own and has a category attribute, it can then filter the remarks table based on a single relationship for all categories.

But each comment is it's own record already and already has a category field therefore giving it a category attribute.

How could we then filter the remarks table based on a single relationship for all the categories? Isn't this the problem that we have been running in to all along? We can't filter some records without the intermediary tables?

Am I missing something?

Thanks again for your valuable time