Multiple SQL tables query and computing statistics code based on ACCESS and ASP, accesssql

Multiple SQL tables query and computing statistics code based on ACCESS and ASP, accesssql

Recently, I am writing a few management systems about "Project-Subitem-Task", saying that the system is a bit exaggerated. It is basically a multi-Table query call and database operations of insert and update, the SQL statements are complex because of a lot of computing and statistics issues. Therefore, it is necessary to make a summary after a stage task is completed, and summarize some SQL code in the test process, in case you forget to use it later, you are also welcome to join the database coders.

Several systems have been written in the past few months. The last system is the credit card management system, which is also a complex stage of SQL accumulation, taking this as an example, it is more appropriate to sort out multiple SQL tables based on ACCESS and ASP over this period of time to query and calculate the Statistical Code.

PS: Some information is hidden because it involves the information security of your bank. However, the quota and Bill amount are still true. Is it very difficult.

Back to the topic, there are a lot of credit cards, and I hope to use a better solution to count credit card bills, quotas, payments, and other information. I don't want to use software such as "51 credit card management, it is best for people to obtain their financial information by creating their own databases and putting them on their own cloud hosts or virtual servers. The same is true for mobile phones. This is the reason for making this simple system. A lot of data is not a result, but the code is improved by constantly adding the information to be collected during use.

Create a CreditCard database. I use ACCESS2007 and the extension is accdb. A total of three tables are created, including credit (credit card information), Bill (Bill), and Payment (repayment). Try to use the most basic data input to reduce redundancy, more computing and statistics are written on ASP.

Just like the CreditCard Information page above, let's look at the SQL statement:

Select Credit. ID as number, Credit. CCno as index, Credit. bank as Bank, Credit. brand as brand, Credit. card as Card number, Format (Credit. limit, 'currency ') as Credit. belong as cardholder, Credit. billday as Bill day, Credit. consumeday as consumption day, Count (Bill. CCno) as Bill count, Format (Credit. templimit, 'currency ') as temporary quota, Format (Credit. unsettled, 'currency ') as unpaid amount, Format (Credit. limit + Credit. templimit-Credit.unsettled), 'currency ') as remaining Credit. newtime as Update time FROM Credit left join Bill ON Credit. CCno = Bill. CCnoGROUP BY Credit. ID, Credit. CCno, Credit. bank, Credit. brand, Credit. card, Credit. limit, Credit. belong, Credit. billday, Credit. consumeday, Credit. templimit, Credit. unsettled, Credit. newtimeorder by Credit. ID

The name after "as" in the Select statement can be used as the name of the field (Column). For actual amount counting and Currency symbols, Format (field or expression, 'currency ') is used ') the format is ￥12345.67. expressions can be used in SQL statements, such as "quota + temporary quota-amount not billed" to obtain the remaining quota. Because the Count (Statistics) Bill table is used to calculate the number of bills of the corresponding bank, the link to the two tables is used: [Credit] and [Bill. The link between the two tables is relatively easy. You can use LEFT, INNER, and RIGHT to implement Count. However, in other usage requirements, the link methods of the three tables are different, especially for applications with links to more than three tables.

PS: invisible index, bank, card number, etc.

Bill Payment is used to call three tables at the same time. The purpose is to view the credit card Bill, repayment time, repayment status, and other information at a glance, and check the SQL statement:

Select Bill. ID as serial number, Bill. CCno as index, Credit. bank as bank, Credit. card as Card number, Bill. BLno as Bill number, Month (Bill. billdate) as Bill month, Bill. paydate as the last repayment date, Format (Bill. balance, 'currency ') as current bill amount, Max (Payment. paydate) as repayment time, IIf (Max (Payment. paydate) Is Not Null, IIf (Bill. balance-SUM (Payment. balance) <= 0, 'bill settled ', 'partial repayment, loan' & Bill. paydate-date () & 'day'), IIF (Bill. paydate-date ()> 0, Bill. paydate-date () & 'day', 'overdue: '& date ()-Bill. paydate & 'day') as remaining repayment time, Format (SUM (Payment. balance), 'currency ') as total amount of repayment, Format (IIf (Bill. balance-SUM (Payment. balance) <= 0, 0, Bill. balance-SUM (Payment. balance), 'currency ') as unpaid amount, IIf (Bill. balance-SUM (Payment. balance) <= 0, '√ ', '') as the Bill is completed FROM (Credit inner join Bill ON Credit. CCno = Bill. CCno) left join Payment ON Bill. BLno = Payment. BLnoGROUP BY Bill. ID, Bill. CCno, Credit. bank, Credit. card, Bill. BLno, Bill. billdate, Bill. paydate, Bill. balanceHAVING (Year (Bill. billdate) = '20140901' AND Month (Bill. billdate) = '12') order by Bill. ID Desc

In the Select statement, Month (time field) is used to obtain the Month, and year (time field) and week () are used to obtain the relevant time information; use Max and Min to obtain the maximum or minimum value in a field (Column). The value can be a number or a time type.

This Select statement involves an if statement. The sentence: IIF (field or expression, the expression is a true result, and the expression is a false result), and the expression can be nested. Parse the if statement in the "remaining repayment time" field above.

IIf (Max (Payment. paydate) Is Not Null, IIf (Bill. balance-SUM (Payment. balance) <= 0, 'bill settled ', 'partial repayment, loan' & Bill. paydate-date () & 'day'), IIF (Bill. paydate-date ()> 0, Bill. paydate-date () & 'day', 'overdue: '& date ()-Bill. paydate & 'day') as remaining repayment time

The first if layer (red), IIf (Max (Payment. paydate) Is Not Null, the expression Is a real result, and the expression Is a false result). When the Paydate in the [Payment] table Is Not a Null value (blue ), that is, there is a Bill for a month in the [Bill] Table. However, if the Bill has a value (not a null value) in the [Payment] Table, the "expression is the real result" is executed. if this credit card bill for a month has no repayment record, that is, the repayment time is Null, then execute "the expression is false".

So what are the results of these two true and false expressions? Let's look at the next if layer (purple), IIf (Bill. balance-SUM (Payment. balance) <= 0, 'bill settled ', 'partial repayment, loan' & Bill. paydate-date () & 'day '). That is to say, there are already repayment records, so there are two statuses, that is, the total amount of repayment for a month's bill is paid back at one time, and only part of the money is paid back, and the remaining part is not yet paid back. From the expression, the bill amount-the total amount of the n-times of repayment is less than or equal to 0, that is, the total amount has been completely paid (you can pay back the money more, because most of the time there will be a card swipe next month, there must be a Bill), then it will show "settle the bill"; if the bill amount-the total amount of n times of repayment> 0, that is, you can pay back N times, if you have not completed the payment (if you owe too much money, you have to pay off the service several times), then you will see "partial payment, n days remaining".

There is also a result (green) with the first if statement expression as false, IIF (Bill. paydate-date ()> 0, Bill. paydate-date () & 'day', 'overdue: '& date ()-Bill. paydate & 'day '). That is, the final repayment time-the current date> 0, then calculate the final repayment time-the number of days of the current date; otherwise, the payment is overdue if the value is less than or equal to 0 (because the payment is made on the last day, not all banks can be recorded as payers and will be recorded on the next day). the overdue period is N days.

We will continue to parse the links of the three tables tomorrow. How can we use or select left join, inner join, and right join ), their differences and combinations.