Have you found the conditional formatting in the Format menu? You should
be
able to select for instance Monday and set the text box format based on
the
value being "Manu" or "Dis",....
--
Duane Hookom
MS Access MVP
--
Can you pls give me more details? Because I can't see there are any way
to
change the color of the operation according to their name. How could I
make
all the 'Manu' in red , and all the 'Dis' in green?
Thanks.
:
You can use conditional formatting or code.
--
Duane Hookom
MS Access MVP
--
Thank you so much, Duane,
It is exactly what I want.
Now I need to show those 'MANU', 'BAND' as colored bar in the
report,
is
there any way to do that?
:
Your date format threw me a bit especially since your report
display
doesn't
match your data. Job 1111 should be MANU on 3/6 rather than <BLANK>
(unless
I am totally confused).
I would first create a union query to normalize the table:
==quniJobOpers=======
SELECT JobNo, "CUT" as Operation, Cut_Date as OperDate
FROM [thetable]
UNION ALL
SELECT JobNo, "BAND", Band_Date
FROM [thetable]
UNION ALL
SELECT JobNo, "MANU", Manu_Date
FROM [thetable]
UNION ALL
SELECT JobNo, "DIS", Dis_Date
FROM [thetable];
Create a table of all possible dates:
Table Name: tblAllDates
Field Name: TheDate date/time with every unique date value
Then create a cartesian query to get all the dates filled in
between
operations:
==qcartJobOpers==========
SELECT quniJobOpers.JobNo, quniJobOpers.Operation,
quniJobOpers.OperDate, tblAllDates.TheDate
FROM tblAllDates, quniJobOpers
WHERE (((tblAllDates.TheDate) Between [OperDate] And
(SELECT Min(OperDate)-1
FROM quniJobOpers j
WHERE j.JobNo = quniJobOpers.JobNo AND
j.OperDate > quniJobOpers.OperDate)))
ORDER BY quniJobOpers.JobNo, tblAllDates.TheDate;
You can the Create a crosstab query for your report
==qxtbOperSchedule===========
PARAMETERS [Enter the Week Number] Short;
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr,
DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.JobNo
FROM qcartJobOpers
WHERE (((DatePart("ww",[TheDate]-1))=[Enter the Week Number]))
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday");
--
Duane Hookom
MS Access MVP
--
the table has Primary key 'Jobno', and 4 date field each called
cut_date,
band_date,manu_date, and dis_date. Those 4 fields are
representing
the
4
manufacture status cutting->banding->manufacturing->dispatch
The Table is like:
JobNo cut_date band_date manu_date dis_date
1111 01/03/2006 03/03/2006 05/03/2006 10/03/2006
2222 05/03/2006 08/03/2006 15/03/2006 16/03/2006
3333 02/03/2006 05/03/2006 9/03/2006 1203/2006
4444 04/03/2006 08/03/2006 12/03/2006 15/03/2006
The report I want is like: (suppose the week selected is between
06/03/2006
and 12/03/2006)
06/03 07/03 08/03 09/03 10/03
11/03
12/03
JobNo Monday Tuesday Wednesday Thursday Friday Saturday Sunday
1111 <BLANK> <BLANK> CUT CUT BAND BAND
MANU
2222 CUT CUT BAND BAND BAND BAND
BAND
3333 BAND BAND MANU MANU MANU MANU
DIS
4444 CUT CUT BAND BAND BAND BAND
MANU
Eventually, we'd like using 4 color bars instead of those text:
cut,
band,
manu, dis.
I've already made the first record(1111) showing up in the
report,
but
for
the rest of the lines, they are all the same as the job 1111
The
method
I'm using is Dlookup(). how do I make it to lookup for the second
job
in
the
report?
any ideas?
Thanks
:
You could provide your table structure(s), some sample records,
and
the
desired display in your report...
--
Duane Hookom
MS Access MVP
--
Hi, there,
I'm trying to customize a report in which the value of the
display
field
is
decided by a record field of the record source for that
report.
For example, there is a Table called 'Jobs' , with 'Jobno' as
the
primary
key, it also has several dates for different status. In the
report,
It
need
to display JobNo, and a selected week period( Monday to
Friday, 5
days).
For
each week day, how do I program the report to calculate all
the
status
dates
which is matching that week day?
It sounds confusing, but it is all I can explain.
Thanks.