ACCESS FEED: Re: Re: [MS_AccessPros] generate SeqNo. in query?

Rabu, 03 Februari 2016

Re: Re: [MS_AccessPros] generate SeqNo. in query?

John-

Thanks a lot. I will try it later.

Best Regards,

Kevin

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2016-02-03 18:55
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] generate SeqNo. in query?

Kevin-

Assuming RoominglistID is unique, to sequence records within a Group, do this:

SELECT tblRoominglist.*, (SELECT Count(GroupID) FROM tblRoominglist As R2 WHERE R2.GroupID = tblRoominglist.GroupID AND R2.RoominglistID <= tblRoominglist.RoominglistID) As SeqNo
FROM tblRoominglist

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 2, 2016, at 11:35 PM, 'zhaoliqingoffice'zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
The reason I need to generate SeqNo is that often times we need to export the rooming list to airlines, and it requires sequence number. The table structure is as below:
tblRoominglist
RoominglistID
GroupID
GuestName
Gender
DateOfBirth
Passport
...

the query I need:
SeqNo
GuestName
Gender
DateOfBirth
Passport
....

Please help. Thanks.
Best Regards,
Kevin

---原始邮件---
发件人: "'zhaoliqingoffice'zhaoliqingoffice@163.com [MS_Access_Professionals] "<MS_Access_Professionals@yahoogroups.com>
发送时间: 2016年2月1日 23:42:32
收件人: "John Viescas JohnV@msn.com [MS_Access_Professionals]"<MS_Access_Professionals@yahoogroups.com>;
主题: 回复：[MS_AccessPros] generate SeqNo. in query?

John-
It's a rooming list. I will write to you tomorrow. Thanks.
Best Regards,
Kevin

---原始邮件---
发件人: "John Viescas JohnV@msn.com [MS_Access_Professionals] "<MS_Access_Professionals@yahoogroups.com>
发送时间: 2016年2月1日 23:29:21
收件人: "MS_Access_Professionals"<MS_Access_Professionals@yahoogroups.com>;
主题: Re: [MS_AccessPros] generate SeqNo. in query?

Yes. Really simple in a report - create a text box with the Control Source set to =1 and Running Sum set to Yes. A bit more tricky in a query, but I would need to know the structure of your tables first.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 1, 2016, at 2:49 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
Is that possible to generate sequence number in query? Such as 1,2,3...? Thanks in advance.
Best Regards,
Kevin