CODEWITHSUNDEEP
sqlms-accessvba
KEOC
KEOC

Reputation: 11

Access - How can i split the table records in two queries

I have a table "Products" with n records(more than 5000 and always change the number). How can i generate two queries based on table, one for the first half of records and the second with the other half?

Upvotes: 1

Views: 939

Answers (3)

Parfait
Parfait

Reputation: 107652

Consider the pure SQL solution using the row number count aggregate subquery:

TOP HALF

SELECT [ID], [Category], [Product], [Price]
FROM Products
INNER JOIN 
   (SELECT [ID], 
           (SELECT Count(*) FROM Products t2 
            WHERE Products.[ID] <= t2.[ID]) AS ProductOrder, 
           (SELECT Count(*) FROM Products t2) AS ProductCount
    FROM Products) AS sub 
ON Products.[ID] = sub.[ID]
WHERE sub.ProductOrder >= sub.ProductCount / 2

BOTTOM HALF

SELECT [ID], [Category], [Product], [Price]
FROM Products
INNER JOIN 
   (SELECT [ID], 
           (SELECT Count(*) FROM Products t2 
            WHERE Products.[ID] <= t2.[ID]) AS ProductOrder, 
           (SELECT Count(*) FROM Products t2) AS ProductCount
    FROM Products) AS sub 
ON Products.[ID] = sub.[ID]
WHERE sub.ProductOrder <= sub.ProductCount / 2

Upvotes: 0

Thomas G
Thomas G

Reputation: 10216

The issue is that you cant specify the result of a COUNT(*) subquery in the TOP instruction, so impossible to do this 100% reliably in pure SQL

The reliable alternative is to pre-build your 2 queries with VBA :

Sub Half_Table()

    Dim lngTotal As Long
    Dim lngHalf1 As Long
    Dim lngHalf2 As Long

    Dim strTable As String
    Dim strIDfield As String
    Dim strSQL1 As String
    Dim strSQL2 As String


    strTable = "table_Name"
    strIDfield = "ID"

    lngTotal = DCount("*", strTable)
    lngHalf1 = Round(lngTotal / 2)
    lngHalf2 = lngTotal - lngHalf1

    strSQL1 = "SELECT TOP " & lngHalf1 & " * FROM " & strTable & " ORDER BY " & strIDfield & " ASC"
    strSQL2 = "SELECT TOP " & lngHalf2 & " * FROM " & strTable & " ORDER BY " & strIDfield & " DESC"

    Debug.Print strSQL1
    Debug.Print strSQL2


End Sub

Change strTable and strIDfield names accordingly.

This generates the 2 queries below with a table containing 1399 records :

SELECT TOP 700 * FROM table_Name ORDER BY ID ASC
SELECT TOP 699 * FROM table_Name ORDER BY ID DESC

I have not shown how to use the generated SQL queries afterwards as I assume you know that

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Using an AutoNumber primary key (or at least a field that has sequential numbering with no numbers missing) this may work - although I haven't really thought it through so may be a few pitfalls?

Return the first half of ID numbers:

SELECT  ID
FROM    Table1
WHERE   ID<=INT((SELECT MAX(ID) FROM Table1)/2)

Return the second half of ID numbers:

SELECT  ID
FROM    Table1
WHERE   ID>INT((SELECT MAX(ID) FROM Table1)/2)

Upvotes: 1

Related Questions