Group every x records

[Acke]

I need to create a query that groups records at certain interval, every x records. How can this be done?

Tks...

 

[vbaInet]

Acke give us an example of what you're talking about.

 

[Acke]

I have table with customer information that includes email addresses. I want to use that information to send emails to number of addresses at once. Due to ISP restriction, only certain amount of emails can be sent per x time. Therefore, I must send emails at groups of 3 for example. I need to group all records at groups of x records (3 for example).

The way I see it, it would be as follows:

record 1 group 1
record 2 group 1
record 3 group 1
record 4 group 2
record 5 group 2
record 6 group 2
record 7 group 3
...

I need a query that would assign a value at certain record interval and that way make grouping possible.

 

[vbaInet]

I don't know what your records look like. What I would like to see is:

1. Current records ( a short list)
2. How you would like it to be.

 

[Acke]

Currently the table is empty as in production faze. As I have only several records now, presenting actual table would not be helpful.

Once table is filled with data, it would have between 500-1000 records. In case it is 500 records and I want to have interval at every 5 records, I would need to have 100 groups. In case of 600 records at 5 rec. interval, I should have 120 groups.

Table contents:
ID
CustomerName
CompanyName
blah, blah...
Group

I would like to make a query that would add a value to filed "Group" at predefined record interval. If predefined record interval were to be 5, the query should add a value 1 in the field "Group" for first five records, value 2 for records 6 - 10, value 3 for records 11-15...

I want to add increasing numbers to field "Group" at every (for example) five records.

I hope this clarifies now.

 

[vbaInet]

Have a look into the Partition() function:

http://msdn.microsoft.com/en-us/library/Aa445092

 

[Acke]

Tks vbaInet! It looks as if this could be the solution.

Unfortunately, I get "Data type mismatch in criteria expression" error message.

SELECT DISTINCTROW Partition([NazivFirme], 0, 50, 5) AS Range, 
Count(Klijenti.NazivFirme) AS [Count]
FROM Klijenti
GROUP BY Partition([NazivFirme], 0, 50, 5);

Table name: Klijenti
Field name: NazivFirme
Field format: text

What am I doing wrong?

 

[vbaInet]

Text is what you are doing wrong Acke

That field contains numbers so the data type should be ...?

 

[Acke]

That field contains numbers so the data type should be ...?

...technicalities

Step up, but still not solution...

The table will contain several hundreds of email addresses which should be sent 5 at a time. I was hoping to group all records from the table at groups of five and to assign increment value to each group. Records 1-5 group 1, rec. 6-10 group 2, rec 11-15 group 3....

I would later send email with first five addresses from group 1, then another email for 5 addresses from group 2... automatically.

For that I would need groups to have increment number of groups.

How is it possible to assign increment value to groups of 5 records?

Alternatively, maybe you may have some better idea on how to send emails at groups of five, as that is what I need at the end...

 

[vbaInet]

The Partition() function is what you would use to create groups of x.

What was wrong with it?

 

[spikepl]

What exactly is 5 mails at a time? Five separate emails, or one mail to five recipients? And when is the next 5 to be sent? Why not just count how far you got in the sending business?

 

[Acke]

I don't know how to filter it later... The plan was to make another query that will filter just one group with 5 rec. The loop would then go group by group and send mail by mail.

With partition, I get groups and that is very nice. But, I don't know how to filter just one group. It returns 1:5, 6:10... If I copy the info I see in the field as criteria, it does not work.

 

[vbaInet]

You need to extract the left part of what is returned, i.e. the number before ":". Look into the Mid() and Instr() functions.

 

[Acke]

Would you be so kind to give me quick solution on this one. I am not that familiar with functions you mention and having a time pressure. I would very much appreciate if you could provide actual example for this case.

SELECT DISTINCTROW Partition([broj],1,500,5) AS Range, Count(Test.broj) AS [Count], Test.nazivfirme
FROM Test
GROUP BY Partition([broj],1,500,5), Test.NazivFirme;

What should be added to the code to get return values as needed?

 

[vbaInet]

So test this in a new column in your query:

trim(mid([broj], 1, Instr(1, [broj], ":") - 1))

 

[Acke]

Error message

The expression you entered contains invalid syntax.
You omitted operand or operator, you entered invalid character or coma, or you entered text without surrounding it with quotation marks.

I attached the file with query and table.

 

[vbaInet]

Oops... that was supposed to be based on the Partition field, not on broj.

 

[vbaInet]

Attached.

Hint: The end of Partitition must be >= the max value of the [broj] field.

 

[Acke]

Thank you my friend!!! That is what I needed!

 

[vbaInet]

No problemo!

Note: If the max of [bloj] goes above 1000 the last few records won't be grouped in 5s so you can consider using DMax() in the end argument.