Hi all,
For starters, I apologize if I'm posting on a redundant topic; my background is in data analysis rather than DBMS or programming, so I may be beating a dead horse. I've been asked to design a relatively simple data structure for someone, and seem to have gotten myself stuck. My question is really about filters and querying for the purpose of generating reports, so hopefully I can explain this coherently.
In my database, let's assume I have data on both (a) different foods offered by a producer, and (b) consumers. Aside from numerous fields that exist uniquely for both foods and consumers, they both have approx. 20 fields/variables in common with each other, or at least that mean comparable things.
For consumers, these are preferences (e.g., "sour," "bitter," "crunchy," "soft," and so forth), whereas for foods offered, they are qualities exhibitied(e.g., "sour," "bitter," and the like). These are stored as the "Yes/No" datatype with checkboxes. So, if a consumer displayed preference for sour, salty, crunchy, spicy, then there will be "checks" for these fields in the data, while the other fields remain blank. The same for different foods.
I need to provide a report of all of the different food types, where for each food type, the is a listing of which consumers showed preference, based on common preferences expressed (by consumer) or qualities exhibited (by product). So if a certain food exhibits...sour, bitter, crunchy, I would thus need to provide a list of those consumers exhibiting preference for all of these qualities. If they show preference for these qualities PLUS other qualities which the food does not exhibit, that is okay. In and of itself, it does not seem to be that complex...there are just a lot of fields which need to matched. What is the best way to do this? Can it be done simply by designing a parameter query or filter of some sort?
Thanks for any help,
- Matt
For starters, I apologize if I'm posting on a redundant topic; my background is in data analysis rather than DBMS or programming, so I may be beating a dead horse. I've been asked to design a relatively simple data structure for someone, and seem to have gotten myself stuck. My question is really about filters and querying for the purpose of generating reports, so hopefully I can explain this coherently.
In my database, let's assume I have data on both (a) different foods offered by a producer, and (b) consumers. Aside from numerous fields that exist uniquely for both foods and consumers, they both have approx. 20 fields/variables in common with each other, or at least that mean comparable things.
For consumers, these are preferences (e.g., "sour," "bitter," "crunchy," "soft," and so forth), whereas for foods offered, they are qualities exhibitied(e.g., "sour," "bitter," and the like). These are stored as the "Yes/No" datatype with checkboxes. So, if a consumer displayed preference for sour, salty, crunchy, spicy, then there will be "checks" for these fields in the data, while the other fields remain blank. The same for different foods.
I need to provide a report of all of the different food types, where for each food type, the is a listing of which consumers showed preference, based on common preferences expressed (by consumer) or qualities exhibited (by product). So if a certain food exhibits...sour, bitter, crunchy, I would thus need to provide a list of those consumers exhibiting preference for all of these qualities. If they show preference for these qualities PLUS other qualities which the food does not exhibit, that is okay. In and of itself, it does not seem to be that complex...there are just a lot of fields which need to matched. What is the best way to do this? Can it be done simply by designing a parameter query or filter of some sort?
Thanks for any help,
- Matt
