• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • Last Modified:

Filtering a recordset with an advanced SQL query

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.
1 Solution
Have you tried a RIGHT JOIN instead? Not sure whether that'll make any difference, but I guess it may be worth a try.
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,....
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
torysAuthor Commented:
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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now