How do I write my SQL Code in MS Access SQL?

Table Name: Records

ID	Date	Time	Status	BoxType	Material	Rack	EmployeeNr	Transaction
1	25-05-2020	13:11:12	70	36757	4	4	188	2
2	25-05-2020	13:12:40	71	31178	2	5	188	2
3	25-05-2020	13:13:31	71	31178	2	5	188	102
4	27-05-2020	13:14:14	71	38444	3	2	181	3
5	27-05-2020	13:14:15	71	38444	3	2	181	3
6	27-05-2020	13:14:41	71	38444	3	2	181	3
7	27-05-2020	13:15:10	71	39123	6	5	188	3
8	27-05-2020	13:15:51	71	38444	3	2	181	103
9	27-05-2020	13:16:51	71	38444	3	2	181	103

The Desired OUTPUT I want is Rows 1, 6 and 7

ID	Date	Time	Status	BoxType	Material	Rack	EmployeeNr	Transaction
1	25-05-2020	13:11:12	70	36757	4	4	188	2
6	27-05-2020	13:14:41	71	38444	3	2	181	3
7	27-05-2020	13:15:10	71	39123	6	5	188	3

Hello dear developer friends,

I have this table “Records“. There are two types of transaction viz. ‘Forward’ and ‘Reverse’. The Forward transactions are represented by single to two digits of numbers (that is from 1 to 30) and Reverse transactions are represented by three digits (that is from 100 to 130). Now, every unique Forward transaction has a unique reverse transaction. (For eg. Transaction 1 will have 101, transaction 2 will have 102 and transaction 3 will have 103 and so on).

I do not want to see the Reverse transactions and as well as their forward transactions in my report. (For eg. 102 and 2, 103 and 3) But, I have to find the matching forward transactions whose other columns data (Date, Status,BoxType, Material, Rack,EmployeeNr) are same of Reverse transaction. (As seen in the image, record no. 2 and 3) Also, there can be more than one matches of forward transactions for reverse transactions, and I want to skip only those many forward transactions equal to number of Reverse transactions. (*As seen in the table, record no. 4,5 and 6 matches to record no. 8 and 9, so I want to hide two records no. 4 and 5 with 8 and 9)

So, I tried to write a SQL query, but it is not working in MS access. Appreciate your great help! Thank you! Best Regards.

My code

with table_a as (
  select a.*, row_number() over (partitition by Status, BoxType, Material, Rack, EmployeeNr , Transaction) as dup_n --all columns except ID and time.
  ---for multiple entries of same type
  from Records a
  where Transaction<100
  )
   ,table_b as (
  select b.*, (Transaction-100) as type_new,
         row_number() over (partitition by Status, BoxType, Material, Rack, EmployeeNr , Transaction) as dup_n --all columns except time and ID
         ---for multiple entries of same type
  from Records b
  where Transaction>99
  )
select*from(
  select a.* from table_a
  left join table_b
  on <Status, BoxType, Material, Rack, EmployeeNr , Transaction> --all columns except time and ID
  and a.type = b.type_new
  and a.dup_n = b.dup_n
  )
  where a.Transaction is null

Answer

Stackoverflow is not intended to be a code writing service but since you did make attempt with SQL and I was the one who brought up VBA and I was intrigued by the challenge, here is something to give you a start. First, build a ‘temp’ table – table is permanent but records are temporary – that has same fields as Records table except ID field is not an autonumber, just a number type. Call it RecordsSelected and make sure fields in both tables are in same order in design. Then, build three query objects.

CntRevs:

SELECT [Date], Status, BoxType, Material, Rack, EmployeeNr, Transaction, 
Count(ID) AS CntRev, Val(Mid([Transaction],2)) AS Forward
FROM Records
WHERE (((Records.Transaction)>100))
GROUP BY [Date], Status, BoxType, Material, Rack, EmployeeNr, Transaction, Val(Mid([Transaction],2));

Query1:

SELECT Records.*
FROM Records 
LEFT JOIN (SELECT Records.* FROM Records 
           WHERE (((Records.Transaction)>100))) AS Reverses 
ON (Records.Date = Reverses.Date) 
AND (Records.Status = Reverses.Status) 
AND (Records.BoxType = Reverses.BoxType) 
AND (Records.Material = Reverses.Material) 
AND (Records.Rack = Reverses.Rack) 
AND (Records.EmployeeNr = Reverses.EmployeeNr)
WHERE (((Reverses.Transaction) Is Null));

Query2:

SELECT Records.*, Forward
FROM CntRevs 
INNER JOIN Records ON (CntRevs.EmployeeNr = Records.EmployeeNr) 
AND (CntRevs.Rack = Records.Rack) 
AND (CntRevs.Material = Records.Material) 
AND (CntRevs.BoxType = Records.BoxType) 
AND (CntRevs.Status = Records.Status) 
AND (CntRevs.Date = Records.Date)
AND (CntRevs.Forward = Records.Transaction);

Code in a general module. The result is 3 records identified in your desired output are written to temp table.

Sub Test()
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, db As DAO.Database
Dim x As Integer
Set db = CurrentDb
db.Execute "DELETE FROM RecordsSelected"
db.Execute "INSERT INTO RecordsSelected SELECT * FROM Query1"
Set rs1 = db.OpenRecordset("SELECT * FROM CntRevs")
Set rs3 = db.OpenRecordset("SELECT * FROM RecordsSelected")
Do While Not rs1.EOF
    Set rs2 = db.OpenRecordset("SELECT * FROM Query2 WHERE Transaction = " & rs1!Forward & " ORDER BY ID DESC")
    If Not rs2.EOF Then
        rs2.MoveLast
        rs2.MoveFirst
        If rs2.recordCount > rs1!CntRev Then
            For x = 1 To rs2.recordCount - rs1!CntRev
                With rs3
                .AddNew
                !ID = rs2!ID
                !Date = rs2!Date
                !Time = rs2!Time
                !Status = rs2!Status
                !BoxType = rs2!BoxType
                !Material = rs2!Material
                !Rack = rs2!Rack
                !EmployeeNr = rs2!EmployeeNr
                !Transaction = rs2!Transaction
                .Update
                End With
                rs2.MoveNext
            Next
        End If
    End If
    rs1.MoveNext
Loop
End Sub