It is very useful to create record sets for multi-table joins, because in some cases, we need to display the numeric data type as the corresponding text name, which encounters the problem of creating record sets for multi-table joins. For example, as a member registration system, there are five tables, member information data table member, member identity table MemberIdentity table, member permission table MemberLevel, member category table MemberSort and member marital status table Wedlock. If you want to display all member registration information, you must connect these four tables, otherwise some member information you see may be just data numbers.
In terms of the member category table, in its data table, 1 represents an ordinary member, 2 represents a senior member, and 3 represents a lifetime member. When displaying, if the member category table is not associated with the member detailed data table, then if we are now seeing the registration information of an ordinary member, we can only see that its category is 1, and who will know that 1 represents an ordinary member? Therefore, you need to associate the member category table with the member detailed data table. After the association, 1 will be displayed as an ordinary member, 2 will be displayed as a senior member, and 3 will be displayed as a lifetime member. How good is this? Similarly, the other two tables must also be associated with the member detailed data table before the data number can be displayed as the corresponding name.
Step 1: Use Access software to create a database called Member, and create five tables in it, namely: member information data table member, member identity table MemberIdentity, member permission table MemberLevel, member category table MemberSort and member marital status table Wedlock.
●Member information data table member:
MemberID: automatic numbering, primary key (ID number)
MemberSort: Numbers (member category)
MemberName: text, member name
Password: Text (member password)
MemberLevel: Number (member permissions)
MemberIdentity: Number (membership)
Wedlock: Numbers (marital status)
MemberQQ: Text (QQ number)
MemberEmail: Text (member mailbox)
MemberDate: Date/Time (member registration date)
●MemberIdentity table MemberIdentity:
MemberIdentity: automatic numbering, primary key (ID number)
IdentityName: Text (membership name)
●Member Rights Table MemberLevel:
MemberLevel: automatic numbering, primary key (ID number)
LevelName: Text (member permission name)
●Member Category Table MemberSort:
MemberSort: automatic numbering, primary key (ID number)
SortName: Text (member category name)
●Member marital status chart Wedlock
Wedlock: automatic numbering, primary key (ID number)
WedlockName: Text (Member Marriage Status Category)
Note: After the five tables are built, you can set the category you want by yourself, such as membership permissions. You can set two categories - "Unpaid Member" and "Paid Member", with the numbers "1" and "2" respectively. If you set three options, the number of the third option is of course "3".
What we have to do below is to display numbers such as "1" and "2" as "unpaid member" and "paid member". Otherwise, who would know that "1" represents "unpaid member" and "2" represents "paid member"?
Step 2: Create a DSN data source and create a record set
● Run Dreamweaver MX software and create a DSN data source named ConnMember (you can also give other names) on the member registration information display page.
●Click "Binding" in the server behavior panel to create a data set called MemberShow, select ConnMember in "Conn"; select Member in "Table"; select Member in "Columns", select MemberDate in "Sort", and select MemberDate in descending order. Click the "Advanced" button to modify the automatically generated code in the SQL box:
Original code is:
SELECT *
FROM Member
ORDER BY MemberDate DESC
Modify the code to:
SELECT *
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
ORDER BY MemberDate DESC
After modifying the code, click "OK" and the task is done!
Now, you can open the record set and take a look. All fields in the five tables are integrated in the MemberShow record set. You just need to bind the corresponding field to the cell that the field wants to display. Now, all the numerical numbers have become corresponding names, such as membership permissions, which are no longer in the form of "1" and "2", but have become corresponding names "unpaid member" and "paid member". Other numerical numbers have also become displayed text names. Are you very happy?
Notes:
●In the process of entering letters, you must use half-width punctuation in English and leave half-width spaces between words;
●When creating a data table, if a table is joined to multiple tables, the fields in this table must be a "number" data type, and the same fields in multiple tables must be primary keys and are "auto-numbered" data type. Otherwise, it will be difficult to successfully connect.
●Quick method for code nesting: For example, if you want to join five tables, just add a front and back bracket to the code that connects four tables (the first bracket is added at the back of the FROM, and the back bracket is added at the end of the code), and then continue to add the "INNER JOIN Table Name X ON Table 1. Field Number = Table X. Field Number" code after the brackets, so that you can join the data table infinitely:)
Syntax format:
In fact, the syntax format of INNER JOIN… 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. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table X ON Member. Field number = Table X. Field number
You just need to apply this format.
Ready-made format example:
Although I have already made it clearer, in order to take care of beginners, I will take this member registration system as an example to provide some ready-made syntax format examples. You just need to modify the data table name and field name in it.
Usage of connecting two data tables:
FROM Member INNER JOIN MemberSort ON =
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 tables:
FROM (Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =
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
Usage of connecting four data tables:
FROM ((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =
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. Field number) INNER JOIN Table 4 ON Member. Field number = Table 4. Field number
Usage of connecting five data tables:
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
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. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table 5. Field number
In terms of the member category table, in its data table, 1 represents an ordinary member, 2 represents a senior member, and 3 represents a lifetime member. When displaying, if the member category table is not associated with the member detailed data table, then if we are now seeing the registration information of an ordinary member, we can only see that its category is 1, and who will know that 1 represents an ordinary member? Therefore, you need to associate the member category table with the member detailed data table. After the association, 1 will be displayed as an ordinary member, 2 will be displayed as a senior member, and 3 will be displayed as a lifetime member. How good is this? Similarly, the other two tables must also be associated with the member detailed data table before the data number can be displayed as the corresponding name.
Step 1: Use Access software to create a database called Member, and create five tables in it, namely: member information data table member, member identity table MemberIdentity, member permission table MemberLevel, member category table MemberSort and member marital status table Wedlock.
●Member information data table member:
MemberID: automatic numbering, primary key (ID number)
MemberSort: Numbers (member category)
MemberName: text, member name
Password: Text (member password)
MemberLevel: Number (member permissions)
MemberIdentity: Number (membership)
Wedlock: Numbers (marital status)
MemberQQ: Text (QQ number)
MemberEmail: Text (member mailbox)
MemberDate: Date/Time (member registration date)
●MemberIdentity table MemberIdentity:
MemberIdentity: automatic numbering, primary key (ID number)
IdentityName: Text (membership name)
●Member Rights Table MemberLevel:
MemberLevel: automatic numbering, primary key (ID number)
LevelName: Text (member permission name)
●Member Category Table MemberSort:
MemberSort: automatic numbering, primary key (ID number)
SortName: Text (member category name)
●Member marital status chart Wedlock
Wedlock: automatic numbering, primary key (ID number)
WedlockName: Text (Member Marriage Status Category)
Note: After the five tables are built, you can set the category you want by yourself, such as membership permissions. You can set two categories - "Unpaid Member" and "Paid Member", with the numbers "1" and "2" respectively. If you set three options, the number of the third option is of course "3".
What we have to do below is to display numbers such as "1" and "2" as "unpaid member" and "paid member". Otherwise, who would know that "1" represents "unpaid member" and "2" represents "paid member"?
Step 2: Create a DSN data source and create a record set
● Run Dreamweaver MX software and create a DSN data source named ConnMember (you can also give other names) on the member registration information display page.
●Click "Binding" in the server behavior panel to create a data set called MemberShow, select ConnMember in "Conn"; select Member in "Table"; select Member in "Columns", select MemberDate in "Sort", and select MemberDate in descending order. Click the "Advanced" button to modify the automatically generated code in the SQL box:
Original code is:
SELECT *
FROM Member
ORDER BY MemberDate DESC
Modify the code to:
SELECT *
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
ORDER BY MemberDate DESC
After modifying the code, click "OK" and the task is done!
Now, you can open the record set and take a look. All fields in the five tables are integrated in the MemberShow record set. You just need to bind the corresponding field to the cell that the field wants to display. Now, all the numerical numbers have become corresponding names, such as membership permissions, which are no longer in the form of "1" and "2", but have become corresponding names "unpaid member" and "paid member". Other numerical numbers have also become displayed text names. Are you very happy?
Notes:
●In the process of entering letters, you must use half-width punctuation in English and leave half-width spaces between words;
●When creating a data table, if a table is joined to multiple tables, the fields in this table must be a "number" data type, and the same fields in multiple tables must be primary keys and are "auto-numbered" data type. Otherwise, it will be difficult to successfully connect.
●Quick method for code nesting: For example, if you want to join five tables, just add a front and back bracket to the code that connects four tables (the first bracket is added at the back of the FROM, and the back bracket is added at the end of the code), and then continue to add the "INNER JOIN Table Name X ON Table 1. Field Number = Table X. Field Number" code after the brackets, so that you can join the data table infinitely:)
Syntax format:
In fact, the syntax format of INNER JOIN… 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. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table X ON Member. Field number = Table X. Field number
You just need to apply this format.
Ready-made format example:
Although I have already made it clearer, in order to take care of beginners, I will take this member registration system as an example to provide some ready-made syntax format examples. You just need to modify the data table name and field name in it.
Usage of connecting two data tables:
FROM Member INNER JOIN MemberSort ON =
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 tables:
FROM (Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =
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
Usage of connecting four data tables:
FROM ((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =
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. Field number) INNER JOIN Table 4 ON Member. Field number = Table 4. Field number
Usage of connecting five data tables:
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
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. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table 5. Field number