Difficult Select Query

[Dana F. Brewer]

I have a table with fields: ID, UserName, StrAddress, City and Zip, sorted by
Zip. I want to select the top 25 records (easy to do) and export to a CSV
text file, then select the next 25 records and export, and so on. I will be
happy with just how to continue the selections in groups of 25 until all
records have been selected. If I have to manually create the text file, so
be it.

Any help with be GREATLY appreciated.

....Dana

 

[KARL DEWEY]

UNTESTED - May need to change >= to <= and maybe remove the first
+1 in the statement.
SELECT Q.ID, Q.UserName, Q.StrAddress, Q.City, Q.Zip, (((SELECT COUNT(*)
FROM [YourTable] Q1
WHERE Q1.[Zip] >= Q.[Zip])+1)\25)+1 AS [25_GROUP]
FROM [YourTable] AS Q
ORDER BY Q.Zip;

 

[Dana F. Brewer]

Well Karl, I tried to understand and follow your logic here. This is what I
tried but I can't get it to run (get an error that select statement uses a
reserved word...). I'm sure I'm not understanding your Q and Q1, etc.. What
am I missing?

********************************
SELECT q.ImportID, q.BName, q.Address, q.Address2, q, Zip, q.Latitude,
q.Longitude, (((SELECT COUNT(*)
FROM Belmont AS Q1
WHERE Q1.[Latitude] >= Q.[Latitude])+1)\25)+1 AS GRPof25)
FROM Belmont AS Q
ORDER BY q.Latitude, q.Longitude;
********************************

UNTESTED - May need to change >= to <= and maybe remove the first
+1 in the statement.
SELECT Q.ID, Q.UserName, Q.StrAddress, Q.City, Q.Zip, (((SELECT COUNT(*)
FROM [YourTable] Q1
WHERE Q1.[Zip] >= Q.[Zip])+1)\25)+1 AS [25_GROUP]
FROM [YourTable] AS Q
ORDER BY Q.Zip;

I have a table with fields: ID, UserName, StrAddress, City and Zip, sorted by
Zip. I want to select the top 25 records (easy to do) and export to a CSV
text file, then select the next 25 records and export, and so on. I will be
happy with just how to continue the selections in groups of 25 until all
records have been selected. If I have to manually create the text file, so
be it.

Any help with be GREATLY appreciated.

...Dana

 

[John Spencer]

You should be able to use a query like the following to calculate the
group for your records

SELECT A.ImportID
, 1 + (Count(B.ImportID)\25) as ExportGroup
FROM Belmont as A LEFT JOIN Belmont as B
ON A.ImportID < B.ImportID
GROUP BY A.ImportID

Putting that all together into one query, you would have:

SELECT Belmont.*
FROM Belmont INNER JOIN
(SELECT A.ImportID
, 1 + (Count(B.ImportID)\25) as ExportGroup
FROM Belmont as A LEFT JOIN Belmont as B
ON A.ImportID < B.ImportID
GROUP BY A.ImportID) as S
ON Belmont.ImportID = S.ImportID
WHERE ExportGroup = 1
ORDER BY ExportGroup, Belmont.ImportID

Of course, if you wanted to you could include the ExportGroup field in
the export and export the entire thing into one CSV file.

You can use VBA to loop through the query and export to separate files,
but that is a separate question.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well Karl, I tried to understand and follow your logic here. This is what I
tried but I can't get it to run (get an error that select statement uses a
reserved word...). I'm sure I'm not understanding your Q and Q1, etc.. What
am I missing?

********************************
SELECT q.ImportID, q.BName, q.Address, q.Address2, q, Zip, q.Latitude,
q.Longitude, (((SELECT COUNT(*)
FROM Belmont AS Q1
WHERE Q1.[Latitude] >= Q.[Latitude])+1)\25)+1 AS GRPof25)
FROM Belmont AS Q
ORDER BY q.Latitude, q.Longitude;
********************************

UNTESTED - May need to change >= to <= and maybe remove the first
+1 in the statement.
SELECT Q.ID, Q.UserName, Q.StrAddress, Q.City, Q.Zip, (((SELECT COUNT(*)
FROM [YourTable] Q1
WHERE Q1.[Zip] >= Q.[Zip])+1)\25)+1 AS [25_GROUP]
FROM [YourTable] AS Q
ORDER BY Q.Zip;

I have a table with fields: ID, UserName, StrAddress, City and Zip, sorted by
Zip. I want to select the top 25 records (easy to do) and export to a CSV
text file, then select the next 25 records and export, and so on. I will be
happy with just how to continue the selections in groups of 25 until all
records have been selected. If I have to manually create the text file, so
be it.

Any help with be GREATLY appreciated.

...Dana

 

[KARL DEWEY]

You have a typo in -- q.Address2, q, Zip, q.Latitude,
to be q.Address2, q.Zip, q.Latitude,
or q.Zip

Well Karl, I tried to understand and follow your logic here. This is what I
tried but I can't get it to run (get an error that select statement uses a
reserved word...). I'm sure I'm not understanding your Q and Q1, etc.. What
am I missing?

********************************
SELECT q.ImportID, q.BName, q.Address, q.Address2, q, Zip, q.Latitude,
q.Longitude, (((SELECT COUNT(*)
FROM Belmont AS Q1
WHERE Q1.[Latitude] >= Q.[Latitude])+1)\25)+1 AS GRPof25)
FROM Belmont AS Q
ORDER BY q.Latitude, q.Longitude;
********************************

UNTESTED - May need to change >= to <= and maybe remove the first
+1 in the statement.
SELECT Q.ID, Q.UserName, Q.StrAddress, Q.City, Q.Zip, (((SELECT COUNT(*)
FROM [YourTable] Q1
WHERE Q1.[Zip] >= Q.[Zip])+1)\25)+1 AS [25_GROUP]
FROM [YourTable] AS Q
ORDER BY Q.Zip;

I have a table with fields: ID, UserName, StrAddress, City and Zip, sorted by
Zip. I want to select the top 25 records (easy to do) and export to a CSV
text file, then select the next 25 records and export, and so on. I will be
happy with just how to continue the selections in groups of 25 until all
records have been selected. If I have to manually create the text file, so
be it.

Any help with be GREATLY appreciated.

...Dana