Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    3

    Unanswered: Need help with creating an Access query.

    I am attempting to create an Access query that will return a view with the following columns:

    JobID |SupplierName |JobStatus |TotalOrders |TotalBoxes |TotalProducts

    Each of the queries below work fine individually, but I need to combine them all into one SQL statement that I can use as a source for a Crystal Report. I want to be able to select a JobID from a combo box from VB6 and then pass this JobID to my Crystal Report.


    SELECT tblJobs.JobID, tblSuppliers.SupplierName, tblJobs.JobStatus
    FROM tblJobs INNER JOIN tblSuppliers ON tblJobs.SupplierID = tblSuppliers.SupplierID
    WHERE JobID = ?

    SELECT Count(JobID) AS TotalOrders
    FROM tblOrders
    WHERE JobID=?

    SELECT Count(JobID) AS TotalBoxes
    FROM tblBoxes
    WHERE JobID = ?

    SELECT SUM(Quantity) AS TotalProducts
    FROM tblBoxes
    WHERE JobID = ?

    Any suggestions would be greatly appreciated!

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109

    suggestion

    Code:
    SELECT tblJobs.JobID, tblSuppliers.SupplierName, tblJobs.JobStatus, 
    	(SELECT Count(JobID) AS TotalOrders
    	FROM tblOrders
    	WHERE JobID=?),
    	(SELECT Count(JobID) AS TotalBoxes
    	FROM tblBoxes
    	WHERE JobID = ?),
    	(SELECT SUM(Quantity) AS TotalProducts
    	FROM tblBoxes
    	WHERE JobID = ?)
    
    FROM tblJobs INNER JOIN tblSuppliers ON tblJobs.SupplierID = tblSuppliers.SupplierID
    WHERE JobID = ?
    That should work, make sure you replace the ? by a number, and you could add a GROUP BY at the end of the query, actually, you could remove nested query and use links and group by to return the same result

    JefB - hope it helps

Posting Permissions

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