G
Guest
but the result is , in the crosstab query, it is ordered differently from the
report.
in the query, it is like:
Week JobDue JobNo Mon Tue Wed Thu Fri
5 01/03/2006 4001 OE OE INW
5 06/03/2006 3001 OE OE OE
6 01/03/2006 4001 EMB EMB EMB PRT QC
6 06/03/2006 3001 CUT CUT CUT CUT CUT
7 01/03/2006 4001 QC DIS
7 06/03/2006 3001 SUB SUB SUB SUB SUB
in the report, it is opposite:
Week JobDue JobNo Mon Tue Wed Thu Fri
5 06/03/2006 3001 OE OE OE
5 01/03/2006 4001 OE OE INW
6 06/03/2006 3001 CUT CUT CUT CUT CUT
6 01/03/2006 4001 EMB EMB EMB PRT QC
7 06/03/2006 3001 SUB SUB SUB SUB SUB
7 01/03/2006 4001 QC DIS
report.
in the query, it is like:
Week JobDue JobNo Mon Tue Wed Thu Fri
5 01/03/2006 4001 OE OE INW
5 06/03/2006 3001 OE OE OE
6 01/03/2006 4001 EMB EMB EMB PRT QC
6 06/03/2006 3001 CUT CUT CUT CUT CUT
7 01/03/2006 4001 QC DIS
7 06/03/2006 3001 SUB SUB SUB SUB SUB
in the report, it is opposite:
Week JobDue JobNo Mon Tue Wed Thu Fri
5 06/03/2006 3001 OE OE OE
5 01/03/2006 4001 OE OE INW
6 06/03/2006 3001 CUT CUT CUT CUT CUT
6 01/03/2006 4001 EMB EMB EMB PRT QC
7 06/03/2006 3001 SUB SUB SUB SUB SUB
7 01/03/2006 4001 QC DIS
Duane Hookom said:They are applied with the same expression. You can't group without sorting.
--
Duane Hookom
MS Access MVP
--
Candy said:then which one has the higher priority, the grouping? or Sorting?
Duane Hookom said:I'm not sure where you are at with this. Reports are only reliably
ordered
using the report's Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP
--
Hi, Duane,
Remember in the original Union query it only selected jobno, operation,
and
operdate? I've then added another date field called 'jobDue' which is
the
due
date for each job.
The question is, how do I order the report by 'jobDue' instead of
'jobNo'?
I've tried to use "order by jobDue" instead of "order by jobno" either
in
the cartesian query or in the crosstable query, or in both, none of
these
works, always order by jobno.
Is it because the jobno is a primary key? Isn't it any way to order by
"jobDue"?
Thanks.
Candy
:
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.