Creating duplicate lines? 1

[SeeThru]

Odd one for all the SQL specialists....

I have a table like this

Name Address NumberLetters


I need to create a Query for a mail merge that shows one row per letter required. For instance, the follwoing data:

Mr Smith 1 The Street 5

Would create tho following query for merging

Mr Smith 1 The Street
Mr Smith 1 The Street
Mr Smith 1 The Street
Mr Smith 1 The Street
Mr Smith 1 The Street

Any ideas?

The number of letters is quite variable - ideally, the elegant solution would accomodate infinity-1 number of letters...

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[Golom]

I think you're pretty much stuck with code.

Sub SomeSub()
BuildMailMerge "Mr Smith", "1 The Street", 5
BuildMailMerge "Mrs Jones", "2 The Avenue", 3
[COLOR=black cyan]' etc.[/color]
End Sub


Public Sub BuildMailMerge (Customer As String, _
                           Address As String, _
                           Copies As Long)

Dim SQL As String
Dim n   As Long

SQL = "INSERT INTO mmTable (Customer, Address) " & _
      "VALUES ( '" & Customer & "', '" & Address & "')"

For n = 1 To Copies
   CurrentDb.Execute SQL
Next n

Exit Sub

Then use "mmTable" as the recordsource for your mail merge.

Can't quite accommodate infinity - 1 ...
Will 9,223,372,036,854,775,807 be enough?

 

[PHV]

Create a table of numbers and then:
SELECT A.Name, A.Address
FROM yourTable AS A INNER JOIN tblNumbers AS B ON A.NumberLetters <= B.Number

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Nice PH- I'll try that - if not I'll have to build a temporary table - they are such a pain due to the bloat they create.

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk