Multi-table joint establishment record set is very useful, because in some cases, we need to display the digital data type as the corresponding text name, which encounters the problem of multi-table joint establishment record set. For example, a member registration system, a total of five tables, member information data sheet Member, member identity table MemberIdentity, member privilege form MemberLevel, Member Category Meter MEMBERSORT and Member Marriage Status Table WEDLOCK. If you want to display all the membership registration information, you must connect these four forms, otherwise some of the membership information you can see is just the data number.
In terms of membership table, in its data table, 1 represents the ordinary member, 2 represents senior members, 3 represents a lifetime member, when displaying, if you do not associate with the member table detailed data table, then what we now Seeing a registration information of a general member, we can only see that its category is 1, and who will know that 1 representative is ordinary member? So to associate the member class table with the member detailed data sheet, after the association, 1 is displayed as a regular member, 2 is displayed as a senior member, 3 is displayed as a lifelong member, how good? Similarly, the other two tables should be associated with the member detailed data table to display the data number as the corresponding name.
I have encountered this problem in the future of the website, in the bread forum, the mad club, blue ideals, and the 5D multimedia forum sent a post, there is no answer, I have to study myself, I spent two days, I finally succeeded, now Write it into a tutorial for everyone to share, I hope everyone will go less.
This tutorial is to link five episodes. If you want, you can connect more together, the method is very similar ~
Step 1: Establish a database named Member using the Access software, built five tables, respectively: Member Information Datasheet Member, Member Identom Member Memberlevel, Member Category Meter MEMBERSORT and Member Marriage Status Table WEDLOCK .
● Member Information Data Sheet MEMBER:
MEMBERID: Auto number, primary key (ID number)
MEMBERSORT: Digital (Member Category)
MEMBERNAME: Text, member name
Password: Text (member password)
MemberLevel: Digital (Member Privilege)
MEMBERIDENTITY: Number (member identity)
WEDLOCK: Digital (Marital Status)
MEMBERQQ: Text (QQ number)
MEMBEREMAIL: Text (Member Email)
MemberDate: Date / Time (Member Registration Date)
● Member Idential Member MEMBERIDENTINTITY:
MEMBERIDENTITY: Auto number, primary key (ID number)
IdentityName: Text (member identity name)
● Membership Table Memberlevel:
MEMBERLEVEL: Auto number, primary key (ID number)
LevelName: Text (Member Right Name)
● Member class meter Membersort:
MEMBERSORT: Auto number, primary key (ID number)
Sortname: Text (Member Category Name) ● Member Marital Status Table Wedlock
WEDLOCK: Auto number, primary key (ID number)
WEDLOCKNAME: Text (member marital status category)
Note: After five tables are built, you can set your own categories, such as member privileges, you can set up two categories - "Unpaid members" and "paid members", numbers are "1", "2", as you have set up three options, then the number of the third option is of course "3".
Below we have to ask the numbers such as "1", "2" as "unpaid members" and "paid members", otherwise, everyone will know "1" representative "unpaid members" "2" represents "paid members"?
Step 2: Building a DSN data source, build a record set
● Run Dreamweaver MX software, build a DSN data source named Connmember (you can also do other names) at the Member Registration Information display page.
● Click "Bind" in the server behavior panel, build a data set named Membershow, "Connect" Select Connmember, "Table" Select MEMBER, "Column", "Sort" Select MEMBERDATE, descending. Click the "Advanced" button to modify the code automatically generated in the SQL box:
The original code is:
SELECT *
From member
Order by Memberdate DESC
Modify the code to:
SELECT *
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort = MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel = 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 make a lot of work!
Now you can open a record set, all the fields in the five tables are integrated in the Membershow record set, and you can bind the corresponding fields in the cell you want to display. This is better, all the number numbers become the corresponding name, such as membership, no longer "1" and "2" digital form, but become the corresponding name "unpaid member" and "Paid members". Other digital numbers have also become a displayed text name, is it very happy?
Precautions:
● In the input letter, must use the English half-width punctuation, and one half-angle space between words;
● When a table is established, if a table is coupled to multiple tables, the fields in this table must be a "digital" data type, and the same fields in multiple tables must be the primary key, but also "Auto Number" data. Types of. Otherwise, it is difficult to join success.
● Code Nested Quick Method: If you want to connect five tables, just add a front and rear brackets on the code connected to the four tables (front brackets are added behind from the future, the posterior brackets are added at the end of the code), then Continue to add the "Inner Join Name X ON Table 1." INNER JOIN Table Name "code after the poster parentheses, so you can join the data table :) Syntax format:
In fact, Inner Join ... ON's grammatical format 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 number) INNER JOIN Table X on Member. Field number = Table X. Field number
You can use this format if you have this format.
Existing format example:
Although I have already understood it, I will use the membership registration system as an example to provide some existing grammar format examples, as long as you modify the data table name and field name.
Usage of two data tables:
From member inner join memberster on member.membersort = Membersort.membersort
The syntax format can be summarized as:
From table 1 Inner Join table 2 ON table 1. Field number = Table 2. Field number
Usage of connecting three data sheets:
From (Member Inner Join MEMBERSORT ON Member.membersort = Membersort.membersort) Inner Join MemberLevel On Member.memberlevel = MemberLevel.memberLevel
The syntax format 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
Connecting four data tables:
FROM ((Member INNER JOIN MemberSort ON Member.MemberSort = MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel = MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity = MemberIdentity.MemberIdentity
The syntax format 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 number
Connecting five data tables:
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort = MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel = MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity = MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock = Wedlock .Wedlock
The syntax format 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 number) INNER JOIN Table 5 On Member. Field number = Table 5. The field number connection six data tables: slightly, similar to the above-mentioned coupling method, everyone raises three things :)
