Combo Box update based on query

[Parariddle]

Hi Guys

I have a combo box based on a query which has these fields

Instance ID
Report ID
Report Name
Due Date

What I want is the first combo box too bring back a list of report names and the second to return the Due Date for that particular report based on the selection.

My problem is I am finding that all instances of the report name are listed (so 2 reports listed many times) and all dates are then shown.

Any ideas how I can stop this or the best way to achieve this ? Thanks in advance !

 

[missinglinq]

It sounds like you should be using SELECT DISTINCT in your SQL for the query instead of plain old SELECT.

This should only return one instance of each report.

 

[Parariddle]

Thanks Missingling

I put that into the query and have refreshed everything but it doesnt not work. I forgot to mention report name is drawn from another table via a link and not sure if that would make a difference ?

 

[CheapEmployerNoSQL]

is there a join in the query, you may need to use a left outer join and not an inner join

 

[Parariddle]

Thanks for the reply cheap but still not getting the desired result and am still getting multiple entrys for the report name unless I have the dates unshown but I want those to be returned in the following combo box.

 

[shez]

post your query? & relationship diagram if you can

 

[Parariddle]

SELECT DISTINCT tblReportInstances.InstanceID, tblReports.ReportID, tblReports.ReportName, tblReportInstances.DueDate
FROM tblReports LEFT JOIN tblReportInstances ON tblReports.ReportID = tblReportInstances.ReportID
GROUP BY tblReportInstances.InstanceID, tblReports.ReportID, tblReports.ReportName, tblReportInstances.DueDate;

Is the query in SQL view if that helps

 

[shez]

i am just wondering why would you have multiple report names in the first place? I think you have something like:

reportId, reportName,
1, 1stReport
2, 1stReport

reportId, dueDate
1, 01/01/2009
2, 02/02/2009

in this case you should be getting two report name?

 

[Parariddle]

Its based on a table that produces all reports for a year so staff can go in against them and tick them off once they are complete