Joining a Query and Table while Excluding some results 1

[tempus4]

Here's the problem;

We need to return a query that shows the products that aren't
associated with a specific PriceGroupID.(PriceGroupID is the
specific group that will see these prices)

We've got a Product table

ProductID | ProductName |
-------------------------
1 | Product01 |
2 | Product02 |
3 | Product03 |
4 | Product04 |

and a Price table.

PriceID | PriceGroupID| ProductID| Price |
-----------------------------------------------
1 | 1 | 2 | $35 |
2 | 1 | 4 | $59 |
3 | 2 | 4 | $24 |
4 | 3 | 3 | $99 |

We can make a query (qryGroupsPrices) that just lists one
PriceGroupID from the Price Table which comes from a URL
querystring variable [MMColParam]

i.e.
SELECT *
FROM tblPrices
WHERE ((tblPrices.PriceGroupID)=[MMColParam]);

Then we make an outer join query that finds the Products that aren't
yet associated with the specific group;

SELECT tblProducts.ProductID, tblProducts.ProductName,
qryGroupsPrices.PriceGroupID
FROM tblProducts LEFT JOIN qryGroupsPrices
ON tblProducts.ProductID = qryGroupsPrices.ProductID
WHERE ((qryGroupsPrices.ProductID) Is Null);

And Wallah! It works fine from Access but when we try and get
a result from the web page that sends the querystring [MMColParam]
to this query we get a "Too Few Parameters. Expected 1" Result.

We tried to combine the two queries but we're not so familiar with
the SQL idiosyncrasies of Access and have spent two days pulling
our hair out over it. This just can't be so difficult so please help
us if you can?

In desperate need,

Judy

 

[mordja]

Judy,

The query is not recieving the parameter. When you say "sends the querystring [MMColParam]
to this query" how are you achieving this ??

Mordja

 

[tempus4]

Hi Mordja,

The variable [MMColParam] is sent in an URL querystring (i.e. webpage.asp?PriceGroupID=2) to the browser... and then we make MMColParam = Request.QueryString("PriceGroupID") so we can use it in the query.
Hope that sheds some more light. Thanks heaps for responding. I'm getting quite distraught as this was meant to be a couple of hours work and it's turned into 3 days

Jude

 

[mordja]

Jude,

Ok you say you

"make
MMColParam = Request.QueryString("PriceGroupID")
so we can use it in the query"

Then how do you relate your MMColParam variable to the query ?? Where do you do this, in a module ? How do you trigger the query ? Do you have any of the module code for me to look at ??

Mordja

 

[tempus4]

Hi Mordja,

Never actually used a module. We get the list of unassigned products for this group using this statement in the web page;

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("PriceGroupID") <> "") Then
Recordset1__MMColParam = Request.QueryString("PriceGroupID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_AutoGar_STRING
Recordset1.Source = "SELECT * FROM qryProductsWithoutPrices WHERE PriceGroupID = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

Hope this makes sense,

Thanks again,
Jude

 

[mordja]

What type of object is Recordset1__MMColParam ??

If it is a string I would replace the line

Dim Recordset1__MMColParam

with

Dim Recordset1__MMColParam AS String

and replace

Recordset1.Source = "SELECT * FROM qryProductsWithoutPrices WHERE PriceGroupID = " + Replace(Recordset1__MMColParam, "'", "''") + ""

With

Recordset1.Source = "SELECT * FROM qryProductsWithoutPrices WHERE PriceGroupID = '" & Recordset1__MMColParam & "'"

Lastly have you tried declaring

Dim Recordset1__MMColParam

above the % instead of below ?

Mordja

 

[tempus4]

Hi again Mordja,

Well, gave it a go but I get a "Invalid Variable" error message. Not sure what to do now

Thanks again,

Jude

 

[mordja]

What did you give a go ?

Why dont you declare a new string object. Assign it the value of the parameter you bring in and use that.

Have you tried putting in a messagebox statement or debugging to check that the value is coming through, if not that is definitely the first thing to test. Try

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("PriceGroupID") <> "") Then
Recordset1__MMColParam = Request.QueryString("PriceGroupID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

MsgBox "Parameter Received : " & Recordset1__MMColParam

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_AutoGar_STRING
Recordset1.Source = "SELECT * FROM qryProductsWithoutPrices WHERE PriceGroupID = '" & Recordset1__MMColParam & "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>


Mordja

 

[tempus4]

Hey Mordja,

Sorry... I sent a reply and it didn't get posted so here it is again;

"What did I have a go" Well, I tried your suggestions as well as variation on the string and a few other things.

No luck I'm afraid... I tested the querystring a couple of days ago to make sure it was sending. I'm just in the process of trying something I found in the forum... Will post the details as soon as I get anything interesting.

Thanks again,
Judy

 

[tempus4]

Halleluya!

Got it finally with thanks to Mordja and John Winterbottom... We ended up doing the query from the web page instead of making a seperate query in Access.

Here's the web page code that does the magic. Hope this saves anyone else from the 3 days of agony we suffered trying to solve it. Cheers to all!!!!!

<%
Dim Recordset1__PriceGroupID
Recordset1__PriceGroupID = "1"
If (Request.QueryString("PriceGroupID") <> "") Then
Recordset1__PriceGroupID = Request.QueryString("PriceGroupID")
End If
%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_AutoGar_STRING
Recordset1.Source = "select * from tblProducts as p where not exists (select * from tblPrices as c2 where c2.PriceGroupID = " + Replace(Recordset1__PriceGroupID, "'", "''") + " and c2.ProductID = p.ProductID)"

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>