Hi All
I run a query which gets data from two tables.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of the
initator of the record and the email of all the contract dependents to allow
me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON Contractdependents.EMPName =
EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
Regards
Jas
6  1376 
"JP SIngh" <no**@none.com> wrote in message
news:%2******************@tk2msftngp13.phx.gbl... Hi All
I run a query which gets data from two tables.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of the
initator of the record and the email of all the contract dependents to
allow me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON Contractdependents.EMPName
= EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
How 'bout:
SELECT m.ContractId, m.InitiatedBy, e1.EmpName, e1.Email, c.ContractId,
c.EmpName, e2.Email
FROM ((MainContracts AS m INNER JOIN EmpProfile AS e1 ON m.InitiatedBy =
e1.EmpName)
INNER JOIN ContractDependents AS c ON m.ContractId = c.ContractId)
INNER JOIN EmpProfile AS e2 ON c.EmpName = e2.EmpName
WHERE m.ContractId=54207;
Returns one set of data with the initiator's information repeated in each
row and the database does all the work. If there is a possibility of
unmatched records but you still want rows returned use LEFT JOIN and/or
RIGHT JOIN in place of INNER JOIN where appropriate.
But then how will you write the asp code not to repeat sending the email to
the initator repeatedly but only once
"MyndPhlyp" <no****@homeright.now> wrote in message
news:uz**************@TK2MSFTNGP14.phx.gbl...
"JP SIngh" <no**@none.com> wrote in message
news:%2******************@tk2msftngp13.phx.gbl... Hi All
I run a query which gets data from two tables.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of the
initator of the record and the email of all the contract dependents to allow me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON
Contractdependents.EMPName = EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
How 'bout:
SELECT m.ContractId, m.InitiatedBy, e1.EmpName, e1.Email, c.ContractId,
c.EmpName, e2.Email
FROM ((MainContracts AS m INNER JOIN EmpProfile AS e1 ON m.InitiatedBy =
e1.EmpName)
INNER JOIN ContractDependents AS c ON m.ContractId = c.ContractId)
INNER JOIN EmpProfile AS e2 ON c.EmpName = e2.EmpName
WHERE m.ContractId=54207;
Returns one set of data with the initiator's information repeated in each
row and the database does all the work. If there is a possibility of
unmatched records but you still want rows returned use LEFT JOIN and/or
RIGHT JOIN in place of INNER JOIN where appropriate.
JP SIngh wrote: Hi All
I run a query which gets data from two tables.
What database? Type and version, please.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of the
initator of the record and the email of all the contract dependents
to allow me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Hmm, all the parentheses makes this look like the sql created by the Access
Query Builder, so I will assume you are using Access. Please don't make us
guess. :-)
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON
Contractdependents.EMPName = EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
Regards
Jas
It sounds like you need a union query. It could be as simple as this:
SELECT c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
Is the email to the initiator different from the one sent to the dependants?
If so, you will need to include
SELECT 'Initiator' As Source,
c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT 'Dependant' As Source,
c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
ORDER BY Source DESC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Thanks bob
just a follow up on your suggestion
who would I write the look such that the initiator only gets email once and
not everytime.
I mean if there are 6 contract dependents i don't want initator to be
emailed 6 times but only once
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u$*************@TK2MSFTNGP12.phx.gbl... JP SIngh wrote: Hi All
I run a query which gets data from two tables.
What database? Type and version, please.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of the
initator of the record and the email of all the contract dependents
to allow me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Hmm, all the parentheses makes this look like the sql created by the
Access Query Builder, so I will assume you are using Access. Please don't make us
guess. :-)
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON
Contractdependents.EMPName = EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
Regards
Jas
It sounds like you need a union query. It could be as simple as this:
SELECT c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
Is the email to the initiator different from the one sent to the
dependants? If so, you will need to include
SELECT 'Initiator' As Source,
c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT 'Dependant' As Source,
c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
ORDER BY Source DESC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Run my query. The initiator should only be returned once.
JP SIngh wrote: Thanks bob
just a follow up on your suggestion
who would I write the look such that the initiator only gets email
once and not everytime.
I mean if there are 6 contract dependents i don't want initator to be
emailed 6 times but only once
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u$*************@TK2MSFTNGP12.phx.gbl... JP SIngh wrote: Hi All
I run a query which gets data from two tables.
What database? Type and version, please.
I store in MainContracts Table
ContractId
InitiatedBy
In the ContractDependents table I store
ContractId
EmpName
In the empprofile table I store
EmpName
FirstName
LastName
Email
For each contract we have multiple contract dependents.
What I want to do is to write a query to pull the email address of
the initator of the record and the email of all the contract
dependents to allow me to send an email to all of them.
At present I doing this.(just the logic not the actual code)
Get contract initiators email
SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
EMPProfile.email
FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
EMPProfile.EmpName
WHERE (((Contracts.Id)=54207));
Hmm, all the parentheses makes this look like the sql created by the
Access Query Builder, so I will assume you are using Access. Please
don't make us guess. :-)
Send email to the contract initiator
Get the list of all contract dependents and thier email addresses
SELECT Contractdependents.contractnumber,
Contractdependents.EMPName, EMPProfile.EmpName, EMPProfile.email
FROM Contractdependents LEFT JOIN EMPProfile ON
Contractdependents.EMPName = EMPProfile.EmpName
WHERE (((Contractdependents.contractnumber)=54207));
Loop through the above recordset and send email to each dependent.
I feel opening two recordsets are slow and not efficient.
Am I correct? is there a better way of doing this.
Regards
Jas
It sounds like you need a union query. It could be as simple as this:
SELECT c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
Is the email to the initiator different from the one sent to the
dependants? If so, you will need to include
SELECT 'Initiator' As Source,
c.Id, c.initiatedby, e.EmpName,e.email
FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
e.EmpName
WHERE c.Id=54207
UNION
SELECT 'Dependant' As Source,
c.contractnumber, c.EMPName,e.EmpName, e.email
FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
e.EmpName
WHERE c.contractnumber=54207
ORDER BY Source DESC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
"JP SIngh" <no**@none.com> wrote in message
news:eF**************@TK2MSFTNGP12.phx.gbl... But then how will you write the asp code not to repeat sending the email
to the initator repeatedly but only once
A test to see if the initiator's id changed would do the trick ...
strInitiator = ""
rs.Open
Do Until rs.EOF
If rs.Fields("InitiatedBy").Value <> strInitiator Then
' Do one time stuff here
strInitiator = rs.Fields("InitiatedBy").Value
End If
' Do repetitive stuff here
rs.MoveNext
Loop
rs.Close This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: LRW |
last post by:
Well, I have another question that's hard to ask, so I may end up over
explaining the question.
I have about 40 tables, all with a similar column structure. I want to be
able to search through...
|
by: david_0 |
last post by:
I have inherited support for a database with many used tables. There
isn't any documentation on what is used or dead. I was hoping to run
traces and capture the objectid but the property doesn't...
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked...
|
by: Krechting |
last post by:
hi all,
I try to show three linked tables in a treeview.
PIR, SIR and Indicator. The first two are no problem, but I can't
display the third table. What should I change in the code?
Private...
|
by: b b |
last post by:
I created the following code to delete all linked tables in my database
(Access 200):
--------------------------------------------------------
Dim tbl As TableDef
Dim dbs As Database
Set dbs...
|
by: Scamjunk |
last post by:
I have been desperately looking for a treeview-type solution for my
problem
for the past three weeks and have been greatly unsuccessful. I am
totally
new to the world of XSLT and I *don't know*...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
Can I code this somehow. So the code presumabley would loop through all the
tables, open each...
|
by: empiresolutions |
last post by:
Hello Fellow Programmers,
I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked...
|
by: cranfan |
last post by:
I'm using a relink method.It works fine but i have a question about
making it work fast.
If .Tables(tdfRelink.name).Type = "LINK" Then
in this line i put a breakpoint and look for the table...
|
by: gillianbrooks91 |
last post by:
Forgive me for asking this question, I've trawled through nearly every
available post on this subject that I can find for a few weeks now but
nothing quite points me in the right direction.
I'm...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
