Three in one?

[jpstroud]

Hi folks. I'm pulling my hair out on this one.

I am creating a database to keep track of product evaluations that come in. We have a table with evaluator info, one with product info, and one that stores the evaluations themselves (since the evaluator usually evaluates more than one product).

We'd like to have a form that goes by the evaluator first, then you choose the product, then you enter in the answers to the questions. In this particular database, each question is it's own row of data (ID, Q#, Score, Comments).

I can get the overall database to work, but I'd prefer not having to select the product every time as there can be 20 questions per product per evaluator.

Any thoughts on how I can get this to work in a form? I've been trying to nest subforms, but that's not really working.

Thanks!

 

[TheAceMan1]

how are ya jpstroud . . . . .

[blue]but [purple]I'd prefer not having to select the product every time[/purple] as there can be 20 questions per product per evaluator.[/blue]

[purple]What is it you'd rather do?[/purple]

See Ya! . . . . . .

 

[Edski]

I think I understand. The way you've set-up your database may be the problem. It sounds like you have to select the product for each question. Correct? And you don't want to have to do that. I wouldn't want to either!

You may have to Normalise your database first. Create another table that contains EvaluatorID, ProductID and the 20 questions (call the table 'tblEvaluations'), and link it to the tblEvaluators and tblProducts tables. That way you'll only have to enter the product once.

Does that make sense? Hope so.

 

[jpstroud]

Yes, it does make sense, and is the way I would normally build the database. I was hoping to not have to rebuild that particular part of the database if there was another way.

AceMan: I'd rather select the product once, jump into the subform and do the questions, without having to do the product again.

 

[jpstroud]

Edski, I forgot to mention, I have the keys in the Evaluations table already (reviewers <--> evaluations <--> product). I can't seem to make a form that sets it up how I'd like however.

As I said in the first post, I'm looking to create a form that allows you to chose the reviewer first (not a filter), then the product, and then go through the questions.

My original thought was similar to below:

======Form=======
Reviewer information
------Subform------
Product Info (this can just be a single combo box, as editing prod info is elsewhere)
------sub-subform-----
Scores

 

[Edski]

So has the data been entered yet? If not, then redesign the database. You can't build the forms unless the tables have been built and set-up correctly.

If you really don't want to redesign the database, then you are going to be duplicating the products information. I wouldn't recommend doing this but you can write some code to do this automatically for you. Eg, enter the product ID once, then enter the 20 evaluations, each time the program will enter the same product ID. Again, I highly advise [red]against[/red] doing this.

 

[jpstroud]

I've redesigned it. I wish they'd stop giving me half finished databases (usually WITH data in them) to fix. (I'm a graphic designer, I USED to work with programming and DBs)