A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calendar and time sheet



 
 
Thread Tools Display Modes
  #51  
Old December 26th, 2005, 12:01 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old January 4th, 2006, 12:13 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old January 4th, 2006, 12:18 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old March 2nd, 2006, 04:26 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old April 20th, 2006, 01:37 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old April 20th, 2006, 01:40 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 10: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


All times are GMT +1. The time now is 10:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.