R
Rick
The table is JobsInProgAssign containing the following fields
JIPAssignID PK autonumber
JIPID Long Interger (lookup to JobsInProgress table)
StaffID Long Interger (lookup to Staff table)
WeekID Long Interger (lookup to Weeks table)
HourBudget Long Integer
HourLeft Long Interger
HourActual Long Integer
LastChangeBy Long Integer (lookup to Staff table)
LastChangeOn Date
Principle is a job is set up in the master JobsInProgess table. There is a
one to many link from JobsInProgess table to JobsInProgAssign to enable
recording of several staff members working on the same job over a period of
several weeks.
The existing Pivot Table I have written is based on the JobsInProgAssignqry
which pulls info from the JobsInProgess, JobsInProgAssign,Staff and Weeks
tables.
Hope this helps.
JIPAssignID PK autonumber
JIPID Long Interger (lookup to JobsInProgress table)
StaffID Long Interger (lookup to Staff table)
WeekID Long Interger (lookup to Weeks table)
HourBudget Long Integer
HourLeft Long Interger
HourActual Long Integer
LastChangeBy Long Integer (lookup to Staff table)
LastChangeOn Date
Principle is a job is set up in the master JobsInProgess table. There is a
one to many link from JobsInProgess table to JobsInProgAssign to enable
recording of several staff members working on the same job over a period of
several weeks.
The existing Pivot Table I have written is based on the JobsInProgAssignqry
which pulls info from the JobsInProgess, JobsInProgAssign,Staff and Weeks
tables.
Hope this helps.
Rob Oldfield said:Good.
Which table(s) do 'Hours left' and 'Date in' come from?
Rick said:Corrected my naming convention and the code generates the ReportBasis table
as follows:
Staff 34 35 36
AJB XYZ Company Brown's Oil Co. Pictures Framed Ltd.
KAR All About Sails Willow Candies
MAJ Hardcastle Const Super Stores Fine Furniture Refinishing
SST Barns Pet Store Ace Towing Co.
Appear to be getting there.
However; require two other pieces of data in addition to the JIPAssignment
to be in the ReportBasis table for the final report. Require Hours left and
the Date In.
Have added the fields to the ReportData query and confirmed their presence
when running the query. How do I get them into the ReportBasis table?
Rick said:Opps! Think I might have discovered the problem. Please stand by.
:
Yes, exactly as copied from code window:
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
:
That's...
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
....correct?
Sorry, yes, the comment marker was dropped when pasting the code.
Ran code again with the following error in Private Sub SetupData() :
Run-time error 3265 Item not found in collection
Debug highlites line "rsoutput.Fields(SCtr(rs!WeekID) = rsjipassignment"
Tried commenting that line out and the code runs to same line as above
written after Else statement.
:
Which text does it highlight?
(If it's any of the text 'WeekID field' then that is supposed to be
part of
the comment on the previous line. You can just add a ' at the start of
the
line to comment it out.)
Code runs to the SetupData query line WeekId Field and returns a
compile
error "Invalid Use of Property" :
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but where
the
WeekID Field
'hasn't been completed yet
.FindFirst ("[Staff]='" + CStr(rs!StaffID) + _
"' and isnull([" + CStr(rs!WeekID) + "])")
:
You need a method of running the code. The best way to do this will
be
to
create a form (doesn't need to be based on any data) and add a
button to
it
(..use View, Toolbox and then drag a button to the form..) Then
click
on
the button and get the properties of it by View, Properties. On the
Event
tab click in the On Click event and click the three buttons on the
right.
You want the Code Builder option. That gives you an event that will
run
when you click the button.
Edit that so it says...
Private Sub Command0_Click()
DoCmd.SetWarnings False 'stops you getting warnings about queries
running
DoCmd.OpenQuery "StoreSchedData"
Call SetupData 'calls the subroutine to set the data up
DoCmd.SetWarnings True 'turns warning back on
End Sub
....below that add...
Private Sub SetupData()
....all the other stuff....
End Sub
And then you can just view the form and keep your fingers crossed.
Notes... you're right that you'll need to change a couple of things
because
the StaffPK is text...
DatesCT will now need to be...
TRANSFORM Max('x') AS Expr1
SELECT 'x' AS Staff
FROM Weeks
GROUP BY 'x'
PIVOT [Weeks].[WeekID];
StoreSchedDate...
SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>"x"));
Update a line in the SetupData routine to be...
..FindFirst ("[Staff]='" + CStr(rs!staffid) + _
"' and isnull([" + CStr(rs!WeekID) + "])")
Query DatesCT; query 2 which I have name StoreSchedData and query
ReportData
appear to function as expected.
ReportData query generates a table showing the WeekID; StaffID and
JIPAssignment.
However; I am confused about the code used to "take this dta and
push
it
into the ReportBasis table". Not sure if this code is supposed to
reside
in
yet another query or added to ReportData query. Attempted copying
the
code
into a new query and running it with the Access error "SQL Error -
expecting
INPUT, DELETE ...."
Guess you can tell by now I am far from an Access programmer.
Incidentally, I have noted an error in my original comments about
the
structure of the Staff table. The StaffID PK is not actually an
autonumber;
but rather a three letter index. Doesn't appear to affect your
suggestions
to date but the reference to the numeric 1 might have to be
altered.
:
Now how did I know you were going to say that?
Right. I've got most of it sorted out I think. Not quite
polished
off
but
let's see what you think so far. Again, this really is quite
nasty.
Your problem is, pretty much, that you're attempting to take a
normalised
set of data and push it back into a completely non-normalised
format.
Nothing wrong with that, but it does make things complicated.
I don't think there's any way of getting at what you're after
without
some
serious playing around with the data. It might be possible to
do it
by
using subreports but I think that looks at least as messy.
So...
First thing that I've done is set up a query that I've called
DatesCT.
SQL
is
TRANSFORM Max('x') AS Expr1 SELECT 1 AS Staff FROM Weeks GROUP
BY 1
PIVOT Weeks.WeekID;
....basically using a crosstab query to create a structure with
weeks as
column headings.
Next...another query that is a make table in order to
permanently
create
a
structure that I'm going to use to store the data...
SELECT DatesCT.* INTO ReportBasis
FROM DatesCT
WHERE (((DatesCT.Staff)<>1));
Note that I want the Staff field (which I really should have
called
StaffID
again) to be numeric (so that I can put StaffIDs into it) which
is
where
the
"1" in DatesCT comes from - to ensure that it comes out that
way.
Similarly, I want the data type of the 1, 2, 3 fields to be text
(so
that I
can drop the names of jobs into them) - hence the Max('x'). The
<>1
criterion is just so that the existing row doesn't get added to
the
new
table.
OK. Next bit. Take the normalised version of your data and
push it
into
this new table. To do this I have another query called
ReportData...
SELECT JobsInProgressAssigned.JIPAssignment, Weeks.WeekID,
Staff.StaffID
FROM Weeks INNER JOIN (Staff INNER JOIN JobsInProgressAssigned
ON
Staff.StaffID = JobsInProgressAssigned.JIPStaffID) ON
Weeks.WeekID =
JobsInProgressAssigned.JIPWeekID;
To take this data and push it into the ReportBasis table I've
needed
to
resort to code...
Private Sub SetupData()
Dim db As Database
Dim rs As DAO.Recordset
Dim rsoutput As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportData")
Set rsoutput = db.OpenRecordset("ReportBasis", dbOpenDynaset)
rs.MoveFirst
'Loop through source records
Do While Not rs.EOF
With rsoutput
'Check for a row that belongs to our staff member, but
where
the
WeekID field
'hasn't been completed yet
.FindFirst ("[Staff]=" + CStr(rs!staffid) + _
" and isnull([" + CStr(rs!WeekID) + "])")
'If there isn't one, then we need a new row
If .NoMatch Then
'and complete the information
rsoutput.AddNew
rsoutput!Staff = rs!staffid
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
Else
'otherwise, just fill in the blank
rsoutput.Edit
rsoutput.Fields(CStr(rs!WeekID)) = rs!jipassignment
rsoutput.Update
End If
End With
'and move to the next record
rs.MoveNext
Loop
rs.Close
rsoutput.Close
Set db = Nothing
End Sub
You could obviously apply some kind of date criteria to this
process
as
you
require.
You can now create a report based on this new table... but...
The need to keep the flexibility to print out the report for 52
weeks is
the
last complication, and the one that I haven't dug into in detail
as
yet.
Obviously, there's no way in the world you're going to be able
to
fit 52
weeks across a single piece of paper so...
My idea would be to create the report with the maximum number of
fields
across the page... say five. Given an outside parameter - i.e.
the
first
week number required in the report - then you can do something
like
this
in
the Open event of the report...
Me.[1_label].Caption = 'look up the outside number in the weeks
table to
figure out what the date should say
Me.[1].ControlSource = "2" 'or similar... the control (by
default,
using
my
methodology) is called 1 but you can remap it to something
different
by
doing this.
I think you should, therefore, be able to loop through values in
a
form..
For i = 1 To 11 Step 5
me.whatever=i
docmd.openreport "ReportName"
next
....and each time the report opens it references
forms!formname!whatever
to
see the first week it should be working with.
Simple, isn't it?
We do seem to have got a little way away from where this thread
started
out,
but I think it's going to be tricky to come up with an automated
solution no
matter how you try and do it (if you use the pivottable
approach,
then
that's going to be equally useless when you have 52 weeks to
cope
with).
The simple approach would be to just push the Excel data out
into a
spreadsheet, or just create a new spreadsheet linked in to the
database,
but
that's always going to require the user to be able to manipulate
the
spreadsheet in order to get the required output.
I'm going for a nice lie down now anyway....
The selection of the weeks needs to be flexible. Sometimes
the
user
will
only want to print 2 weeks, another time three weeks, another
time
four
weeks
and occasionally the scheduling committee may want to print 52
weeks
in
advance.