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