 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Denormalizing for form only
Hi, all; Many times I've answered questions like this in the queries and tabledesign forum: I have a table like this: MyThing ========== MyThingID MyThingDesc MyThingField1 MyThingField2 MyThingField3 Now, how do I query in such a way that Field1, Field2, Field3 are all treated in essentially the same way? And of course what I tell them is that they need a separate table, with MyThingField1, MyThingField2, and MyThingField3 as separate records. But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. With the "correct" structure, you have to use a Left Join on some other table that will return you 3 records, and then you have to worry about a frustrated join. You also may have to add extra code to make sure that MyThingID gets into the MyNewThing table as a FK. And even then, your users are dealing with a vertical structure when the horizontal one might well be more user friendly. Or at least this is what I've always had to do. I'm wondering if there isn't something I've been missing, some wonderfully simple feature of Access, that makes using normalized data less painful from a form-building point of view. It's very frustrating telling people "you need to structure your data this way, but then once you do it you have to manhandle Access into allowing you to enter data." I'm thinking maybe it's something like PivotTable view, but so far I haven't seen any tutorials that show how to use it for this. TIA; Amy Amy Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel users, as you already know, it isn't necessary (or desirable) to use this in an Access database. What I've found quite useful for such one-to-many relationship is a main form/subform construction. Regards Jeff Boyce Microsoft Office/Access MVP > Hi, all; > [quoted text clipped - 40 lines] > > Amy > Amy > [quoted text clipped - 4 lines] > What I've found quite useful for such one-to-many relationship is a main > form/subform construction. So your position is to force the client to adapt to how Access does things, rather than finding a way to force Access to present information in the way your client finds easiest to work with. That's interesting, but it doesn't really answer my question. My preference is to try were possible to do things in a way that my client prefers. >> Amy >> [quoted text clipped - 11 lines] > preference is to try were possible to do things in a way that my > client prefers. Amy, you seem a little frustrated, but you are in error when you say >> But there is a reason people keep asking questions like this, and >> it is that Access does a much better job of making it easy to >> make data entry forms with the structure above than with the >> "correct" structure. That's not the real reason, the truth is that until people are familiar with the relational model, they tend to think "spreadsheet on steroids", and build the database based on that presumption. >> I'm wondering if there isn't something I've been missing, some >> wonderfully simple feature of Access, that makes using normalized >> data less painful from a form-building point of view. Yes, what you've been missing is using a proper subform with properly defined relations between the tables and queries. Building proper data input and data editing form/subform sets becomes child's play once you learn how. The relational structure becomes transparent to the client. As to your preference in doing what your client prefers, I say it is just lack of confidence in asserting that he will prefer it once he's used to it.  SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >>> Amy >>> [quoted text clipped - 23 lines] > familiar with the relational model, they tend to think "spreadsheet > on steroids", and build the database based on that presumption. The truth is that spreadsheets are easier _for input_, and that is why new designers tend to try to use it. Proper table design makes it way easier to get data _out_ and is trivial to populate outside of Access, such as in a web form, but Access really fights you on it. >>> I'm wondering if there isn't something I've been missing, some >>> wonderfully simple feature of Access, that makes using normalized [quoted text clipped - 5 lines] > play once you learn how. The relational structure becomes > transparent to the client. I love the way that you presume that I don't know what I'm doing, and that I'm asking from a point of complete ignorance. I am asking as someone who has been working in Access for many years, and I was wondering if maybe there wasn't some feature I'd missed that makes data entry into a normalized table structure easy, since clients often don't want to pay for the hoops you have to jump through to do it in Access--they simply don't realize how difficult it is. > As to your preference in doing what your client prefers, I say it is > just lack of confidence in asserting that he will prefer it once > he's used to it. OK, so tell me exactly how you'd make this structure easy to do data entry on. This is a real world situation that I find myself up against, trying to solve it in a way that isn't going to use hundreds of dollars worth of my time. The situation is that of a SAT score conversion table. If the subject matter is "writing" all scores must be indexed against the essay score. If the subject matter is "reading" or "math", the score is not indexed. So for any one given score, there can either be one data point, or seven. It's much easier to keep mental track of the scores that index to a particular "raw" score if you can enter them all in a row, across. At a minimum, it means you don't have to enter the same raw score seven times. Each practice SAT exam can have its own score conversion table for math, reading, and writing. The table structure is this: ScoreSet =========== ScoreSetId-Autonumber, PK SubjectName-reading, writing, math ScoreSetDesc-will allow users to select this set later and associate it with an exam ScoreSetItems ============ ScoreID-Autonumber PK ScoreSetID-FK to scoreset WritingScore-score to index this on (will be 0 for subjects that don't apply, 0-6 for writing) RawScore-the actual score on the multiple choice questions SATScore-the scaled SAT score in the given subject Your task, should you choose to accept it, is to create a form structure that uses no code and no left joins and is not a royal pain in the butt to enter all 7 data points for scores from -12 to 49 (434 data points PER exam just for writing, plus the 65-90 data points for the ones that are not as complicated) and that will be easy to use and intuitive. Maybe that will explain to me what I've missed, and how this is so easy ;-). -Amy Amy, I certainly understood what you meant the first time. The two solutions you were offered were vertical entry-based, which is counter to what you stated that you wanted. I too have been frustrated at times by this limitation, and in at least one case, I've intentionally denormalized some to accomodate this limitation. In this case, the customer wanted a spreadsheet-like view of the data (simple enough with a crosstab result), however, the data also had to be editable. I can tell you that lots of people have requested this in the past to Microsoft. I just did a few weeks ago, and my contact at Microsoft replied that they have heard this request many times. I'm not positive, but I think one can accomodate this type of data entry with an ActiveX grid control, but that introduces issues related to using ActiveX controls (distribution, licensing, proper registration, etc.). Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html > Amy, > [quoted text clipped - 11 lines] > be > editable. Unfortunately, I find that when I denormalize to accommodate data entry, I regret it on the data extraction/analysis side. Either way, the client has to spend unnecessary money or I just put in free time. > The situation is that of a > SAT score conversion table. If the subject matter is "writing" [quoted text clipped - 6 lines] > same raw score seven times. Each practice SAT exam can have its > own score conversion table for math, reading, and writing. Before I can proceed with the task, please explain what you mean by indexed? do you mean scaled against the minimum and maximum? Please explain the process of creating the conversion table? (formulas, algotithm) > The table structure is this: > [quoted text clipped - 13 lines] > RawScore-the actual score on the multiple choice questions > SATScore-the scaled SAT score in the given subject is RAWScore applicable to the writing test? or only WritingScore? How is SATScore determined? sonds like it should be a calculated field, or looked up from another table or query. > Your task, should you choose to accept it, is to create a form > structure that uses no code and no left joins and is not a royal [quoted text clipped - 7 lines] > > -Amy SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >> The situation is that of a >> SAT score conversion table. If the subject matter is "writing" [quoted text clipped - 9 lines] > Before I can proceed with the task, please explain what you mean by > indexed? do you mean scaled against the minimum and maximum? I mean it is indexed. If the raw score is 1 and the writing score is 0, then the SAT score will be something like 220 (I don't have an exact table, this is just an estimate). If the raw score is 1 and the writing score is 1, then the SAT score will be more like 240. > Please explain the process of creating the conversion table? The process is of tedious data entry. Look at the source graphic, enter the number where it goes. >>> The situation is that of a >>> SAT score conversion table. If the subject matter is "writing" [quoted text clipped - 16 lines] > and the writing score is 1, then the SAT score will be more like > 240. What you mean is that it's a lookup table? >> Please explain the process of creating the conversion table? > > The process is of tedious data entry. Look at the source graphic, > enter the number where it goes. The source graphic must have been created by a table, somewhere, or calculated. SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >>>> The situation is that of a >>>> SAT score conversion table. If the subject matter is "writing" [quoted text clipped - 18 lines] > > What you mean is that it's a lookup table? In essence, but with two look up axes, which is why doing things the way Access normally supports things is a PIA. And even to get what Access supports, you have to use code and left or right joins. >>> Please explain the process of creating the conversion table? >> [quoted text clipped - 3 lines] > The source graphic must have been created by a table, somewhere, or > calculated. True, but since I don't have access to the original, hardly relevant. >>>>> The situation is that of a >>>>> SAT score conversion table. If the subject matter is [quoted text clipped - 24 lines] > what Access supports, you have to use code and left or right > joins. Aw, come on... All you need is a table with two key columns and one value column. Not left joins. and a simple where clause in a Dmin() function. >>>> Please explain the process of creating the conversion table? >>> [quoted text clipped - 6 lines] > True, but since I don't have access to the original, hardly > relevant. If the result set is a linear line or a simple polynomial, it can be calculated. This might require a little code, but not necessarily. SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >>>>>> The situation is that of a >>>>>> SAT score conversion table. If the subject matter is [quoted text clipped - 28 lines] > value column. Not left joins. and a simple where clause in a Dmin() > function. I'm talking about _form_ structure, not table structure. If you look at my table structure, it is as you've described. Please tell me how this translates to a form structure that allows the user to enter the double-indexed value without having to repeatedly re-enter at least one of those values that also provides enough spaces to ensure that all six values get entered when appropriate. Thanks; Amy > I'm talking about _form_ structure, not table structure. If you > look at my table structure, it is as you've described. [quoted text clipped - 8 lines] > > Amy The table structure is wrong, therefore the form structure cannot be right. ScoreSet =========== ScoreSetId-Autonumber, PK SubjectName-reading, writing, math ScoreSetDesc-will allow users to select this set later and associate it with an exam ScoreSetItems ============ ScoreID-Autonumber PK ScoreSetID-FK to scoreset WritingScore-score to index this on (will be 0 for subjects that don't apply, 0-6 for writing) NewTable ============ NewTableID-Autonumber PK ScoreID-FK to scoresetItems RawScore-the actual score on the multiple choice questions The following should be a calculated value, not a table field. SATScore-the scaled SAT score in the given subject SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >> I'm talking about _form_ structure, not table structure. If you >> look at my table structure, it is as you've described. [quoted text clipped - 34 lines] > The following should be a calculated value, not a table field. > SATScore-the scaled SAT score in the given subject There's no advantage to this structure (what's the point of providing a fk that just goes to a number?), and the scaled SAT cannot be calculated, and so must be ENTERED BY HAND. Since you haven't grasped that concept, I'm guessing you're not going to be able to provide a form that will allow that to happen efficiently. I appreciate the time you've taken, though. >>> I'm talking about _form_ structure, not table structure. If you >>> look at my table structure, it is as you've described. [quoted text clipped - 42 lines] > going to be able to provide a form that will allow that to happen > efficiently. I appreciate the time you've taken, though. When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong. ---Arthur C. Clarke I say it can be calculated. :-) As to the advantage, it allows normalization. SignatureBob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** >>>> I'm talking about _form_ structure, not table structure. If you >>>> look at my table structure, it is as you've described. [quoted text clipped - 51 lines] > > As to the advantage, it allows normalization. OK, get me the formula the college board uses, and I'll calculate it :-). If I _could_ calculate it, then all the data entry wouldn't be necessary, so I wouldn't need a form. However, the table structure _is_ normalized. The problem is that Access doesn't make it easy to build forms that work with a properly normalized structure in many cases. I was hoping when I asked this question that there was something I'd been missing all along, but it looks like if there is, I'm in really good company, since everyone else missed it too! On May 16, 12:25 pm, "Amy Blankenship" <Amy_nos...@magnoliamultimedia.com> wrote: > >>>> I'm talking about _form_ structure, not table structure. If you > >>>> look at my table structure, it is as you've described. [quoted text clipped - 63 lines] > > - Show quoted text - google is your friend. These sites explain pretty well how to generate the scaling algorithm http://en.wikipedia.org/wiki/SAT#Raw_scores.2C_scaled_scores.2C_and_percentiles http://www.colinfahey.com/oldpages/2003apr5_sat/original_2003apr5_sat.htm http://professionals.collegeboard.com/data-reports-research On May 16, 12:25 pm, "Amy Blankenship" <Amy_nos...@magnoliamultimedia.com> wrote: > "Bob Quintal" <rquin...@sPAmpatico.ca> wrote in message > [quoted text clipped - 75 lines] > > - Show quoted text - google is your friend. These sites explain pretty well how to generate the scaling algorithm http://en.wikipedia.org/wiki/SAT#Raw_scores.2C_scaled_scores.2C_and_percentiles http://www.colinfahey.com/oldpages/2003apr5_sat/original_2003apr5_sat.htm http://professionals.collegeboard.com/data-reports-research -------------------------------------------- No they don't. If you knew the percentile for a given test (which we don't), you could use link 1. Link 2 shows an example curve for _one_ test, but any idiot can see it's not actually a smooth curve so there wouldn't be a way to generate it with a mathematical function (plus he doesn't give the exact process used to arrive at the data points, just a general explanation). Link 3 doesn't seem to provide any relevant information. If you dig, you can find the percentiles that got a given _scaled_ score, but that's pretty much useless given link 1 (circular). I repeat, if you can come up with a particular mathematical formula that could be used, I would use it (at least for entering prototype data), but it seems pretty clear that this is not available. But I do thank you for the time you have continued to put into this. I would submit, though, that even if you did find an exact algorithm that would work in all cases, probably my client would still want the ability to input the numbers by hand. Amy I rarely force my clients to learn Access. It is a power tool, not unlike a table saw. It is not a "bookcase", like Word or Excel. If I've done a reasonable job of creating a user interface that's "discoverable" and well-documented (internally, not via a "user manual/code book), the users don't even know/care that I built the application using MS Access. Regards Jeff Boyce Microsoft Office/Access MVP >> Amy >> [quoted text clipped - 10 lines] > but it doesn't really answer my question. My preference is to try were > possible to do things in a way that my client prefers. > Amy > [quoted text clipped - 5 lines] > manual/code book), the users don't even know/care that I built the > application using MS Access. That's kind of the point of my question... > Hi, all; > [quoted text clipped - 25 lines] > users are dealing with a vertical structure when the horizontal one might > well be more user friendly. Why limit yourself to Access input only? If users are more comfortable with Excel-type input, let them use that. Then analyze the data in Access. The whole idea behind Microsoft Office is to be able to use a suite of programs that interoperate. You can have the front end be an Excel worksheet while the back end is Access. I've also seen Word used to create forms that created a data file for Excel or Access. Just as a professional carpenter has many tools and many types of the same tool to get a job done, so should we use all the tools we have! Tom Lake >> Hi, all; >> [quoted text clipped - 29 lines] > with Excel-type input, let them use that. Then analyze the data in > Access. The data needs to be in the database. Why is this concept so foreign? > The whole idea behind Microsoft Office is to be able to use a suite of > programs that interoperate. You can have the front end be an Excel > worksheet while the back end is Access. I've also seen Word used to > create forms that created a data file for Excel or Access. Just as a > professional carpenter has many tools and many types of the same tool > to get a job done, so should we use all the tools we have! Well, certainly I can write an import routine that can take a spreadsheet and split it out into the requisite records, but this thread is about trying to get Access to allow input of data in the format that you're actually supposed to use in Access. If you have to go outside Access to work with data in a format that makes sense to users, then write a routine to fix it, Access isn't really fit for the purpose it's advertised for. And that's more money my client has to spend without a real understanding of why, or I just have to eat. I'm really tired of solving this problem over and over! >> Why limit yourself to Access input only? If users are more comfortable >> with Excel-type input, let them use that. Then analyze the data in >> Access. > > The data needs to be in the database. Why is this concept so foreign? The Excel worksheet can be linked in and used just as a native Access table. The users should have no idea where the data is stored nor what format it's in. They should be able to just run their business. My users never see a query, table, module or anything other than input Forms (which may be Word documents, Access forms or Excel worksheets) and Reports. It takes more work on my part to make sure everything they need is included but it's worth it to them. They pay me well! > Well, certainly I can write an import routine that can take a spreadsheet > and split it out into the requisite records, but this thread is about [quoted text clipped - 4 lines] > And that's more money my client has to spend without a real understanding > of why, or I just have to eat. That's my point. There is no reason to use Access (or any other tool) for problems it wasn't meant to solve. Yes, Access IS limited in its input functionality for your particular application. That's why you have to use the proper tool for each job. If it's a combination of Excel, Access and any other program then that's what you do. Your client probably already has the whole Office suite anyway. It wouldn't cost extra to combine solutions. > I'm really tired of solving this problem over and over! and THAT is what the Office suite is meant to eliminate. Tom Lake >>> Why limit yourself to Access input only? If users are more comfortable >>> with Excel-type input, let them use that. Then analyze the data in [quoted text clipped - 14 lines] > to them. > They pay me well! That's great for what you need, but for what I need it _has_ to be in the database. Please trust me to know my own requirements. I also am paid well, though less so if I have to eat time because I can't convince my client that he should pay for a deficiency in the tool I've chosen. >> Well, certainly I can write an import routine that can take a spreadsheet >> and split it out into the requisite records, but this thread is about [quoted text clipped - 14 lines] > the > whole Office suite anyway. It wouldn't cost extra to combine solutions. Yes, it would. Amy, I understand your frustration. I've designed a couple report card applications for various schools and each was pretty tough to normalize and yet make data entry easy. Could you post a sample dataset of how it should look to the user? I feel like there's something that I'm not understanding and maybe looking at the final product would help. I've always found solving the mazes on the back of a cereal box easier to start from finish than start. >Hi, all; > [quoted text clipped - 40 lines] > >Amy > Amy, > [quoted text clipped - 6 lines] > like there's something that I'm not understanding and maybe looking at the > final product would help. I assume you mean to the data entry person rather than the actual end user, who will only see the end result of the calculation. I ultimately decided to go with an excel import. Here are a few sample rows from excel (note that the first row is the writing score, and the first column is the raw score). I ran it through notepad to remove the excel formatting: Raw Score 0 1 2 3 4 5 6 49 650 670 690 710 750 780 800 48 630 640 660 690 720 760 780 47 600 620 640 660 690 720 760 46 580 600 620 650 680 710 740 45 570 580 600 630 670 700 730 44 560 570 590 620 660 690 720 Here is what this looks like after import: ScoreID ScoreSetID WritingScore RawScore SATScore 1 2 0 49 650 2 2 1 49 670 3 2 2 49 690 4 2 3 49 710 5 2 4 49 750 6 2 5 49 780 7 2 6 49 800 8 2 0 48 630 9 2 1 48 640 10 2 2 48 660 11 2 3 48 690 12 2 4 48 720 13 2 5 48 760 14 2 6 48 780 15 2 0 47 600 16 2 1 47 620 17 2 2 47 640 18 2 3 47 660 19 2 4 47 690 20 2 5 47 720 21 2 6 47 760 22 2 0 46 580 23 2 1 46 600 24 2 2 46 620 25 2 3 46 650 26 2 4 46 680 27 2 5 46 710 28 2 6 46 740 29 2 0 45 570 30 2 1 45 580 31 2 2 45 600 32 2 3 45 630 33 2 4 45 670 34 2 5 45 700 35 2 6 45 730 36 2 0 44 560 37 2 1 44 570 38 2 2 44 590 39 2 3 44 620 40 2 4 44 660 41 2 5 44 690 42 2 6 44 720 This is the import routine I use, in case it helps anyone: Sub importScoreConversion(catID, SetDesc) 'with thanks to Danny Lesandrini 'http://www.databasejournal.com/features/msaccess/article.php/3557541 'database vars Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String 'vars that have temp uses Dim s As String, dlg As Variant, i As Integer, j As Integer, k As Integer, strDoWhat As String ' Excel object variables Dim appExcel As Excel.Application, wbk As Excel.Workbook, wks As Excel.Worksheet 'vars for dealing with the worksheet once it's open Dim endRow As Integer, endCol As Integer, sheets As Integer, startRow As Integer 'variables that contain actual values to append Dim sheetName As String, rawScore As Integer, writingScore As Integer Dim scaledScore As Integer, setID As Integer On Error GoTo Cleanup ' let user select excel file Set dlg = Application.FileDialog(msoFileDialogFilePicker) strDoWhat = "finding file" With dlg 'note this line doesn't actually work as expected, but it gets close enough .InitialFileName = Left(CodeProject.Path, InStrRev(CodeProject.Path, "\\")) If .Show = -1 Then s = .SelectedItems(1) End With strDoWhat = "Opening workbook" Set appExcel = Excel.Application Set wbk = appExcel.Workbooks.Open(s) sheets = wbk.Worksheets.Count startRow = 2 Set db = CurrentDb() For i = 1 To sheets Set wks = Nothing Set rst = Nothing strDoWhat = "parsing sheet " & i Set wks = wbk.Worksheets(i) s = wks.UsedRange.Address endRow = CInt(Mid(s, InStrRev(s, "$"))) s = Mid(s, InStr(s, ":$") + 2, (Len(s) - InStrRev(s, "$")) - 1) endCol = Asc(s) - 64 ' only works up to Z, but expect max of H sheetName = wks.Name 'insert set record strDoWhat = "inserting new set" strSQL = "INSERT INTO ScoreSets (KCategorySetID, SetDesc) " & _ "SELECT KCatSetID, '" & SetDesc & " " & sheetName & _ "' FROM KCategorySet WHERE KCatSetDesc = '" & sheetName & "'" 'execute query db.Execute strSQL 'retrieve new ID strDoWhat = "retrieving new score set ID" strSQL = "SELECT Max(ScoreSetID) FROM ScoreSets" Set rst = db.OpenRecordset(strSQL) If Not rst.EOF Then setID = CInt(rst(0)) Else Err.Raise 999, , "Score set could not be created or retrieved" End If 'associate this set with the catID strDoWhat = "attaching score set to category" strSQL = "INSERT INTO CategoryScoreSet (CategoryID, ScoreSetID) " & _ "VALUES (" & catID & ", " & setID & ")" db.Execute strSQL For j = startRow To endRow rawScore = CInt(wks.Cells(j, 1)) For k = 2 To endCol writingScore = k - 2 scaledScore = wks.Cells(j, k) 'Debug.Print "rawscore " & rawScore & " writingScore " & writingScore & " scaledScore " & scaledScore strDoWhat = "inserting " & sheetName & " raw " & rawScore & " writingscore " & writingScore strSQL = "INSERT Into ScoreSetItem (ScoreSetID, WritingScore, RawScore, SATScore) " & _ "VALUES (" & setID & ", " & writingScore & ", " & rawScore & ", " & scaledScore & ")" db.Execute strSQL Next Next Next Cleanup: If Err.Number Then Debug.Print "An error occurred in " & strDoWhat & " (" & Err.Number & "): " & Err.Description End If On Error Resume Next wbk.Close Set wbk = Nothing Set appExcel = Nothing rst.Close Set rst = Nothing Set db = Nothing End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.