If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#51
|
|||
|
|||
Calendar and time sheet
Having become mightily annoyed at PC Datasheet's boneheaded advertising,
I did complain about him, but now I kinda miss him, haven't seen him for a while now. I suppose his helpful comments never came without the spammy advertisements... :-( Anyway, having played around with Fehn's request a bit (and writing some VBA code), I came up with what I think is a way to do it without a heck of a lot of trouble. (But, as Ed Warren suggested, I didn't see an obvious way to do it without VBA.) My first thought was to populate a Form with a bunch of little list boxes, one per datum, but I quickly abandoned that -- each one needed a separate name, and the number would be fixed. We want a set that would adjust automatically to the number of columns resulting from the data set. So, I plugged in a Microsoft Excel spreadsheet (Microsoft Office Web Components 9.0) in MS Access 2000 (had to change it to Microsoft Office Web Components 11.0 in Access 2003), and got good results. This is an object with OLE Class = "Microsoft Office Spreadsheet 9.0" and Class = "OWC.Spreadsheet.9". To use it, you can create a blank Form and paste a Microsoft Office Spreadsheet 9.0 object onto the Form Header section, since you won't have any underlying records. On my Form, I opened the Properties sheet in Form Design View and turned off features I didn't need, such as navigation controls, scroll bars, the spreadsheet's Tool Bar and Title Bar, etc. (In the Access 2003 version, Microsoft Office Spreadsheet 11.0, I wasn't able to hide the column headers such as "A", "B", &c.) In addition to pasting the object into the blank Form, you'll also need to set references in VBA to the objects, specifically to (in Access 2000) "Microsoft Office Web Components 9.0" (found in Office\MSOWC.DLL) and "Microsoft DAO 3.6 Object Library" (found in Microsoft Shared\DAO\dao3). The OWC lets you play with the spreadsheet, and the DAO lets you read & write your database Tables. (Well, actually, there's only one Table in this example.) A nice feature of the OWC Spreadsheet is that all of the cells in it already have names (you don't need to assign any), and there are probably more of them than you'll ever need. There were certainly more than enough for my version of your example. Since some of the questions weren't really answered here, such as exactly HOW one is intended to edit the editable values, I made some simplifying assumptions. The data are displayed as in Ed's "Case A" layout, but if you try to edit one by clicking on it, a dialog box pops up asking for the new value, and you can either enter one or cancel the request. I made no provisions for adding new data nor for zapping existing ones. Those functions would be easy to add, but you'd need to decide how you wanted them to work. For example, you could allow a new value to be entered into a spreadsheet cell, and the "On Update" Event could do what my pop-up dialog box does. But I omitted stuff like that because it would make a long example even longer and not add much in the way of clarity. I figured an InputBox() is about as easy as anything is to program, so that's what I used. OK... now to the specific example. The raw data, in the only Table that this example contains, are as shown here. The [TableSampleObservations_ID] field is an Autonumber field that serves as a primary key. [SampleNum] is a number from which the sample name is derived; e.g., [SampleNum] = 3 for the sample whose name is "Sample3". [ObservationValue] is the datum to be displayed; I didn't restrict it to integers, as in the original example, but it could easily be limited that way. [TableSampleObservations] Table Datasheet View: TableSample SampleNum Observation Observation Observations_ID Date Value --------------- --------- ----------- ----------- -1495793276 2 12/1/2005 3 -1468273538 2 12/3/2005 5 -683632410 1 12/3/2005 5 -620604590 2 12/2/2005 4 119706792 3 12/2/2005 3.1 387895402 2 12/31/2005 6 456754287 2 12/1/2005 6.4 499238439 2 12/3/2005 2.1 844422197 2 12/1/2005 7 1107219629 2 12/31/2005 3 1210703523 3 12/4/2005 4 1355043179 2 12/2/2005 7 1469899153 2 12/3/2005 3 1507253980 2 12/31/2005 5 1602234521 1 12/1/2005 2 These data get displayed on the Form in an array looking like this. I omitted any dates between 12/4 and 12/31 for brevity, but any additional dates in the Table will automatically generate corresponding columns in this Form. For example, the first record, -1495793276, is a "Sample2" on 12/1 with a value of 3. It appears on the form as the first of 3 "Sample2" values in the "12/01" column. If the Table contained four "Sample2" records for some one date, then "Sample2" would be listed (at least) four times in this Form. [F_Observations_Crosstab] Form View: +-------------------------------------------------+ | Description 12/01 12/02 12/03 12/04 12/31 | | ----------- ----- ----- ----- ----- ----- | | Sample1 2 5 | | Sample2 3 4 5 6 | | Sample2 6.4 7 2.1 3 | | Sample2 7 3 5 | | Sample3 3.1 4 | +-------------------------------------------------+ Clicking on any of these numbers brings up a dialog box asking for a value to which that number should be changed, and if you enter a number, the value in the corresponding record in the [TableSampleObservations] Table is set to that value, but the rest of the fields ([TableSampleObservations].[ObservationDate], for example) are left unchanged. Details on how this is accomplished follow. Obviously, there are other ways to do this, but I thought that what I show here should be not too difficult to follow, though it's sort of long and a bit tedious to read. Sorry. First step was to generate a sequence number that could serve to distinguish among multiple samples on the same date. This Query does that: [Q_Observations] SQL: SELECT TSO1.TableSampleObservations_ID, TSO1.ObservationDate, "Sample" & TSO1!SampleNum AS Description, Count(TSO2.TableSampleObservations_ID) AS Seq, TSO1.ObservationValue FROM TableSampleObservations AS TSO1 INNER JOIN TableSampleObservations AS TSO2 ON (TSO1.SampleNum = TSO2.SampleNum) AND (TSO1.ObservationDate = TSO2.ObservationDate) WHERE (((TSO2.TableSampleObservations_ID) =[TSO1]![TableSampleObservations_ID])) GROUP BY TSO1.TableSampleObservations_ID, TSO1.ObservationDate, TSO1.ObservationValue, TSO1.TableSampleObservations_ID, TSO1!SampleNum, TSO2.SampleNum ORDER BY TSO1.ObservationDate, TSO1!SampleNum, Count(TSO2.TableSampleObservations_ID); This displays pretty much the same information as [TableSampleObservations] in Table Datasheet View, except that a new field, [Seq], appears here, and the records are sorted by date, sample number, and sequence. [Q_Observations] Query Datasheet View: TableSample Observation Description Seq Observation Observations_ID Date Value --------------- ----------- ----------- --- ----------- 1602234521 12/1/2005 Sample1 1 2 -1495793276 12/1/2005 Sample2 1 3 456754287 12/1/2005 Sample2 2 6.4 844422197 12/1/2005 Sample2 3 7 -620604590 12/2/2005 Sample2 1 4 1355043179 12/2/2005 Sample2 2 7 119706792 12/2/2005 Sample3 1 3.1 -683632410 12/3/2005 Sample1 1 5 -1468273538 12/3/2005 Sample2 1 5 499238439 12/3/2005 Sample2 2 2.1 1469899153 12/3/2005 Sample2 3 3 1210703523 12/4/2005 Sample3 1 4 387895402 12/31/2005 Sample2 1 6 1107219629 12/31/2005 Sample2 2 3 1507253980 12/31/2005 Sample2 3 5 Having generated the basic information, we now display them in (roughly) the same format that will appear on the Form, as a Crosstab. We could have stopped here, except that the Crosstab wouldn't be editable; you'd have to do any editing on the underlying Table, maybe using a separate Form. [Q_Observations_Crosstab] SQL: TRANSFORM First(QOb.ObservationValue) AS SampleValue SELECT QOb.Description, QOb.Seq FROM Q_Observations AS QOb GROUP BY QOb.Description, QOb.Seq PIVOT Format([ObservationDate],"mm/dd"); The results look like this; notice that the sample value of 3 cited above appears in the correct place in the first "Sample2" row of column "12/01". [Q_Observations_Crosstab] Query Datasheet View: Description Seq 12/01 12/02 12/03 12/04 12/31 ----------- --- ----- ----- ----- ----- ----- Sample1 1 2 5 Sample2 1 3 4 5 6 Sample2 2 6.4 7 2.1 3 Sample2 3 7 3 5 Sample3 1 3.1 4 To make it easy to distinguish among values appearing on the Form, I chose to identify them via their key values instead of the [TableSampleObservations].[ObservationValue] field. I used a separate Query to do that: [Q_Observations_Xtab_IDs] SQL: TRANSFORM First(QOb.TableSampleObservations_ID) AS SampleValue SELECT QOb.Description, QOb.Seq FROM Q_Observations AS QOb INNER JOIN TableSampleObservations AS TSO ON QOb.TableSampleObservations_ID = TSO.TableSampleObservations_ID GROUP BY QOb.Description, QOb.Seq PIVOT Format([TSO]![ObservationDate],"mm/dd"); This Query is similar to the previous one, except that it displays key values instead of observation data. This is not the sort of thing that you would normally want users to look at, but this Query's output will be read by a VBA procedure, not by a human being. I'd probably choose to hide this Query if I were delivering the database to someone else. (Hidden Queries can be used, they just don't necessarily appear in the Database Window.) This isn't pretty, but it's useful. [Q_Observations_Xtab_IDs] Query Datasheet View: Descrip Seq 12/01 12/02 12/03 12/04 12/31 tion ------- --- ----------- ---------- ----------- --------- ---------- Sample1 1 1602234521 -683632410 Sample2 1 -1495793276 -620604590 -1468273538 387895402 Sample2 2 456754287 1355043179 499238439 1107219629 Sample2 3 844422197 1469899153 1507253980 Sample3 1 119706792 1210703523 I also need easy access to the Table. Rather than addressing the Table directly, I define another Query to allow me to update the Table. [Q_TableSampleObservations] SQL: SELECT TSO.TableSampleObservations_ID, TSO.ObservationValue FROM TableSampleObservations AS TSO; There's not much to this; it simply selects from the Table the two fields that interest me. [Q_TableSampleObservations] Query Datasheet View: TableSample Observation Observations_ID Value --------------- ----------- -1495793276 3 -1468273538 5 -683632410 5 -620604590 4 119706792 3.1 387895402 6 456754287 6.4 499238439 2.1 844422197 7 1107219629 3 1210703523 4 1355043179 7 1469899153 3 1507253980 5 1602234521 2 Now, the VBA code that consumes the results of these Queries is as shown here. The only objects involved are a Form named [F_Observations_Crosstab] and the spreadsheet object named [sshXtab] that occupies the Form Header. Since the Form is not attached to a recordset, there is no need for navigation controls, or for that matter even for a Form Detail section; the Form just serves as a convenient container for the Spreadsheet object. There are three procedures in this code. - sshXtab_Click identifies the record in [TableSampleObservations] corresponding to a spreadsheet cell that is clicked, and it updates the value in the record if the user enters a new value. - UpdateSpreadsheet refreshes the values displayed on the Form. - SetUpRecordset reads the output of a Query into a recordset. For the sake of brevity, I omitted error-checking code, leaving it as an exercise for the reader. (What do you mean, it's not brief? You should see the long form.) Errors might still occur, but if so the result will be that the request will fail, and you'll see a brief message in a dialog box. [F_Observations_Crosstab] VBA code: Option Compare Database Option Explicit '----------------------------------------------------- 'Clicking on a cell containing a number (observation ' value) brings up a dialog box asking for a possible ' change to the value. The user may cancel, but if ' he enters a numeric value, that new value replaces ' the previous one, and the spreadsheet is updated. ' Private Sub sshXtab_Click( _ ByVal EventInfo As Object) Dim sshActive As OWC.Spreadsheet 'OWC.Spreadsheet Dim intCol As Integer 'Column number on spreadsheet Dim rstFiltered As DAO.Recordset 'Selected row 'of rstRecordIDs Dim rstID_Record As Recordset 'Record from '[Q_TableSampleObservations] 'with [TableSampleObservations_ID] 'matching selected key value Dim strNewValue As String 'New value for selected cell Dim rstRecordIDs As DAO.Recordset 'Query [Q_Observations_Xtab_IDs] 'Fields: ' Description, Seq, ID_1, ID_2, ... Dim intRow As Integer 'Row number on spreadsheet Dim varSelectedID As Variant 'Key value for selected 'cell, or null Dim rstSelection As DAO.Recordset 'Query [Q_TableSampleObservations] 'Fields: ' ID value, observation value 'Populate the spreadsheet with the results of the ' Crosstab Query [Q_Observations_Crosstab] Set sshActive = sshXtab.Object 'Spreadsheet on Form UpdateSpreadsheet sshSpreadsheet:=sshActive SetUpRecordset rstThisRecordset:=rstRecordIDs, _ strQueryName:="Q_Observations_Xtab_IDs" 'Description, Seq, ID_1, ID_2, ... SetUpRecordset rstThisRecordset:=rstSelection, _ strQueryName:="Q_TableSampleObservations" 'ID value, observation value 'rstSelection.Requery intRow = sshActive.ActiveCell.Row intCol = sshActive.ActiveCell.Column rstRecordIDs.MoveLast 'Be sure we've counted all 'Allow the user to click only in the part of the ' spreadsheet where there are values, not in the ' 1st row or column, or beyond the edges If intCol 3 _ Or intCol rstRecordIDs.Fields.Count _ Or intRow 2 _ Or intRow rstRecordIDs.RecordCount + 1 Then MsgBox "Please click on one of the values.", _ , "sshXtab_Click" GoTo CleanUpAndExit: End If 'intCol ... 'Find the (unique) record with the matching ' description (e.g., "Sample2") ' and sequence(e.g., 3). rstRecordIDs.Filter = _ "Description = '" _ & sshActive.Cells(intRow, 1) _ & "' AND Seq = " _ & sshActive.Cells(intRow, 2) 'This recordset should contain just the 1 record ' containing ID numbers for observation values ' for the selected Description and Sequence Set rstFiltered = rstRecordIDs.OpenRecordset varSelectedID = rstFiltered.Fields _ (intCol - 1).Value 'Of course, since not all cells contain values, ' the user could have clicked on an empty one. ' If so, we don't allow it -- pop up a message. If IsNull(varSelectedID) Then MsgBox "Please click on an existing value.", _ , "sshXtab_Click" GoTo CleanUpAndExit: End If 'IsNull( ... 'Now locate the one record in Table ' [TableSampleObservations] that has the matching ' ID value (key), and which we may want to edit rstSelection.Filter = _ "TableSampleObservations_ID = " _ & varSelectedID Set rstID_Record = rstSelection.OpenRecordset 'Ask the user for the new value to be used for ' the observation in the clicked-on cell ' (if none, just exit without doing anything) strNewValue = InputBox( _ "What value do you want to use here, " _ & vbCrLf & " instead of " _ & rstID_Record.Fields(1) & "?", _ "New value", "") If IsNumeric(strNewValue) Then rstID_Record.Edit 'User specified a new value, so store it into ' the Table instead of the previous one rstID_Record.Fields("ObservationValue") _ = strNewValue rstID_Record.Update 'Re-populate the spreadsheet with the new (changed) ' results of [Q_Observations_Crosstab] UpdateSpreadsheet sshSpreadsheet:=sshActive End If 'IsNumeric( ... CleanUpAndExit: Set sshActive = Nothing Set rstFiltered = Nothing Set rstID_Record = Nothing End Sub 'sshXtab_Click '----------------------------------------------------- 'Display the current values from [Q_Observations_Crosstab] ' in the spreadsheet displayed on the Form. ' Private Sub UpdateSpreadsheet( _ ByRef sshSpreadsheet As OWC.Spreadsheet) 'ByRef sshSpreadsheet As OWC.Spreadsheet 'Spreadsheet ' displayed by Form Dim intCol As Integer 'Column number on spreadsheet Dim intRow As Integer 'Row number on spreadsheet Dim varValue As Variant 'Value to place into a cell Dim rstXtab As DAO.Recordset '"Q_Observations_Crosstab" ' recordset 'Fields: _ ' Description, Seq, 12/1, 12/2, ... SetUpRecordset rstThisRecordset:=rstXtab, _ strQueryName:="Q_Observations_Crosstab" 'Description, Seq, 12/1, 12/2, ... With sshSpreadsheet .Cells.ClearContents .Columns(2).Hidden = True 'Hide the [Seq] field 'Use the first row to label the columns For intCol = 1 To rstXtab.Fields.Count 'Prefix this with apostrophe to force ' Excel/OWC not to reformat the dates .Cells(1, intCol) = "'" _ & rstXtab.Fields(intCol - 1).Name Next intCol rstXtab.MoveFirst intRow = 2 While Not rstXtab.EOF For intCol = 1 To rstXtab.Fields.Count varValue = rstXtab.Fields(intCol - 1).Value If Not IsNull(varValue) Then _ .Cells(intRow, intCol).Value _ = varValue Next intCol intRow = intRow + 1 rstXtab.MoveNext Wend 'Not rstXtab.EOF End With 'sshSpreadsheet End Sub 'UpdateSpreadsheet '----------------------------------------------------- 'This defines (rstThisRecordset) to be the records ' returned by the named Query. ' Private Sub SetUpRecordset( _ ByRef rstThisRecordset As DAO.Recordset, _ ByVal strQueryName As String) 'ByRef rstThisRecordset As DAO.Recordset 'Set of records ' returned by the named Query 'ByVal strQueryName As String 'Name of Query that will ' populate the recordset Dim qdfTemp As DAO.QueryDef 'Specified Query definition Set qdfTemp = CurrentDb.QueryDefs( _ strQueryName) Set rstThisRecordset = qdfTemp.OpenRecordset End Sub 'SetUpRecordset '=================== Well, that's about it. This should give you a display similar to the one in the original request, but editable (after a fashion) and based on a Table in which each record describes just one datum, making other types of analysis easy to perform. Good luck. -- Vincent Johns Please feel free to quote anything I say here. Ed Warren wrote: Your data table probably should look like: TableSamples: SampleID (key) [1,2,3,4,5,6] SampleText [Sample1, Sample2, Sample3...] TableSampleObservations: ObservationID (Key) [1,2,3,4,5,.....n] SampleID (Foreign Key) --refers to the TableSamples [1,2,3,4....] ObservationDate (date/time) [12/1/2005, 12/2/2005, 12/3/2005....] ObservationValue (Number: Integer) [3,4,5,6,6,7,....] Now the question is how to build a form in the format desired then maintain the data in the format above. This will require some VBA coding . Now that we have a better statement of the problem, there may be an Access Wizard out here somewhere that can provide better advice than I can. I would build some temp tables with temp1: ColumnNum: ColumnDate: fill with rows [1,12/1/2005; 2,12/2/2005; 3,12/3/2005.... N, 12/N/2005) I would use this as a map to go from a column to a date. this way you can change the dates as required to map to any date in any month Temp2: columns (col1... 31 as required for the month of interest) rows: for each sample mapped to the appropriate dates then build a form based on the temp table then map any changes, additions, deletions back to the basic tables. You might also look for some code that will allow you to 'transpose rows and columns' Ed Warren. "Fehn" wrote in message ... Thanks Ed! Case A is what i want to see.... What will happen if there there is another date?? Because what I am trying to do is a form that is updatable and will be seen like this Case A Description 12/1/2005 12/2/2005 12/3/2005 .... 12/31/2005 Sample2 3 4 5 .... 6 Sample2 6 7 2 .... 3 Sample2 7 6 3 .... 5 DO you have any suggestion on this? Thanks again. "Ed Warren" wrote: My first thought was to take my non-MVP ideas and stay home, but what fun would that be? In Duane's solution: you may want to consider what happens when you (or others) manage to enter more than one case of Sample2 on the same date. ----------------------------------- data structure (Duane) SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue ------------------------------------------ (maybe not if you strictly use Duane's input screen for all data input) Nothing from a data structure perspective prevents this. This could easily be fixed by setting a unique index to the SampleTypeID/date pair. SampleID SampleTypeID SampleDate SampleValue 1 2 12/1/2005 3 2 2 12/1/2005 6 3 2 12/1/2005 7 ---------------------------------------------- Now what do you want to see? Case A Description 12/1/2005 Sample2 3 Sample2 6 Sample2 7 --------------------------- [...] Ed Warren. "Fehn" wrote in message ... Thanks Duane! That is what I want, I want it to be updatable... I will try other methods that other MVP's suggested.... Thanks again..... "Duane Hookom" wrote: A pivot or crosstab was my first thought also. However, Fehn possibly wants a solution that is "updatable". The pivot and crosstab work great for presenting the data from a normalized table but unfortunately they are not updateable. -- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message . .. Your example looks a lot like a pivot table; some grouping at the top and left with some data in the center of the table. You just might be able to get to where you want to be using a clever query and then displaying it in a pivot table. Ed Warren. "Fehn" wrote in message ... Does anyone know how to make this possible and updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : etc. Thanks. |
#52
|
|||
|
|||
Calendar and time sheet
Vincent! you are a genius!
I havent tried your procedure, but by reading it, it looks its going to work.... I will definitely let you know if it works, I will try your procedure when I have my free time in the office. Is it OK for me to contact you on your personnal emial? Thanks again! "Vincent Johns" wrote: Having become mightily annoyed at PC Datasheet's boneheaded advertising, I did complain about him, but now I kinda miss him, haven't seen him for a while now. I suppose his helpful comments never came without the spammy advertisements... :-( Anyway, having played around with Fehn's request a bit (and writing some VBA code), I came up with what I think is a way to do it without a heck of a lot of trouble. (But, as Ed Warren suggested, I didn't see an obvious way to do it without VBA.) My first thought was to populate a Form with a bunch of little list boxes, one per datum, but I quickly abandoned that -- each one needed a separate name, and the number would be fixed. We want a set that would adjust automatically to the number of columns resulting from the data set. So, I plugged in a Microsoft Excel spreadsheet (Microsoft Office Web Components 9.0) in MS Access 2000 (had to change it to Microsoft Office Web Components 11.0 in Access 2003), and got good results. This is an object with OLE Class = "Microsoft Office Spreadsheet 9.0" and Class = "OWC.Spreadsheet.9". To use it, you can create a blank Form and paste a Microsoft Office Spreadsheet 9.0 object onto the Form Header section, since you won't have any underlying records. On my Form, I opened the Properties sheet in Form Design View and turned off features I didn't need, such as navigation controls, scroll bars, the spreadsheet's Tool Bar and Title Bar, etc. (In the Access 2003 version, Microsoft Office Spreadsheet 11.0, I wasn't able to hide the column headers such as "A", "B", &c.) In addition to pasting the object into the blank Form, you'll also need to set references in VBA to the objects, specifically to (in Access 2000) "Microsoft Office Web Components 9.0" (found in Office\MSOWC.DLL) and "Microsoft DAO 3.6 Object Library" (found in Microsoft Shared\DAO\dao3). The OWC lets you play with the spreadsheet, and the DAO lets you read & write your database Tables. (Well, actually, there's only one Table in this example.) A nice feature of the OWC Spreadsheet is that all of the cells in it already have names (you don't need to assign any), and there are probably more of them than you'll ever need. There were certainly more than enough for my version of your example. Since some of the questions weren't really answered here, such as exactly HOW one is intended to edit the editable values, I made some simplifying assumptions. The data are displayed as in Ed's "Case A" layout, but if you try to edit one by clicking on it, a dialog box pops up asking for the new value, and you can either enter one or cancel the request. I made no provisions for adding new data nor for zapping existing ones. Those functions would be easy to add, but you'd need to decide how you wanted them to work. For example, you could allow a new value to be entered into a spreadsheet cell, and the "On Update" Event could do what my pop-up dialog box does. But I omitted stuff like that because it would make a long example even longer and not add much in the way of clarity. I figured an InputBox() is about as easy as anything is to program, so that's what I used. OK... now to the specific example. The raw data, in the only Table that this example contains, are as shown here. The [TableSampleObservations_ID] field is an Autonumber field that serves as a primary key. [SampleNum] is a number from which the sample name is derived; e.g., [SampleNum] = 3 for the sample whose name is "Sample3". [ObservationValue] is the datum to be displayed; I didn't restrict it to integers, as in the original example, but it could easily be limited that way. [TableSampleObservations] Table Datasheet View: TableSample SampleNum Observation Observation Observations_ID Date Value --------------- --------- ----------- ----------- -1495793276 2 12/1/2005 3 -1468273538 2 12/3/2005 5 -683632410 1 12/3/2005 5 -620604590 2 12/2/2005 4 119706792 3 12/2/2005 3.1 387895402 2 12/31/2005 6 456754287 2 12/1/2005 6.4 499238439 2 12/3/2005 2.1 844422197 2 12/1/2005 7 1107219629 2 12/31/2005 3 1210703523 3 12/4/2005 4 1355043179 2 12/2/2005 7 1469899153 2 12/3/2005 3 1507253980 2 12/31/2005 5 1602234521 1 12/1/2005 2 These data get displayed on the Form in an array looking like this. I omitted any dates between 12/4 and 12/31 for brevity, but any additional dates in the Table will automatically generate corresponding columns in this Form. For example, the first record, -1495793276, is a "Sample2" on 12/1 with a value of 3. It appears on the form as the first of 3 "Sample2" values in the "12/01" column. If the Table contained four "Sample2" records for some one date, then "Sample2" would be listed (at least) four times in this Form. [F_Observations_Crosstab] Form View: +-------------------------------------------------+ | Description 12/01 12/02 12/03 12/04 12/31 | | ----------- ----- ----- ----- ----- ----- | | Sample1 2 5 | | Sample2 3 4 5 6 | | Sample2 6.4 7 2.1 3 | | Sample2 7 3 5 | | Sample3 3.1 4 | +-------------------------------------------------+ Clicking on any of these numbers brings up a dialog box asking for a value to which that number should be changed, and if you enter a number, the value in the corresponding record in the [TableSampleObservations] Table is set to that value, but the rest of the fields ([TableSampleObservations].[ObservationDate], for example) are left unchanged. Details on how this is accomplished follow. Obviously, there are other ways to do this, but I thought that what I show here should be not too difficult to follow, though it's sort of long and a bit tedious to read. Sorry. First step was to generate a sequence number that could serve to distinguish among multiple samples on the same date. This Query does that: [Q_Observations] SQL: SELECT TSO1.TableSampleObservations_ID, TSO1.ObservationDate, "Sample" & TSO1!SampleNum AS Description, Count(TSO2.TableSampleObservations_ID) AS Seq, TSO1.ObservationValue FROM TableSampleObservations AS TSO1 INNER JOIN TableSampleObservations AS TSO2 ON (TSO1.SampleNum = TSO2.SampleNum) AND (TSO1.ObservationDate = TSO2.ObservationDate) WHERE (((TSO2.TableSampleObservations_ID) =[TSO1]![TableSampleObservations_ID])) GROUP BY TSO1.TableSampleObservations_ID, TSO1.ObservationDate, TSO1.ObservationValue, TSO1.TableSampleObservations_ID, TSO1!SampleNum, TSO2.SampleNum ORDER BY TSO1.ObservationDate, TSO1!SampleNum, Count(TSO2.TableSampleObservations_ID); This displays pretty much the same information as [TableSampleObservations] in Table Datasheet View, except that a new field, [Seq], appears here, and the records are sorted by date, sample number, and sequence. [Q_Observations] Query Datasheet View: TableSample Observation Description Seq Observation Observations_ID Date Value --------------- ----------- ----------- --- ----------- 1602234521 12/1/2005 Sample1 1 2 -1495793276 12/1/2005 Sample2 1 3 456754287 12/1/2005 Sample2 2 6.4 844422197 12/1/2005 Sample2 3 7 -620604590 12/2/2005 Sample2 1 4 1355043179 12/2/2005 Sample2 2 7 119706792 12/2/2005 Sample3 1 3.1 -683632410 12/3/2005 Sample1 1 5 -1468273538 12/3/2005 Sample2 1 5 499238439 12/3/2005 Sample2 2 2.1 1469899153 12/3/2005 Sample2 3 3 1210703523 12/4/2005 Sample3 1 4 387895402 12/31/2005 Sample2 1 6 1107219629 12/31/2005 Sample2 2 3 1507253980 12/31/2005 Sample2 3 5 Having generated the basic information, we now display them in (roughly) the same format that will appear on the Form, as a Crosstab. We could have stopped here, except that the Crosstab wouldn't be editable; you'd have to do any editing on the underlying Table, maybe using a separate Form. [Q_Observations_Crosstab] SQL: TRANSFORM First(QOb.ObservationValue) AS SampleValue SELECT QOb.Description, QOb.Seq FROM Q_Observations AS QOb GROUP BY QOb.Description, QOb.Seq PIVOT Format([ObservationDate],"mm/dd"); The results look like this; notice that the sample value of 3 cited above appears in the correct place in the first "Sample2" row of column "12/01". [Q_Observations_Crosstab] Query Datasheet View: Description Seq 12/01 12/02 12/03 12/04 12/31 ----------- --- ----- ----- ----- ----- ----- Sample1 1 2 5 Sample2 1 3 4 5 6 Sample2 2 6.4 7 2.1 3 Sample2 3 7 3 5 Sample3 1 3.1 4 To make it easy to distinguish among values appearing on the Form, I chose to identify them via their key values instead of the [TableSampleObservations].[ObservationValue] field. I used a separate Query to do that: [Q_Observations_Xtab_IDs] SQL: TRANSFORM First(QOb.TableSampleObservations_ID) AS SampleValue SELECT QOb.Description, QOb.Seq FROM Q_Observations AS QOb INNER JOIN TableSampleObservations AS TSO ON QOb.TableSampleObservations_ID = TSO.TableSampleObservations_ID GROUP BY QOb.Description, QOb.Seq PIVOT Format([TSO]![ObservationDate],"mm/dd"); This Query is similar to the previous one, except that it displays key values instead of observation data. This is not the sort of thing that you would normally want users to look at, but this Query's output will be read by a VBA procedure, not by a human being. I'd probably choose to hide this Query if I were delivering the database to someone else. (Hidden Queries can be used, they just don't necessarily appear in the Database Window.) This isn't pretty, but it's useful. [Q_Observations_Xtab_IDs] Query Datasheet View: Descrip Seq 12/01 12/02 12/03 12/04 12/31 tion ------- --- ----------- ---------- ----------- --------- ---------- Sample1 1 1602234521 -683632410 Sample2 1 -1495793276 -620604590 -1468273538 387895402 Sample2 2 456754287 1355043179 499238439 1107219629 Sample2 3 844422197 1469899153 1507253980 Sample3 1 119706792 1210703523 I also need easy access to the Table. Rather than addressing the Table directly, I define another Query to allow me to update the Table. [Q_TableSampleObservations] SQL: SELECT TSO.TableSampleObservations_ID, TSO.ObservationValue FROM TableSampleObservations AS TSO; There's not much to this; it simply selects from the Table the two fields that interest me. [Q_TableSampleObservations] Query Datasheet View: TableSample Observation Observations_ID Value --------------- ----------- -1495793276 3 -1468273538 5 -683632410 5 -620604590 4 119706792 3.1 387895402 6 456754287 6.4 499238439 2.1 844422197 7 1107219629 3 1210703523 4 1355043179 7 1469899153 3 1507253980 5 1602234521 2 Now, the VBA code that consumes the results of these Queries is as shown here. The only objects involved are a Form named [F_Observations_Crosstab] and the spreadsheet object named [sshXtab] that occupies the Form Header. Since the Form is not attached to a recordset, there is no need for navigation controls, or for that matter even for a Form Detail section; the Form just serves as a convenient container for the Spreadsheet object. There are three procedures in this code. - sshXtab_Click identifies the record in [TableSampleObservations] corresponding to a spreadsheet cell that is clicked, and it updates the value in the record if the user enters a new value. - UpdateSpreadsheet refreshes the values displayed on the Form. - SetUpRecordset reads the output of a Query into a recordset. For the sake of brevity, I omitted error-checking code, leaving it as an exercise for the reader. (What do you mean, it's not brief? You should see the long form.) Errors might still occur, but if so the result will be that the request will fail, and you'll see a brief message in a dialog box. [F_Observations_Crosstab] VBA code: Option Compare Database Option Explicit '----------------------------------------------------- 'Clicking on a cell containing a number (observation ' value) brings up a dialog box asking for a possible ' change to the value. The user may cancel, but if ' he enters a numeric value, that new value replaces ' the previous one, and the spreadsheet is updated. ' Private Sub sshXtab_Click( _ |
#53
|
|||
|
|||
Calendar and time sheet
Fehn wrote:
Vincent! you are a genius! I havent tried your procedure, but by reading it, it looks its going to work.... I will definitely let you know if it works, I will try your procedure when I have my free time in the office. Is it OK for me to contact you on your personnal emial? Thanks again! Yes, it's perfectly OK to send me email, but I may not answer right away -- you might have to wait a couple of days. Postings to Usenet might be answered (by someone) in an hour or two. -- Vincent Johns Please feel free to quote anything I say here. |
#54
|
|||
|
|||
Calendar and time sheet
Wow! What an exciting and informative thread! This is just exactly what I
would like to accomplish with my own database. However, the solution sounds a bit beyond me at the moment. I'll try and see... Jaybird "Vincent Johns" wrote: Fehn wrote: Vincent! you are a genius! I havent tried your procedure, but by reading it, it looks its going to work.... I will definitely let you know if it works, I will try your procedure when I have my free time in the office. Is it OK for me to contact you on your personnal emial? Thanks again! Yes, it's perfectly OK to send me email, but I may not answer right away -- you might have to wait a couple of days. Postings to Usenet might be answered (by someone) in an hour or two. -- Vincent Johns Please feel free to quote anything I say here. |
#55
|
|||
|
|||
Calendar and time sheet
I would like to add something. I use this newsgroup from time to time and
have always been really amazed by the fact that all these guys are happy to help anyone from the newest newbie to an expert. Even though sometimes the requests are long winded or very vague, they still try their best, even those who perhaps aren't experts themselves still try and point people in the right direction. It seems to me that anyone trying to tout for business in a forum especially made for people to help other people for free just seems wrong. And when others mention this then those doing the touting just get aggressive and offensive. I have no doubt, i'll receive an offensive, aggressive reply to this post but i think that just proves the point I, and many others are trying to make. Perhaps someone could find out if it is possible to bar people doing this, i.e. by barring posts with their email or websites in?!? Thanks again to all the people who have given up their time and expertise over the years to help me with my queries, you are all stars in my eyes! "PC Datasheet" wrote: This is totally untrue!! These people are class acts because they spend their time helping posters in the newsgroups and not polluting the newsgroups with garbage and taking up volumes of bandwidth in the newsgroup servers. It has nothing with their feelings about me. For anyone reading this, just take a look at how many real contributions the following dimwits have made to the newsgroups: Arno R, John Marshall, Randy Harris and Keith Wilby. Arno R would rather spend his time sending emails to my office. John Marshall would rather spend his time showing how dimwitted he is. Randy Harris would rather spend his time being a puppet of Arno R and Keith Wilby would rather spend his time showing how two-faced he is. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Randy Harris" wrote in message ... "PC Datasheet" wrote in message ink.net... Duane, You continue to demonstrate that you are not even in the league with class acts like Allen Browne, Ken Snell, Pieter Linden, Albert Kallal, Fred G, Graham Mandello, Chuck Grimsby, MG Foster, and others. -- PC Datasheet The way that Steve figures it, anyone that doesn't object to his Datasheet spam is a class act. |
#56
|
|||
|
|||
Calendar and time sheet
One other thing......is the only word you know 'dimwit'...just wondering
"PC Datasheet" wrote: Duane, Just take a cue from the people who are class acts and quit supporting and promoting Arno R and his band of dimwits. It's that simple. It's not complicated. It's not difficult. You are not impressing anyone siding with Arno R and his band of dimwits. You don't see these class act people siding with Arno R. They adhere to common netiquette rules. Whatever their feelings are, they stick to helping people in the newsgroups. There's an old saying, "birds of a feather flock together". Everyone is seeing that you are just like Arno R and his band of dimwits. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... Steve, Just start behaving like everyone else. It's that simple. It's not complicated. It's not difficult. Everyone else understands this. Everyone else is very good at complying. Everyone else sticks fairly close to common netiquette rules. -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message ink.net... Duane, You continue to demonstrate that you are not even in the league with class acts like Allen Browne, Ken Snell, Pieter Linden, Albert Kallal, Fred G, Graham Mandello, Chuck Grimsby, MG Foster, and others. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... I would keep all the data in normalized tables (and ignore or verbally abuse PC Datasheet). tblSamples ================ SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue If you really need a form like this, consider -writing code that loads your data into a "flat" table for editing more code would be needed to normalize after editing -create an unbound form like a grid of text boxes. use code to fill the grid with data and then to save the data to your normalized table I wrote some code for another poster a while back the filled a bunch of text boxes with customer names and order dates from Northwind. One of the keys was to use a scheme for naming the text boxes as below: txtCust1 txtOrdDate1_1 txtOrdDate1_2 ...etc... txtCust2 txtOrdDate2_1 txtOrdDate2_2 ...etc... txtCust3 txtOrdDate3_1 txtOrdDate3_2 ...etc... ...etc... ...etc... ...etc... ...etc... The code to fill the text boxes with customers and order dates: Private Sub cmdPullOrderDates_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim intCustomer As Integer Dim strCustomer As String Dim intOrder As Integer strSQL = "SELECT CompanyName, OrderDate " & _ "FROM Customers INNER JOIN " & _ "Orders ON Customers.CustomerID = Orders.CustomerID " & _ "ORDER BY CompanyName, OrderDate" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) rs.MoveFirst With rs intCustomer = 0 Do Until .EOF Or intCustomer 2 strCustomer = .Fields("CompanyName") intCustomer = intCustomer + 1 Me("txtCust" & intCustomer) = strCustomer intOrder = 0 Do Until strCustomer .Fields("CompanyName") Or intOrder 4 intOrder = intOrder + 1 Me("txtOrdDate" & intCustomer & "_" & intOrder) = .Fields("OrderDate") .MoveNext Loop Loop .Close End With Set rs = Nothing Set db = Nothing End Sub -- Duane Hookom MS Access MVP "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I fill a time sheet from a calendar | PeterM | General Discussion | 6 | November 29th, 2005 09:24 PM |
Time Zone suggestions for Outlook Calendar | Ben Knox | Calendar | 2 | October 20th, 2005 03:42 PM |
Calendar Time | Linn Allen | Calendar | 1 | September 20th, 2005 10:04 PM |
Calendar should allow scheduling appointments in any time zone | Art H | Calendar | 1 | August 19th, 2005 11:25 PM |
Access Calendar | lost | General Discussion | 2 | July 7th, 2004 04:58 AM |