how to program a single field in report

[Candy]

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

They are applied with the same expression. You can't group without sorting.

--
Duane Hookom
MS Access MVP
--

then which one has the higher priority, the grouping? or Sorting?

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.

 

[Duane Hookom]

You are not reading what I am writing. When you place a query as the record
source of a report, the sorting in the query is generally ignored. Don't
ever count on the sorting in the query having any effect.

Use the sorting and grouping dialog in the report to reliably sort the
report.

--
Duane Hookom
MS Access MVP
--

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

They are applied with the same expression. You can't group without
sorting.

--
Duane Hookom
MS Access MVP
--

then which one has the higher priority, the grouping? or Sorting?

:

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.

 

[Candy]

All solved. Many thanks.

You are not reading what I am writing. When you place a query as the record
source of a report, the sorting in the query is generally ignored. Don't
ever count on the sorting in the query having any effect.

Use the sorting and grouping dialog in the report to reliably sort the
report.

--
Duane Hookom
MS Access MVP
--

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

They are applied with the same expression. You can't group without
sorting.

--
Duane Hookom
MS Access MVP
--

then which one has the higher priority, the grouping? or Sorting?

:

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.

 

[Candy]

Hi, Duane,
The program has been launched , but the problem is it is quite slow when
generating the cross tab query report. Is there any way to speed it up?

Cheers,

Candy

You are not reading what I am writing. When you place a query as the record
source of a report, the sorting in the query is generally ignored. Don't
ever count on the sorting in the query having any effect.

Use the sorting and grouping dialog in the report to reliably sort the
report.

--
Duane Hookom
MS Access MVP
--

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

They are applied with the same expression. You can't group without
sorting.

--
Duane Hookom
MS Access MVP
--

then which one has the higher priority, the grouping? or Sorting?

:

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.

 

[Duane Hookom]

I have lost track of your report's record source query (and source queries)
as well as the code. Generically, you need to make sure fields used to join
or in criteria are indexed.

Using [Page] of [Pages] will seriously slow a report.

--
Duane Hookom
MS Access MVP
--

Hi, Duane,
The program has been launched , but the problem is it is quite slow when
generating the cross tab query report. Is there any way to speed it up?

Cheers,

Candy

You are not reading what I am writing. When you place a query as the
record
source of a report, the sorting in the query is generally ignored. Don't
ever count on the sorting in the query having any effect.

Use the sorting and grouping dialog in the report to reliably sort the
report.

--
Duane Hookom
MS Access MVP
--

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



:

They are applied with the same expression. You can't group without
sorting.

--
Duane Hookom
MS Access MVP
--

then which one has the higher priority, the grouping? or Sorting?

:

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.

 

[Candy]

I didn't use Pages. Basically, there are 3 queries:qUniCurrSche,
qcartJobOpers, and qxtbOperSchedule, the one bounded with the report is the
qxtbOperSchedule. I've listed the SQL code as following. Please check see if
there is anything I can improve it.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1), qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");

 

[Duane Hookom]

Is DIS_A indexed? Why not use
DIS_A < #01/01/2000#

I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to opening
your report, delete all the records from the table and use qUniCurSche to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.

--
Duane Hookom
MS Access MVP
--

I didn't use Pages. Basically, there are 3 queries:qUniCurrSche,
qcartJobOpers, and qxtbOperSchedule, the one bounded with the report is
the
qxtbOperSchedule. I've listed the SQL code as following. Please check see
if
there is anything I can improve it.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1), qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");