torys
asked on
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
FROM Leads LEFT JOIN Estimates ON Leads.LeadID = Estimates.LeadID;
Thanks in advance for the help.
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
FROM Leads LEFT JOIN Estimates ON Leads.LeadID = Estimates.LeadID;
Thanks in advance for the help.
Phatzer
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,....
use the DISTINCT keyword to eliminate duplicate listings, ie
SELECT DISTINCT Leads.LeadID, Leads.FirstName, Leads.LastName, Leads.Address, Leads.City,....
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
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.