Tables - can you break up data too much?

[Reviewer]

I am an Access newbie. I am trying to decide how many tables I should use. I am compiling topics from our customer reviews for our 165 locations. I already have set up a table that contains all of our locations.

I also need to keep track of the following information:

• Review Received by: Examples: Yelp, Internal Survey, Google+, Facebook, etc.
• Date of review
• Reviewer Name
• Review Content
• Location that received review - I think I want to be able to look this up from the Location Table?
• Phase in customer lifecycle review was left - options to select: Before purchase, during purchase, after purchase.
• Positive comments: I have 17 categories - example of category is Customer Service, Location, Parking, Fees. Each of the categories has many subcategories (111 total subcategories) example: Customer Service subcategories would include: Hours of operation, communications to customer, Response time for phone calls. These are all just a yes or no selection - did the customer talk about it or not.
• Negative comments: Same as positive comments for set up, there are 16 categories, and 136 subcategories

Do I set up a table for each Review Received by? - that is still 252 fields per table - I'm not even sure a table has the option for that many fields.



Please help! I am completely lost and under the gun to get this project moving.

[LaughingBull]

Check out this video on table relationship https://www.youtube.com/watch?v=dZEs-JCkOmk

and this one on normalization https://www.youtube.com/watch?v=Rwkr4ueBvfY

Good luck

[June7]

In my view, it is a balancing act between normalization and ease of data entry/output. A common mantra is "normalize until it works, denormalize until it hurts" (or vice versa).

Why do you have 252 fields? Field limit is 255.

Should not be a field for each category and subcategory.

Do NOT have a table for each Review Received By.

Maybe this will help http://www.rogersaccesslibrary.com/

Consider:

tblSources
SourceID
SourceName

tblLocations
LocationID
LocationName

tblReviewers
ReviewerID
ReviewerLastName
ReviewerFirstName

tblCategorySubCat
ID
Category
Subcategory

tblReviews
ReviewID
LocationID
SourceReceivedByID
ReviewDate
ReviewerID
Phase

tblReviewDetails (there will only be record for a CategorySubCatID if that item was discussed - not 252 yes/no fields)
ReviewID
CategorySubCatID
Comment

[Reviewer]

Thank you so much! I realized while reading your post that I was not thinking in the database way. It is quite a bit different. When I posted the question I was thinking of how I wanted the form to look with check boxes. Now (thanks to you) I realize that I needed more tables. I have the tables built and am now working on the form for data entry. Quick question - is there a way to make the list box display in several columns. I have quite a few subcategories to scroll through and want to display them all on the screen in the form rather than having to scroll through them all to make selections.

[June7]

If I understand the question, no, cannot have the subcategories display in multiple columns of a listbox or combobox. Each line represents a single record from the source table.

If you have a long list, scrolling probably cannot be avoided, whether using a combobox or listbox. However, maybe this tutorial will offer a partial solution http://www.datapigtechnologies.com/f...combobox2.html

[LaughingBull]

create the form then in design view add a combobox from the tools menu you click on the combox release your mouse then take your mouse to the form where you want to place it then a menu comes up select info from the form and when you all finished view the form and select an item and all the info will show up for that record for all the fields you put on the form.