Trying to create an sql query that results in no duplicates 1

[bslintx]

HI everyone,

I am using an Access db and asp to track software on pc's. What the final output is SUPPOSED to show is a MASTER list of software from the table (tblSoftware) AND what software is actually on a user's pc from the table tblCompSW. The idea is to have a list for the user to choose from (tblSoftware) and enter license number and type then check off what is installed on his pc. When posted it sends the data to the tblCompSW. When the user goes back to add more software the MASTER list should show "greyed out" checkmarks in check field on software already installed. This is working great if "distinct" checked software for each machine is done; however, if the SAME software is checked I get a greyed out checkbox like normal but also another of the same software item NOT greyed out. I'm assuming I will need to use a Group By or some other type of join. I have experimented but I cannot find a solution thus far. I am a newbie and any help would be appreciated.

-BSL


<form action="default.asp" method="post">

<%
strSQL = "SELECTtblCompSW.SoftwareID,
tblCompSW.EditSoftware,
tblCompSW.ComputerID,tblCompSW.LicenseType, tblCompSW.LicenseNumber, " &_
"tblSoftware.Software, tblSoftware.SoftwareID FROM tblSoftware LEFT JOIN tblCompSW ON " &_
"tblSoftware.SoftwareID = tblCompSW.SoftwareID;"
objRS.Open strSQL, objConn, 3

'Start looping counting variable to allow for unique checkbox values
i = 0

%>

<table class="swlisttable" cellspacing="0">
<tr>
<th>Software</th>
<th>License Type</th>
<th>License Number</th>
<th>Select</th>
</tr>

<%

'Start looping through recordsets until the end of the recordset
Do Until objRS.EOF
i = i + 1

softid = objRS("tblSoftware.SoftwareID")
cbName = "CB_" & softid


active = "Yes"

If objRS("tblCompSW.SoftwareID") = objRS("tblSoftware.SoftwareID") AND objRS("ComputerID") = compid Then
chkd = "checked disabled"


Else

chkd = ""

