0

I have an application that is currently using Microsoft SQL Server Lite, however I'm building a Microsoft Access database to replace the SQL Server Lite so that I can use Pivot Table Queries. For the time being, I'm working in Microsoft Access until I get the database setup the way I want it.

I have three tables tables, Players, Coaches, Ratings. Each coach must rate each player, therefore I have the following relationships:

Coaches --> Ratings
Players --> Ratings

Each coach may only rate each player once, and I want a crosstab view that will show the players as rows and the coaches as columns so that the ratings can be entered in the cells of the table.

Creating the crosstab query was easy enough to do, however it won't allow me to enter data in the cells. I'm pretty sure that this is happening because of the One-to-Many relationship. Each coach must rate multiple players, and each player must be rated by multiple coaches, so they must be one-to-many relationships in both cases. That said, each coach will only rate each player once.

Is there a way to create a compound primary key, something I need to do differently in the relationships or maybe a preliminary query that would solve this?

Thank you.

PAUL DUFRESNE
  • 348
  • 3
  • 4
  • 11
  • Not exactly, but you can do something a bit fiddly: http://wiki.lessthandot.com/index.php?title=Crosstabs,_forms_and_updating – Fionnuala Sep 26 '12 at 15:35
  • pivot views tend to be for displaying summarized data, e.g. sums of groups. unless you 'widen' the pivot so that the groups are down to invididual records, not really. – Marc B Sep 26 '12 at 15:35
  • After composing the question, I realized I should have said "crosstab view". I don't think I can edit it now. – PAUL DUFRESNE Sep 26 '12 at 16:05
  • Is the person entering the data doing it for more than one coach at a time? Seems like you can have a coach dropdown and then all the players listed with the necessary data in columns. Don't go out of your way to mimic Excel in Access. – JeffO Sep 26 '12 at 17:13
  • The link I posted in my comment shows how you can access the underlying data of a crosstab row so it can be changed. – Fionnuala Sep 26 '12 at 19:54
  • @JeffO - Yes, the person entering the data is collecting the ratings from the coach, and entering them all at the same time. Part of the intent is not only to enter the data, but also limit the data to one rating per player/per coach. – PAUL DUFRESNE Sep 28 '12 at 03:30
  • @Remou - I did look at the link you provided, and I'm not sure it's really helping me. Perhaps I don't understand the way the data is composed. I'm starting to get the sense that I won't be able to do this the way I would like, and may have to use Jeff O's suggestion using a drop down list or a pop-up form. I was really hoping that someone would tell me I was structuring my database wrong or that my relationships needed to be fixed somehow. – PAUL DUFRESNE Sep 28 '12 at 03:33

0 Answers0