Genius123

asked on 2/6/2018

Access Query 2010 grouping

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.series, Format(DateAdd("s",[act_start_cut],#1/1/1970#),"mm/dd/yy") AS [CUTTING START], Format(DateAdd("s",[act_end_cut],#1/1/1970#),"mm/dd/yy") AS [CUTTING END], Format(DateAdd("s",[act_start_mach],#1/1/1970#),"mm/dd/yy") AS [MACHINING START], Format(DateAdd("s",[act_end_mach],#1/1/1970#),"mm/dd/yy") AS [MACHINING END], Format(DateAdd("s",[act_start_weld],#1/1/1970#),"mm/dd/yy") AS [WELDING START], Format(DateAdd("s",[act_end_weld],#1/1/1970#),"mm/dd/yy") AS [WELDING END], Format(DateAdd("s",[act_start_prep],#1/1/1970#),"mm/dd/yy") AS [FAB PREP START], Format(DateAdd("s",[act_end_prep],#1/1/1970#),"mm/dd/yy") AS [FAB PREP END], Format(DateAdd("s",[act_start_coat],#1/1/1970#),"mm/dd/yy") AS [COATING START], Format(DateAdd("s",[act_end_coat],#1/1/1970#),"mm/dd/yy") AS [COATING END], Format(DateAdd("s",[act_start_assy],#1/1/1970#),"mm/dd/yy") AS [ASSEMBLY START], Format(DateAdd("s",[act_end_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.jobid
GROUP BY dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.series, Format(DateAdd("s",[act_start_cut],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_cut],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_start_mach],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_mach],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_start_weld],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_weld],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_start_prep],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_prep],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_start_coat],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_coat],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_start_assy],#1/1/1970#),"mm/dd/yy"), Format(DateAdd("s",[act_end_assy],#1/1/1970#),"mm/dd/yy")
HAVING (((dbo_projects.job_number)="12203216") AND ((dbo_projects_schedule.series)="A2"));

and here's the result:

Notice the highlighted fields. I want group it somehow to get one record like this:

Thanks for your help,
Joel

Microsoft Access

Last Comment

Ryan Chong

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Ryan Chong

2/6/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

PatHartman

2/6/2018

I don't know what the dateadd()s and format()s are all about but your table is not normalized. If each of the dates were on a separate row, you would use a cross tab to pivot them.

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.series, Max([act_start_cut]) AS [CUTTING START], Max([act_end_cut]) as [CUTTING END], ... rest of the dates
Group by dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.series
Order by dbo_projects.job_number, dbo_projects.jobid, dbo_projects_schedule.series;

Gustav Brock

2/7/2018

Remove all instances of Format in your SQL, or the "dates" will be text which you can't do any meaningful "Max" on.

Then you can do something like:

IIf(Max([ASSEMBLY START]) = #1/1/1970#, Null, Max([ASSEMBLY START])) As [ASSEMBLY START]

Open in new window

and apply a format like this in the form:

mm/dd/yyyy,,,"N/A"

Open in new window

Genius123

2/7/2018

ASKER

Thank you. The Max worked.

Your help has saved me hundreds of hours of internet surfing.

fblack61

PatHartman

2/7/2018

Just FYI, Ryan's SQL will result in the dates being strings.

Gustav Brock

2/7/2018

Pat is right. It won't work as is. See my previous note, please.

Genius123

2/7/2018

ASKER

Thanks. I modified Ryan's solution slightly to bring them in as dates. Here's an example:

CUTTING END: Max(IIf([act_end_cut]<>0,Format(DateAdd("s",[act_end_cut],#1/1/1970#),"mm/dd/yy")))

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Gustav Brock

2/7/2018

That doesn't help. It is still text you try to "Max".
Study again my comment above. That is how to do it.

Genius123

2/7/2018

ASKER

Gustav, you are right. I changed it and it works great. Thank you. I'll try to change the points awarded.

Ryan Chong

2/7/2018

eventually you stil need to convert it to string if you want the same field to display as "na" for some of the date values

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Genius123

2/7/2018

ASKER

Ryan, I just used Null instead of "NA".

Gustav Brock

2/7/2018

you stil need to convert it to string if you want the same field to display as "na"

No, the format takes care of that in the form.

PatHartman

2/8/2018

There is one and only one place where you ever have to convert a date to a string and that is when you are creating an SQL string that includes a date value AND you are not using the US mm/dd/yyyy standard. That is because SQL ASSUMES mm/dd/yyyy format for dates.

Therefore:
if you create a Querydef with:
Where MyDate = Forms!someform!somedate
AND the form field is a datetime datatype, there is no reason to convert the date to a string.

However, if you build the SQL String in VBA,

strSQL = "Select ...From ... Where MyDate = #" Format(& Me.somedate, "mm/dd/yyyy") & "#"

You do need to reformat to m/d/y order.

SQL will also recognize the unambiguous yyyy/mm/dd order.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Ryan Chong

2/10/2018

No, the format takes care of that in the form.

you can do that if you want. there are many ways we can do/suggest here.