Repeating Datasets

[Eric_Dahlenburg]

Hi,

This has been driving me crazy ! I want to display all parts that have the same Manufacture, based on the following query:

<cfquery name="Main" datasource="Parts" username="admin" password="XXXXX">
SELECT Main_Table.Image_File, Main_Table.Part_Number, Contract_Table.Name_of_Contract, Main_Table.Quantity,
Main_Table.Specific_Description, Nomen_Table.Generic_Nomen, MNFR_Table.Manufacture_Name,
Vendor_Table.Vendor_Name, Main_Table.NEMS_Tag, Main_Table.Location
FROM Vendor_Table INNER JOIN (Nomen_Table INNER JOIN (MNFR_Table INNER JOIN (Contract_Table INNER JOIN Main_Table ON Contract_Table.Contract_num = Main_Table.Contract_num) ON MNFR_Table.MNFR_num = Main_Table.MNFR_num) ON Nomen_Table.Nomenclature_ID = Main_Table.Nomen_num) ON Vendor_Table.Vendor_num = Main_Table.Vendor_num
WHERE (((Main_Table.MNFR_num)=#form.mnfr_lst#) AND ((Main_Table.Contract_num)=[Contract_Table]![Contract_num]) AND ((Main_Table.MNFR_num)=[MNFR_Table]![MNFR_num]) AND ((Main_Table.Nomen_num)=[Nomen_Table]![Nomenclature_ID]) AND ((Main_Table.Vendor_num)=[Vendor_Table]![Vendor_num]))</cfquery>

It uses an Access database. (You can see the table relations here: http://spacecoasthomes.net/relation.jpg )



However, When I display the page, I get repeating data. (Check it out on my test server, http://devo.dns2go.com/ )

Select Hewlett Packard as the manufacter. It should return only 3 parts. However, it is repeating the following fields:
Main.Part_Number, Main.Quantity, Main.NEMS_Tag, Main.Specific_Description, and Main.Location.


Here is the table I use to display the data:

<table border="0" cellpadding="15" cellspacing="0" bgcolor="FFFFFF">
<cfoutput query="Main">
<cfif Main.currentrow mod 3 eq 1>
<tr>
</cfif>
<td>
<p><cfoutput><img src="images/#Main.Image_File#" width="150"></cfoutput></p>
<p><span class="style1">Part Number:</span> <cfoutput><span class="style1"><cfoutput>#Main.Part_Number#</cfoutput></span></cfoutput><br />
<span class="style1">Contract:</span> <cfoutput><span class="style1">#Main.Name_of_Contract#</span></cfoutput><br />
<span class="style1">Quantity</span>: <cfoutput><cfoutput><span class="style1">#Main.Quantity#</span></cfoutput></cfoutput><br />
<span class="style1">Specific Description: </span> <cfoutput><cfoutput><span class="style1">#Main.Specific_Description#</span></cfoutput></cfoutput><br />
<span class="style1">Nomenclature:</span> <cfoutput><span class="style1">#Main.Generic_Nomen#</span></cfoutput><br />
<span class="style1">Manufacturer:</span> <cfoutput><span class="style1">#Main.Manufacture_Name#</span></cfoutput><br />
<span class="style1">Primary Vendor: </span> <cfoutput><span class="style1">#Main.Vendor_Name#</span></cfoutput><br />
<span class="style1">NEMS Tag: </span> <cfoutput><cfoutput><span class="style1">#Main.NEMS_Tag#</span></cfoutput></cfoutput><br />
<span class="style1">Location (Cage/Row): <cfoutput><cfoutput>#Main.Location#</cfoutput></cfoutput></span> <br />
</p></td>
<cfif Main.currentrow mod 3 eq 0>
</tr>
</cfif>
</cfoutput>
</table>

I know the dataset is correct as I tested the dataset return (using <cfdump var="#Main#"> ) That verifies the correct data (You can see it here:
http://spacecoasthomes.net/dataset.jpg )

I have tried many different forms of the same query, from nested queries and Joins. Any help would be grealty appreciated!

Eric

[Dan_Bracuk]

This part,
AND ((Main_Table.Contract_num)=[Contract_Table]![Contract_num]) AND ((Main_Table.MNFR_num)=[MNFR_Table]![MNFR_num]) AND ((Main_Table.Nomen_num)=[Nomen_Table]![Nomenclature_ID]) AND ((Main_Table.Vendor_num)=[Vendor_Table]![Vendor_num]))

seems to be redundant because it is a repeat of what is in your from clause.

If you change this:
FROM Vendor_Table INNER JOIN (Nomen_Table INNER JOIN (MNFR_Table INNER JOIN (Contract_Table INNER JOIN Main_Table ON Contract_Table.Contract_num = Main_Table.Contract_num) ON MNFR_Table.MNFR_num = Main_Table.MNFR_num) ON Nomen_Table.Nomenclature_ID = Main_Table.Nomen_num) ON Vendor_Table.Vendor_num = Main_Table.Vendor_num

to

Main_Vendor_Table mvt inner join Vendor_Table vt on mvt.vendor_num= vt.vendor_num
etc

your sql will be easier to follow.

Also, to make sure it's your query and not your output code, use cfdump to view the results. Once you know you are getting the right data, you can worry about outputting it properly.

[Eric_Dahlenburg]

Thanks for the reply!

I am pretty sure it isn't in the query part of my code. Checkout the output using <cfdump var="#Main#"> (See it here: http://spacecoasthomes.net/dataset.jpg )

If you look at the test server at http://devo.dns2go.com and use any of the manufactures from the drop down that return more than one result (Default MNFR, Hewlett Packard, Test MNFR#4) it gives good dataset return( All records with no repeats), but I think the problem is in my table section where I display it.


Any thoughts?

Eric

Table Section:

<table border="0" cellpadding="15" cellspacing="0" bgcolor="FFFFFF">
<cfoutput query="Main">
<cfif Main.currentrow mod 3 eq 1>
<tr>
</cfif>
<td>
<p><cfoutput><img src="images/#Main.Image_File#" width="150"></cfoutput></p>
<p><span class="style1">Part Number:</span> <cfoutput><span class="style1"><cfoutput>#Main.Part_Number#</cfoutput></span></cfoutput><br />
<span class="style1">Contract:</span> <cfoutput><span class="style1">#Main.Name_of_Contract#</span></cfoutput><br />
<span class="style1">Quantity</span>: <cfoutput><cfoutput><span class="style1">#Main.Quantity#</span></cfoutput></cfoutput><br />
<span class="style1">Specific Description: </span> <cfoutput><cfoutput><span class="style1">#Main.Specific_Description#</span></cfoutput></cfoutput><br />
<span class="style1">Nomenclature:</span> <cfoutput><span class="style1">#Main.Generic_Nomen#</span></cfoutput><br />
<span class="style1">Manufacturer:</span> <cfoutput><span class="style1">#Main.Manufacture_Name#</span></cfoutput><br />
<span class="style1">Primary Vendor: </span> <cfoutput><span class="style1">#Main.Vendor_Name#</span></cfoutput><br />
<span class="style1">NEMS Tag: </span> <cfoutput><cfoutput><span class="style1">#Main.NEMS_Tag#</span></cfoutput></cfoutput><br />
<span class="style1">Location (Cage/Row): <cfoutput><cfoutput>#Main.Location#</cfoutput></cfoutput></span> <br />
</p></td>
<cfif Main.currentrow mod 3 eq 0>
</tr>
</cfif>
</cfoutput>
</table>

[Eric_Dahlenburg · Accepted solution]

Problem was resolved. For the benifit of others reading this thread, here is what happened:

Made the changes to the SQL Per Dan Bracuk (Thanks !)
While not the problem, it did make it easier to follow.

The problem was in the Table display code. Look at the original code:

<table border="0" cellpadding="15" cellspacing="0" bgcolor="FFFFFF">
<cfoutput query="Main">
<cfif Main.currentrow mod 3 eq 1>
<tr>
</cfif>
<td>
<p><cfoutput><img src="images/#Main.Image_File#" width="150"></cfoutput></p>
<p><span class="style1">Part Number:</span> <cfoutput><span class="style1"><cfoutput>#Main.Part_Number#</cfoutput></span></cfoutput><br />
<span class="style1">Contract:</span> <cfoutput><span class="style1">#Main.Name_of_Contract#</span></cfoutput><br />
<span class="style1">Quantity</span>: <cfoutput><cfoutput><span class="style1">#Main.Quantity#</span></cfoutput></cfoutput><br />
<span class="style1">Specific Description: </span> <cfoutput><cfoutput><span class="style1">#Main.Specific_Description#</span></cfoutput></cfoutput><br />
<span class="style1">Nomenclature:</span> <cfoutput><span class="style1">#Main.Generic_Nomen#</span></cfoutput><br />
<span class="style1">Manufacturer:</span> <cfoutput><span class="style1">#Main.Manufacture_Name#</span></cfoutput><br />
<span class="style1">Primary Vendor: </span> <cfoutput><span class="style1">#Main.Vendor_Name#</span></cfoutput><br />
<span class="style1">NEMS Tag: </span> <cfoutput><cfoutput><span class="style1">#Main.NEMS_Tag#</span></cfoutput></cfoutput><br />
<span class="style1">Location (Cage/Row): <cfoutput><cfoutput>#Main.Location#</cfoutput></cfoutput></span> <br />
</p></td>
<cfif Main.currentrow mod 3 eq 0>
</tr>
</cfif>
</cfoutput>
</table>


Look at all those <cfoutput></cfoutput> tags ! No wonder it was printing duplicate fields.

Redid the code and works great ! Only needed one set of those tags for the whole set of outputs.
Corrected Code follows:

<table border="0" cellpadding="15" cellspacing="0" bgcolor="FFFFFF">
<cfoutput query="Main">
<cfif Main.currentrow mod 2 eq 1>
<tr>
</cfif>
<td>
<p><img src="images/#Main.Image_File#" width="150"></p>
<p><span class="style1">Part Number:</span> <span class="style1">#Main.Part_Number#</span><br />
<span class="style1">Contract:</span> <span class="style1">#Main.Name_of_Contract#</span><br />
<span class="style1">Quantity</span>: <span class="style1">#Main.Quantity#</span><br />
<span class="style1">Specific Description: </span> <span class="style1">#Main.Specific_Description#</span><br />
<span class="style1">Nomenclature:</span> <span class="style1">#Main.Generic_Nomen#</span><br />
<span class="style1">Manufacturer:</span> <span class="style1">#Main.Manufacture_Name#</span><br />
<span class="style1">Primary Vendor: </span> <span class="style1">#Main.Vendor_Name#</span><br />
<span class="style1">NEMS Tag: </span> <span class="style1">#Main.NEMS_Tag#</span><br />
<span class="style1">Location (Cage/Row): #Main.Location#</span> <br />
</p></td>
<cfif Main.currentrow mod 2 eq 0>
</tr>
</cfif>
</cfoutput>
</table>


Thanks !

Eric