Divide Output from Query

[juneau730]

Morning all,

Not sure if what I am attempting to do, is doable or not, but I wasn't able to find anything.

I have a query that pulls 3 columns from the master table. Depending on what department I pull the data from, the results quantity varies.

What I would like to be able to do, if possible, is split the data into 3rds. Basically I would have 3 separate forms created from this query, with a 3rd of the data from the master table on each form.

 

[Joe4]

That can get a little tricky, and might even require VBA.
I think it would be helpful to understand the reason and what you are ultimately trying to accomplish. There may be other ways to get what you need.

So, why do you need the results broken up into three queries?
How exact does it have to be? Do all three need to be exactly the same size, if possible, or is it really just an approximation to keep each list from getting too large?

 

[juneau730]

That can get a little tricky, and might even require VBA.
I think it would be helpful to understand the reason and what you are ultimately trying to accomplish. There may be other ways to get what you need.

So, why do you need the results broken up into three queries?
How exact does it have to be? Do all three need to be exactly the same size, if possible, or is it really just an approximation to keep each list from getting too large?

The reason we are looking to break it into 3rds, is to automate (as much as possible) the work. Right now, we take the row count, divide it by 3 and have to C/P each 3rd on to new spreadsheets.

We use this data for our phishing campaign, it has FN, LN and Email on it. The data can range from a few 1000 rows upwards or more than 30k rows. We need it split in 3rds because we send 3 different phishing emails during each exercise.

 

[juneau730]

The reason we are looking to break it into 3rds, is to automate (as much as possible) the work. Right now, we take the row count, divide it by 3 and have to C/P each 3rd on to new spreadsheets.

We use this data for our phishing campaign, it has FN, LN and Email on it. The data can range from a few 1000 rows upwards or more than 30k rows. We need it split in 3rds because we send 3 different phishing emails during each exercise.

If it helps at all, here is the query as it is right now.

SELECT [tbl AD User Accounts].Email, [tbl AD User Accounts].[Last Name], [tbl AD User Accounts].[First Name]
FROM [tbl AD User Accounts]
WHERE ((([tbl AD User Accounts].[Distinguished Name]) Like "*Users*") AND (([tbl AD User Accounts].Disabled)="No"))
ORDER BY Rnd() DESC;

 

[Joe4]

We use this data for our phishing campaign, it has FN, LN and Email on it. The data can range from a few 1000 rows upwards or more than 30k rows. We need it split in 3rds because we send 3 different phishing emails during each exercise.

What does the data set you are splitting into 3rd looks like?
What kind of data does it contain?

I am just wondering if there is an opportunity to leverage the data already there. For example, if person's name is included, maybe you can split based on their names.
It wouldn't be an exact equal split, but you could get something in the ball park.

 

[juneau730]

What does the data set you are splitting into 3rd looks like?
What kind of data does it contain?

I am just wondering if there is an opportunity to leverage the data already there. For example, if person's name is included, maybe you can split based on their names.
It wouldn't be an exact equal split, but you could get something in the ball park.

It's just First Name, Last Name and Email in three columns, for all staff in a specific division, say HR. So, if HR had 1000 staff, it would be 1000 rows with 3 columns.
Can't split it just by names, at say a specific letter in the last name, because the data is randomized, to prevent any alphabetical ordering.

 

[Joe4]

So, are you saying that not only does it need to be split three ways, but it has to be split three ways RANDOMLY?
If so, I definitely think you are going to need some VBA code to do this. It could get a little complicated.

Maybe something like this would help:

though I still think you are going to need more steps, such as:
- adding a field to your data table that you can update with which one of the three sets the record would be found in
- adding VBA code if you want to automate it and not have to manually update the SQL code each time to account for the number of records at that time

Quite frankly, you may be better off dumping the data to Excel, and splitting it there, if these really need to be randomized.

 

[juneau730]

So, are you saying that not only does it need to be split three ways, but it has to be split three ways RANDOMLY?
If so, I definitely think you are going to need some VBA code to do this. It could get a little complicated.

Maybe something like this would help:

though I still think you are going to need more steps, such as:
- adding a field to your data table that you can update with which one of the three sets the record would be found in
- adding VBA code if you want to automate it and not have to manually update the SQL code each time to account for the number of records at that time

Quite frankly, you may be better off dumping the data to Excel, and splitting it there, if these really need to be randomized.

Correct, it would be great if we could get it split in 3rds, randomized.

The single form/query output is already being randomized, with the ORDER BY Rnd() DESC; string of code in the query. So really, we would just need to find a means to split/divide it in to 3rds.

 

[XPS35]

Maybe it is possible to add a field ("assigned to") to the table underlying the query. It is relatively simple to write a routine that assigns 1, 2 or 3 to all not assigned records.

 

[Joe4]

Maybe it is possible to add a field ("assigned to") to the table underlying the query. It is relatively simple to write a routine that assigns 1, 2 or 3 to all not assigned records.

That would assign them randomly, and in equal numbers?
If it is simple, then please provide some code!