SQL Help

Results 1 to 2 of 2

Thread: SQL Help

  1. #1
    Join Date
    Dec 1969

    Default SQL Help

    I&#039;m trying to link two fields in two different tables. For every project there is a project name and a project ID. There are many projects for each projectID, but only one projectID for each project. I want to find the sum of the miles billed for each projectID, which will be selected through a drop down box. Problem is the projectID is not a field in the table that has all the billing information in it, but the Project Name is in the table. We have another table that relates ProjectID to a corresponding Project Name. The following is the sql that I have come up with thus far and have manipulated for quite some time with no success. Any ideas would be greatly appreciated.<BR><BR>strSQL= "SELECT AGGREGATEDRAFTING.PROJECT, tblInvoiceDetails.PROJECTID, Sum(BILLMILES) AS summiles From AGGREGATEDRAFTING INNER JOIN tblInvoiceDetails on AGGREGATEDRAFTING.PROJECT = tblInvoiceDetails.PROJECTID WHERE (BILLDATE BETWEEN #" &Request.Form("txtFirstDate") & _<BR>"# AND #" & Request.Form("txtSecondDate") & "#) AND (PROJECTID = &#039;" & Request.Form("lstCustomer") & "&#039;) group by PROJECTID HAVING (Sum(BillMiles)&#062;0)"

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL Help

    Well<BR><BR>&#062;&#062;There are many projects for each projectID, but only one &#062;&#062;projectID for each project<BR>Not sure what this means.. aren&#039;t you negating what you say?<BR><BR>you have many projects, each has its own id<BR>and there is one id per project. (clarify if wrong)<BR><BR>so you want to get the sum of the miles for a project.<BR><BR>1st, who was so off the rock that they used project name for a row identifier to the project?<BR><BR>select sum(miles) from billing b inner join projectnametable p on b.projname = p.projname where b.projname=&#039;Akhileshseducation&#039; and date = &#039;01/30/01&#039;<BR><BR>is that what you want?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts