ACCESS FEED: Re: [MS_AccessPros] Re: Order question in query.

Jumat, 15 April 2016

Re: [MS_AccessPros] Re: Order question in query.

you're welcome, Duane, and thanks, that is a clever trick ~ bon voyage

glad you got it, Kevin ~ happy to help

warm regards,
crystal

~ have an awesome day ~

On 4/15/2016 6:33 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:

Thanks Crystal. From my answers you might think I was paying for messages by the character ;-)

Have an awesome weekend,

Duane

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 14 Apr 2016 20:56:47 -0600
Subject: Re: [MS_AccessPros] Re: Order question in query.

adding on ...

Duane used an alias in the SUBQUERY to differentiate from the group booking table in the main query -- note that when GB is used it refers to subquery and tblGroupBooking refers to the main query source

LEFT(GB.GroupCode,6) = LEFT(tblGroupBooking.GroupCode,6)) AS GroupCheckin
FROM tblGroupBooking;

On 4/14/2016 8:53 PM, crystal 8 wrote:

Hi Kevin

tblGroupBooking GB

can also be written as:

tblGroupBooking As GB

WHERE
GB is an 'alias', nickname, abbreviation, etc -- instead of writing the tablename out each time ...

assume GB is short for GroupBooking ...

warm regards,
crystal

~ have an awesome day ~

On 4/14/2016 6:54 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] wrote:

Duane,

What does "GB" mean? Is there any online descriptions about how to use "GB"? Thanks.

Best Regards,

Kevin

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]

Date: 2016-04-12 06:12

To: Access Professionals Yahoo Group

Subject: RE: [MS_AccessPros] Re: Order question in query.

Kevin,
Apparently you want to find the earliest Checkin for the first 6 characters of the GroupCode and use this for sorting the group.

You can use a subquery like:

SELECT tblGroupBooking.CheckIn, tblGroupBooking.GroupCode,

(SELECT MIN(Checkin) from tblGroupBooking GB WHERE LEFT(GB.GroupCode,6) = LEFT(tblGroupBooking.GroupCode,6)) AS GroupCheckin

FROM tblGroupBooking;

I don't believe you can sort by the derived column in the query but think this might work to sort a report. If that doesn't work, you could replace the subquery with DMin().

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 12 Apr 2016 05:54:27 +0800
Subject: Re:[MS_AccessPros] Re: Order question in query.

Bill-

This is the result I want:

===tblGroupBooking====

Checkin        GroupCode

2016-May-01    abc001-A

2016-May-05    abc001-B

2016-May-08    abc001-C

2016-May-02    def002

2016-May-03    def003



Best Regrds,

Kevin

Life has an uncanny way of responding to your need.

在 "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2016 年4月12日上午5:26写道：

Kevin

I'm not understanding what you mean by "they should stick together". The sort order should still work even though the group code is a little longer.

What results are you getting now and what is your query?

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.microsoft.com/en-us/PublicProfile/35852?fullName=Bill%20%20Mosca

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <zhaoliqingoffice@163.com> wrote :

Dear All,

Is the possible to get this done by query?

===tblGroupBooking====
Checkin        GroupCode
2016-May-01    abc001-A
2016-May-05    abc001-B
2016-May-08    abc001-C
2016-May-02    def002
2016-May-03    def003


I want the check in to be the first order, and BroupCode the second order. But If the group code has the same prefix (e.g. abc001), then they should stick to one another. Is that possible? Or is there's any other way to make the outcome look like this? Thanks in advance.

Best Regards,
Kevin