how to program a single field in report

C

Candy

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.
 
D

Duane Hookom

You could provide your table structure(s), some sample records, and the
desired display in your report...
 
C

Candy

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
 
D

Duane Hookom

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");
 
C

Candy

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?


Duane Hookom said:
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
--

Candy said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

You can use conditional formatting or code.

--
Duane Hookom
MS Access MVP
--

Candy said:
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?


Duane Hookom said:
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
--

Candy said:
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.
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

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.

Duane Hookom said:
You can use conditional formatting or code.

--
Duane Hookom
MS Access MVP
--

Candy said:
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?


Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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

Candy said:
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.

Duane Hookom said:
You can use conditional formatting or code.

--
Duane Hookom
MS Access MVP
--

Candy said:
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.
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

Thanks, duane, but it seems that only 3 conditions are allowed, and I need at
least 4 ...

Duane Hookom said:
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
--

Candy said:
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.

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Use code. Assuming your text box names are Monday, Tuesday,...Sunday and the
backgrounds are not transparent.
Put this code in the On Format event of the Detail Section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intDay As Integer
Dim strDay As String
Dim lngColor As Long
For intDay = 1 To 7
strDay = Format(DateSerial(2006, 1, intDay), "dddd")
Select Case Me(strDay)
Case "MANU"
lngColor = 39423
Case "BAND"
lngColor = 65535
Case "CUT"
lngColor = 13434828
Case "DIS"
lngColor = 16751052
Case Else
lngColor = vbWhite
End Select
Me(strDay).BackColor = lngColor
Next

--
Duane Hookom
MS Access MVP
--

Candy said:
Thanks, duane, but it seems that only 3 conditions are allowed, and I need
at
least 4 ...

Duane Hookom said:
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
--

Candy said:
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.
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

Hi, Duane,
I've tried your code, it always pop up an syntax error message on the line
"Me(strDay).BackColor = lngColor)"

To be honest, I don't know what is the expression "Me(strDay)"
representing either... Could you please provide more details, or related
knowledge based links?

Thanks so much for you consistant support:)

Duane Hookom said:
Use code. Assuming your text box names are Monday, Tuesday,...Sunday and the
backgrounds are not transparent.
Put this code in the On Format event of the Detail Section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intDay As Integer
Dim strDay As String
Dim lngColor As Long
For intDay = 1 To 7
strDay = Format(DateSerial(2006, 1, intDay), "dddd")
Select Case Me(strDay)
Case "MANU"
lngColor = 39423
Case "BAND"
lngColor = 65535
Case "CUT"
lngColor = 13434828
Case "DIS"
lngColor = 16751052
Case Else
lngColor = vbWhite
End Select
Me(strDay).BackColor = lngColor
Next

--
Duane Hookom
MS Access MVP
--

Candy said:
Thanks, duane, but it seems that only 3 conditions are allowed, and I need
at
least 4 ...

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

My mistake, Duane, your code works as charm...

Duane Hookom said:
Use code. Assuming your text box names are Monday, Tuesday,...Sunday and the
backgrounds are not transparent.
Put this code in the On Format event of the Detail Section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intDay As Integer
Dim strDay As String
Dim lngColor As Long
For intDay = 1 To 7
strDay = Format(DateSerial(2006, 1, intDay), "dddd")
Select Case Me(strDay)
Case "MANU"
lngColor = 39423
Case "BAND"
lngColor = 65535
Case "CUT"
lngColor = 13434828
Case "DIS"
lngColor = 16751052
Case Else
lngColor = vbWhite
End Select
Me(strDay).BackColor = lngColor
Next

--
Duane Hookom
MS Access MVP
--

Candy said:
Thanks, duane, but it seems that only 3 conditions are allowed, and I need
at
least 4 ...

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Steve provided the advice that I would have given. You might be able to use
a single crosstab rather than two crosstabs and a union.
 
C

Candy

Hi Duane,
I'm a little confused of the concept. What I did for one report was
following your instruction: create a Union query first, then, create a
cartesian query based on the Union one. At last, make a crosstab query for
the report.
Now, if I want to merge these 2 reports into one, which of the above query
do I need to Union together?
 
C

Candy

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



Duane Hookom said:
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
--

Candy said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

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

Candy said:
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



Duane Hookom said:
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
--

Candy said:
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.
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

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

Candy said:
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



Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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

Candy said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Group Footer DLookUp 0
Schedule Comparison Error - mismatch duration - "The duration you entered isn't supported for this field. type a duration in the standard format" 0
Show week number in Project Report 0
How to implement two shifts per day with multiple teams 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Creating a Search using a combo box and then creating a report. 4
How to Create an S-Curve from Custom Field? 0
Date Format In A Report 2

Top