Hello,
I have a query that pulls dates for different work cells. Here's the code:
SELECT dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.seri
es, Format(DateAdd("s",[act_st
art_cut],#
1/1/1970#)
,"mm/dd/yy
") AS [CUTTING START], Format(DateAdd("s",[act_en
d_cut],#1/
1/1970#),"
mm/dd/yy")
AS [CUTTING END], Format(DateAdd("s",[act_st
art_mach],
#1/1/1970#
),"mm/dd/y
y") AS [MACHINING START], Format(DateAdd("s",[act_en
d_mach],#1
/1/1970#),
"mm/dd/yy"
) AS [MACHINING END], Format(DateAdd("s",[act_st
art_weld],
#1/1/1970#
),"mm/dd/y
y") AS [WELDING START], Format(DateAdd("s",[act_en
d_weld],#1
/1/1970#),
"mm/dd/yy"
) AS [WELDING END], Format(DateAdd("s",[act_st
art_prep],
#1/1/1970#
),"mm/dd/y
y") AS [FAB PREP START], Format(DateAdd("s",[act_en
d_prep],#1
/1/1970#),
"mm/dd/yy"
) AS [FAB PREP END], Format(DateAdd("s",[act_st
art_coat],
#1/1/1970#
),"mm/dd/y
y") AS [COATING START], Format(DateAdd("s",[act_en
d_coat],#1
/1/1970#),
"mm/dd/yy"
) AS [COATING END], Format(DateAdd("s",[act_st
art_assy],
#1/1/1970#
),"mm/dd/y
y") AS [ASSEMBLY START], Format(DateAdd("s",[act_en
d_assy],#1
/1/1970#),
"mm/dd/yy"
) AS [ASSEMBLY END]
FROM dbo_projects LEFT JOIN dbo_projects_schedule ON dbo_projects.jobid = dbo_projects_schedule.jobi
d
GROUP BY dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.seri
es, Format(DateAdd("s",[act_st
art_cut],#
1/1/1970#)
,"mm/dd/yy
"), Format(DateAdd("s",[act_en
d_cut],#1/
1/1970#),"
mm/dd/yy")
, Format(DateAdd("s",[act_st
art_mach],
#1/1/1970#
),"mm/dd/y
y"), Format(DateAdd("s",[act_en
d_mach],#1
/1/1970#),
"mm/dd/yy"
), Format(DateAdd("s",[act_st
art_weld],
#1/1/1970#
),"mm/dd/y
y"), Format(DateAdd("s",[act_en
d_weld],#1
/1/1970#),
"mm/dd/yy"
), Format(DateAdd("s",[act_st
art_prep],
#1/1/1970#
),"mm/dd/y
y"), Format(DateAdd("s",[act_en
d_prep],#1
/1/1970#),
"mm/dd/yy"
), Format(DateAdd("s",[act_st
art_coat],
#1/1/1970#
),"mm/dd/y
y"), Format(DateAdd("s",[act_en
d_coat],#1
/1/1970#),
"mm/dd/yy"
), Format(DateAdd("s",[act_st
art_assy],
#1/1/1970#
),"mm/dd/y
y"), Format(DateAdd("s",[act_en
d_assy],#1
/1/1970#),
"mm/dd/yy"
)
HAVING (((dbo_projects.job_number
)="1220321
6") AND ((dbo_projects_schedule.se
ries)="A2"
));
and here's the result:
![result 1]()
Notice the highlighted fields. I want group it somehow to get one record like this:
![result 2]()
Thanks for your help,
Joel
Staying with your current format, assuming that only one row in a set would have any given date column filled in, you could use Max() for each date column
SELECT dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.seri
Group by dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.seri
Order by dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.seri