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
(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
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
(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
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar