Filtering a recordset with an advanced SQL query

Posted on 2007-09-30
Last Modified: 2012-06-21
Using Dreamweaver and Access, I have a details page that uses an inner join that ties in two tables (a Leads and Estimates table).
The page preceding the details page is a master page where I click a specific record passing the primary key of that record (LeadID). When I click the link however, it always show me the same record and not the unique record.

I believe I need to build the detail page in a way so it filters on the proper record, but because of the advanced SQL I am not sure how to do that: (normally I'd use Simple mode and filter on the LeadID for the details page).

The SQL query for the details page containing the inner jion is as follows:

SELECT Leads.LeadID, Leads.FirstName, Leads.LastName, Leads.Address, Leads.City, Leads.StateOrProvince, Leads.ZipCode, Leads.Phone, Leads.Contractor, Leads.InstallDate, Estimates.EstimateNumber, Estimates.EstScheduledDate, Estimates.EstTime, Estimates.EstTotal, Estimates.EstAccepted, Estimates.InvoiceTotal, Estimates.PaidFull, Estimates.Notes, Estimates.cardtype, Leads.Removed, Leads.RemovalDate, Leads.Status, Estimates.guarantee, Estimates.InitAmtOwed, Estimates.InitAmtPaid, Estimates.InitAmtCheckNum, Estimates.RemovalAmtOwed, Estimates.RemovalAmtPaid, Estimates.RemovalAmtCheckNum
FROM Leads LEFT JOIN Estimates ON Leads.LeadID = Estimates.LeadID;

Thanks in advance for the help.
Question by:torys
    LVL 10

    Expert Comment

    Have you tried a RIGHT JOIN instead? Not sure whether that'll make any difference, but I guess it may be worth a try.
    LVL 19

    Expert Comment

    your select statement is probably returning multiple rows per record (lead ID) as it probably has 1 to many relationship to that other table.
    use the DISTINCT keyword to eliminate duplicate listings, ie
    SELECT DISTINCT Leads.LeadID, Leads.FirstName, Leads.LastName, Leads.Address, Leads.City,....
    LVL 27

    Accepted Solution

    Your SQL will return multiple rows, one for each lead. If there's no filtering in the detail page it will always show the first row in the details form.
    What you would need is to change the query in the detail form to select on the LeadID that you're passing as a parameter.

    FROM Leads LEFT JOIN Estimates ON Leads.LeadID = Estimates.LeadID
    Where Leads.LeadID = pLeadID;

    I'm not familiar enough with Dreamweaver to show how to code this in detail.

    Hope this helps

    Author Comment

    The proposed solution is correct.
    I took a blend of Dreamweaver's easy mode where it properly filtered on the correct record (the dim MMColParam attribute) and then added the customized SQL query containing the inner join to DW's SQL and it worked.  Thanks for the help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now