I built a database a few years ago to document ergonomic evaluations. Each record has employee information, ergonomic information, but my problem is with the ergonomic items that I purchase for the person. I originally created the database with repeating fields for Qty, Item#, Item Description, Price and Total cost. It's worked out just fine the last few years but now I need to produce cost information. The problem, as you might have already guessed, it that when I conduct a query in the item description field for say, "chair", it finds the records that contain "chair" just fine but the "Total" cost (which is what I'm after) includes the cost of all the other items purchased for that person on that record, other than just "Chair". For example, line item# 1 might be a chair, line item# 2 is a footrest, line item# 3 is a keyboard, etc. It gives me the total for the entire record rather than just a total of the cost of the chairs.
I'm hoping that someone can give me some general pointers regarding how to better build this database so that I can perform queries to group costs by item purchased (total cost of chairs or total cost of keyboards, etc). Below is an example of a common record to help clarify the mess that I've created.
Thank you in advance. Dave