Best structure to handle multiple complaints/dates

[Zaxxon]

I've been running into some issues with queries and inputing data by forms, but before tackling those problems, I thought I should make sure I have the best structure for what I'm working with. I've been playing with a bunch of different approaches and I've reached the point where it's gotten a little messy.

I have a database tracking orders and complaints. I had one field [First Complaint] with a lookup column that lists 10+ different types of complaints. Then I ran into the problem that some orders had multiple complaints. So then I created two more fields [Second Complaint] and [Third Complaint]. Each complaint field also has a field for the date [Date of Complaint]. In total there were six fields for complaints.

Then though I found I had trouble with my queries as far as counting the number of complaints as they were spread across three fields. At that point I tried creating a yes/no field for the most common complaints. That made it easy to count the complaints using:

TotalBadService: Sum(Abs([Complaint of Bad Service filed]))
TotalMissingItem: Sum(Abs([Complaint of MissingItem filed]))

etc...

Problem with that approach was (1) having to have tons of fields and (2) updating each yes/no field using a drop-down box listing the types of complaints on a form.

Each order so far doesn't have more than 3 complaints, but just to be safe I figure I should allow for 4. Any thoughts on the best way to tackle this?

 

[georgedwilkinson]

Normalize the design. Create a separate OrderComplaints table that has a FK from Orders and another FK from ComplaintTypes. Create a field with a timestamp and make those 3 fields the "unique" key. You'll be able to insert a subform into your main form to display/manipulate data in the new table.