loop qry

By allj ·
Hopeful New Year: Any help is much appreciated.
MS Access - for explanatory purposes- I am running a looped sql from two tables. One is clients and other is provider. The sql loops through the provider info entering client data. At the end of each provider my info is put in before the next loop with a different provider begins. So

With rst
If Not rst.BOF And Not rst.EOF Then
End If
Do Until rst.EOF
dim imp as string
*** what do I put here??? if rst!provider <> then
imp = rst!client & my name and info
imp = rst!client
end if

strcolumn3 = "select rst!provider & imp

sSQL = "INSERT INTO table3 ( column1) " _
& "VALUES('" & strColumn3 & "')"

db.Execute sSQL

cnt = cnt + 1
It should look like this:
client 1
my name and info
client 3
client 4
my name and info
provider 3
client 5

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Try something like this

by Tony Hopkinson In reply to loop qry

Select Column1 From(
Select ProviderName, ProviderName as Column1, 1 as LineNo
From Providers
Select ProviderName, ClientName as Column1, 2 as LineNo From Providers,Clients Where Clients.ProviderID = Providers.ProviderID
Select ProviderName, 'My name and Info' as Column1, 3 as LineNo
From Providers) dummytable
Order By ProviderName, LineNo

Taken liberties with the object names, but should be adaptable.

No loop you'll notice, they are bad habit in SQL databases.

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums