Joins multiple table-built recordsets with INNER join syntax

Recordset | syntax INNER join syntax joins multiple table build recordset a Recordset is useful because in some cases we need to display the numeric data type as the corresponding text name, and this encounters the problem of establishing a recordset with a multiple table join. For example, as a member registration system, a total of five tables, membership information Data sheet member, membership table memberidentity, membership rights table Memberlevel, membership category table Membersort and membership of the table wedlock of marital status. If you want to show the membership registration information, you must connect the four tables, otherwise you will see some member information may be just data number.
In the form of a membership category, in its datasheet, 1 on behalf of ordinary members, 2 on behalf of senior members, 3 for life members, in the display, if not the Membership category table with the member details of the table associated, then if we now see a general membership of the registration information, we can only see its category of 1, And who would know that 1 represents an ordinary member? So to the Membership category table and member details of the relevant data table, the association, 1 on display as ordinary members, 2 on display as a senior member, 3 on display as a lifelong member, so much good? Similarly, the other two tables should also be associated with the member Detail data table to display the data number as the corresponding name.

The day before yesterday when the site backstage encountered this problem, in the Bread Forum, fan club, Blue Ideal, and 5D Multimedia Forum sent a post for help, did not get the answer, had their own research, spent two days to finally succeed, now it is written as a tutorial for everyone to share, I hope that everyone less detours
This tutorial is the five tables together, if you want, you can put more tables together, the method is similar to the ~

Step One: Use Access software to establish a database named member, in which five tables, respectively: Membership information data table member, membership table memberidentity, membership rights table Memberlevel, Membership Category Table Membersort and Membership marital status table wedlock.

Membership Information Data Sheet member:
MemberID: Automatic numbering, primary key (ID number)
Membersort: Number (Member category)
MemberName: text, member name
Password: Text (member password)
Memberlevel: Number (member privileges)
Memberidentity: Number (Member status)
Wedlock: Numbers (marital status)
MEMBERQQ: Text (QQ number)
Memberemail: Text (member mailbox)
Memberdate: Date/Time (Member registration date)

Member Status Table memberidentity:
Memberidentity: Automatic numbering, primary key (ID number)
Identityname: Text (member identity name)

Member Privilege Table Memberlevel:
Memberlevel: Automatic numbering, primary key (ID number)
LevelName: Text (member privilege name)

Member Category Table Membersort:
Membersort: Automatic numbering, primary key (ID number)
Sortname: Text (member category name)

Membership Marital Status Table wedlock
Wedlock: Automatic numbering, primary key (ID number)
Wedlockname: Text (Member marital status category)
Note: After the five tables are built, you can set the categories you want, for members, you can set up two categories--"unpaid members" and "paid members", numbered "1", "2", and if you set three options, the number of the third option is, of course, "3".
The next thing we want to do is to put "1", "2" the number shown as "unpaid members" and "paid members", otherwise, everyone who will know that "1" represents the "unpaid members", "2" represents the "paid member"?

Step two: Build a DSN data source, build a recordset
Run the Dreamweaver MX software and build a DSN data source named Connmember (you can also have another name) on the member Registration information display page.

Click "Bindings" in the server behavior panel to build a dataset named Membershow, "Connect" select Connmember, "table" Select Member, "column" Select All, "Sort" choose Memberdate, Descending. Click on the "Advanced" button to modify the automatically generated code in the SQL box:
The original code is:
SELECT *
From member
ORDER BY Memberdate DESC

Modify the code to:
SELECT *
From ((() INNER join Membersort to Member.membersort=membersort.membersort) INNER JOIN memberlevel on Member.memberl Evel=memberlevel.memberlevel) INNER JOIN memberidentity on member.memberidentity=memberidentity.memberidentity) INNER JOIN Wedlock on Member.wedlock=wedlock.wedlock
ORDER BY Memberdate DESC
After modifying the code, click "OK" and you are done!
Now, you can open a recordset to see that the fields in the five tables are all integrated in the Membershow recordset, so you just bind the fields to the cells that the field wants to display. Well, all the numbers are turned into names, such as membership rights, no longer "1" and "2", but they become the corresponding names of "unpaid members" and "paid members". Other digital numbers have also become the display of the text name, is not very happy?

Precautions:
In the input letter process, must use the English Half-width punctuation mark, the word leaves the half corner space;
When you create a datasheet, if a table joins more than one table, the fields in this table must be of the number data type, and the same field in more than one table must be a primary key and an AutoNumber data type. Otherwise, it is difficult to connect successfully.
Code nesting Quick Method: For example, to connect five tables, just add a front and back bracket to the code that connects the four tables (the parentheses are appended to the from, the parentheses are appended to the end of the code), and then continue with the INNER JOIN table name x on table 1 after the parentheses. Field number = Table X. Field number Code, so you can join the data table indefinitely:

Syntax format:
In fact, INNER JOIN ... The syntax format of on can be summarized as:
From ((Table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER join table 3 on table 1. Field number = Table 3. Field number) INNER join table 4 on member. Field number = Table 4. Field no. = INNER Join Table x on member. Field number = table X. Field number
You can just apply the format.

Ready-made format examples:
Although I have said more clearly, but for the care of beginners, I still take this member registration system as an example, to provide some ready-made syntax format examples, you just modify the data table name and field name.

Use to connect two data tables:
From the INNER JOIN membersort on Member.membersort=membersort.membersort
The syntax format can be summed up as:
From table 1 INNER JOIN table 2 on table 1. Field number = Table 2. Field number

Use to connect three data tables:
From (Member INNER join Membersort on Member.membersort=membersort.membersort) INNER JOIN memberlevel on Member.memberlev El=memberlevel.memberlevel
The syntax format can be summed up as:
From (table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER JOIN table 3 on table 1. Field number = Table 3. Field number

Use to connect four data tables:
From ((Member INNER join Membersort to Member.membersort=membersort.membersort) INNER JOIN memberlevel on Member.memberle Vel=memberlevel.memberlevel) INNER JOIN memberidentity on member.memberidentity=memberidentity.memberidentity
The syntax format can be summed up as:
From (table 1 INNER JOIN table 2 on table 1. Field number = Table 2. Field number) INNER join table 3 on table 1. Field number = Table 3. Field number) INNER join table 4 on member. Field number = Table 4.

Use to connect five data tables:
From ((() INNER join Membersort to Member.membersort=membersort.membersort) INNER JOIN memberlevel on Member.memberl Evel=memberlevel.memberlevel) INNER JOIN memberidentity on member.memberidentity=memberidentity.memberidentity) INNER JOIN Wedlock on Member.wedlock=wedlock.wedlock
The syntax format can be summed up as:
From ((Table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER join table 3 on table 1. Field number = Table 3. Field number) INNER join table 4 on member. Field number = Table 4. Field no. = INNER Join Table 5 on member. Field number = Table 5. Field number

Connection six data table usage: slightly, similar to the above join method, everybody extrapolate bar: