Filter Fields

[jereece]

I am not sure if this is the correct section of the forum, but here goes.

Lets say I have a database with the following filelds

Site
Group
Date
Section1Comments
Section2Comments
Section3Comments

I ultimately want to build a report to show the Site, Group Date and comments from only one section. I have a form with a combo box to select which section to view the comments. The problem is the comments are in different fields (Section1Comments, Section2Comments, etc.). Is there a way to use a query to only show the section comments based on the combo box selection?

For example to select the Group, I have a combo box on the form and in the query I wrote "[Forms]![frm_Main]![cboGroup]. However in this case all the groups are in the same field. So I am unsure if there's a way to do this with different fields.

I appreciate any suggestions.

Jim

 

[jereece]

I am afraid there's not just 3 comment fields but more like 10. It would be a mess to combine all of these into one and difficult for the end user as well. I am afraid I am stuck with the comment fields being separate.

 

[gemma-the-husky]

uncle giz isnt suggesting you put the comments together in one field

hes suggesting you move to a design with an extra comment table, so you get

table site
Site
Group
Date

{re-reading it, i think uncle giz took the above are attributes of the comment - which they may be, in which case you need just a comment table, designed differently. i thought the group and date item were attributes of the site, not particularly the comment}

table site comments
site
comment number
Comments


so in the comments table you have

siteno comment_no comment
1234 1 "comment1"
1234 3 "comment3"
1234 5 "comment5"
1234 7 "comment7"
1236 2 "comment2"
1236 3 "comment3"
1236 5 "comment5"

etc, rather than the multi column approach you have at the moment

--------
takes a bit of effort to get there, but much easier to use afterwards