SQL Server: INSERT new records INTO a table SELECT FROM multiple tables with WHERE conditions

We'll be working with three tables in this post.

Supposed you have:

1. [Credit] table
2. [Invoice] table
3. [Shipment] table 

[Credit] table

[Shipment] table

[Invoice] table

And now you want to add new record based on multiple tables with certain conditions applied. The barebone syntax is going to be something like this:

The query goes as such:

INSERT INTO [Credit] (InvoiceId, Description, Amount, Date, LastUpdateBy)

-- adding item into Credit table, columns: InvoiceId, Description, Amount, Date, and LastUpdateBy

SELECT [Invoice].InvoiceId, 'Discount Adjustment', ROUND(([Shipment].[Cost] * 0.10), 2), '2016-12-06', 8

-- from Invoice table with matching columns: [Invoice].InvoiceId, a constant text of 'Discount Adjustment', a simple

-- calculation: 10% discount of [Shipment].Cost, a constant date text of '2016-12-06', and a constant integer of 8

FROM [Shipment] INNER JOIN [Invoice] ON [Shipment].InvoiceId = [Invoice].InvoiceId

-- inner join the two tables: [Shipment] and [Invoice] with the matching value InvoiceId

WHERE [Invoice].CustomerId = 1205 AND [Invoice].Outstanding > 0.00 AND [Invoice].Credits = 0.00

-- and the where conditions: a specific CustomerId, Invoice Outstanding greater than 0, Credits equals to 0

This will query specific records from two tables: [Shipment] and [Invoice] based on the where conditions filter and then insert them into the [Credit] table.

Air Waybill Sequence with VBA

Air Waybill Number is structured into 3 parts with total of 11 digits.

• The first 3-digit is Airline Prefix which is unique for each airline
• The next 7-digit is the Air Waybill's Serial Number, and
• The last digit is the Check digit, which calculated by dividing 7-digit Serial Number by 7 and the reminder determines the Check digit e.g.: 1234567 divided by 7 is 176366 with reminder 5, hence the Serial Number + Check Digit is 1234567-5.

Since the Serial Number is divided by a constant 7, the remainder will always be a number fewer than 7 or any number between 0 and 6. Using Microsoft Access Database for this experiment, the VBA Script would be like this:

If Right(DMax("fieldName", "tableName"), 1) = 6 Then

    Me.AWBNumber = DMax("fieldName", "tableName") + 4

Else

    Me.AWBNumber = DMax("fieldName", "tableName") + 11

End If

The first If condition, look for an Air Waybill field name where you store its numbers, get a maximum value from a table, extract the last digit to the right, if it is 6 which is the maximum digit number it can be for a check digit, then the next Air Waybill number should be the maximum Air Waybill number added by 4. It will the reset the last digit to 0.

Else, it should be the maximum Air Waybill number added by 11.

As the Air Waybill number increases with this routine, it will always loop back to last digit of 0.