End If
Response.Write "<tr>" & vbCrlf & _
" <td>" & objRS("Software") & "</td>" & vbCrlf & _
" <td><select class=""swselect"" name=""type_" & softid & """ >" & vbCrlf & _
" <option value=""""></option>" & vbCrlf & _
" <option value=""Single"">Single</option>" & vbCrlf & _
" <option value=""Network"">Network</option>" & vbCrlf & _
" <option value=""Site"">Site</option>" & vbCrlf & _
" <option value=""Enterprise"">Enterprise</option>" & vbCrlf & _
" </select></td>" & vbCrlf & _
" <td><input type=""text"" name=""num_" & softid & """ ></td>" & vbCrlf & _
" <td><INPUT Type='CheckBox'" & "Name='" & cbName & "' Value='NO'" & chkd & ">" & vbCrlf & _
"</tr>"


objRS.MoveNext
Loop



Response.Write "</table>"
%>
<p align="center"><input type="submit" name="addsofttodb" value="Add Software">



<input type="hidden" name="storemanagerid" value="<%=manidw%>">
<input type="hidden" name="storecomputerid" value="<%=selectcomputer%>"></form></p></div>
<%

'Close the recordset object

End If



storecomputerid = Request.Form("storecomputerid")

If storecomputerid <> "" Then

For Each item In Request.Form



If Left( item, 3 ) = "CB_" Then
recID = Mid( item, 4 )
software_type = Request.Form("type_" & recID)
software_num = Request.Form("num_" & recID)


strSQL = "SELECT * FROM tblCompSW;"
objRS.Open strSQL, objConn, 3 ,3

objRS.AddNew
objRS("ComputerID") = storecomputerid
objRS("SoftwareID") = recID
objRS("LicenseType") = software_type
objRS("LicenseNumber") = software_num
objRS("EditSoftware") = True
objRS.Update


objRS.Close





End If

Next

End If

 

[Sheco]

Sometimes it is easier to get your SQL right first and then try to incorporate it into your code.

Since you are using MS Access you might want to try:
1. Opening the .MDB file in Access
2. Creating a new Query in Design view.
3. Close the little dialog box where you select tables.
4. Right-click and choose "SQL View"
5. Type your SQL here and see if it gives you the correct results when you run it.

 

[Tarwn]

The biggest problem I see in your SQL statement is that I don't see where you are filtering for the specific user. I would expect to see something like:

strSQL = "SELECT tblCompSW.SoftwareID, tblCompSW.EditSoftware, tblCompSW.ComputerID, tblCompSW.LicenseType, tblCompSW.LicenseNumber, tblSoftware.Software, tblSoftware.SoftwareID " & _
   "FROM tblSoftware LEFT JOIN tblCompSW ON " &_
                "tblSoftware.SoftwareID = tblCompSW.SoftwareID " & _
[highlight]   "WHERE tblCompSW.ComputerID = " & compid[/highlight]

This being the case, what you are actually pulling back is probably multiple users with the same software. Except in your code your only disabling the box if the compid matches. So basically all the greyed out ones you see are the ones that belong to the user in question. The unchecked duplicates are ones that in fact belongto other users. Since they don't match the comnpid criteria they don't get blanked out, but still get displayed (since they are in the recordset).
Hope this helps,
-T

 

[bslintx]

Thanks Sheco but I had done this already and the Access query works fine because it COMBINES the junction table tblCompSW and the tblSoftware table. The results are as expected however since they're COMPIINED/JOINED it will always come up with the MASTER software entries (tblSoftware) and those that only apply to tblCompSW.
-----------------------------------------------------------
Tarwn,

You are on the right track as far as what the query is doing in finding the softid variable from (tblSoftwareID) AND finding ANY entries that the junction table tblCompSW has in it; thus, if I entered let's say ADOBE from the dropdoown box of tblSoftware for a computer named comp1 AND I had done the same thing under comp2 the query will display the software twice since the softid it entered twice in the tblCompSW table; in return, causing 2 matches(duplicates). What you have will only display what is on the particuar pc which is fine; however, I need to show the MASTER list with it but not have the query show the match from the other computer. Do I need a COUNT(Distinct) SoftwareID in the main query and somehow and use a GROUP BY clause? The query you have is basically in my IF condition -


If objRS("tblCompSW.SoftwareID") = objRS("tblSoftware.SoftwareID") AND objRS("ComputerID") = compid Then
chkd = "checked disabled"


Else

chkd = ""

End If

____________________________________________________

I truely appreciate the expedient response on this however; I am still in awe on how to combine the two results with a view of ALWAYS showing ALL software from tblSoftware and checking off the comparison of the SoftwareIDs from junction table and MASTER software table.

Again, thanks and help is still needed and appreciated


BSL

 

[Tarwn]

Ack, my WHERE statement counter-acted the LEFT JOIN
Basically we need to LEFT JOIN the software table with a subset of the second table, we could do this a few ways, but i'm on an inner select kick this week:

strSQL = "SELECT A.SoftwareID, A.EditSoftware, A.ComputerID, A.LicenseType, A.LicenseNumber, tblSoftware.Software, tblSoftware.SoftwareID " & _
	"FROM tblSoftware LEFT JOIN "  & _
		"(SELECT tblCompSW.SoftwareID, tblCompSW.EditSoftware, tblCompSW.ComputerID, tblCompSW.LicenseType, tblCompSW.LicenseNumber " & _
		"FROM tblCompSW " & _
		"WHERE tblCompSW.ComputerID = " & compid & ") A " & _
	" ON tblSoftware.SoftwareID = A.SoftwareID"

So basically we are now left joining all of the software entries with a temporary table containing only the records for the given computer. This means we will have full records when this particular machine has the software installed but will have only tblSoftware records (and nulls for the tblCompSW fields) when there isn't a record for the given computer. Hope I was a little closer this time around

-T

 

[bslintx]

Tarwn,

Thanks!!!

Worked EXACTLY as advertised!!!!! Greatly appreciated. My project is now completed and is well worth giving a star for the correct sql statement. Thanks again.

BSL

 

[Tarwn]

Not a problem, sorry about the looked-good-enough-to-be-true-but-sadly-wasn't first attempt

-T