filtering data

[Jan]

I have a database set up for a consignment shop. I'm trying to print a list
of crafters that had sales during a time period that I enter. I'm able to get
the info, but if a crafter had more than 1 sale during that time period,
their name is listed more than once. I want the crafter listed only once.
Below is my SQL. Please help.

Thanks,
Jan

SELECT Crafters.CrafterID, Crafters.CrafterFName, Crafters.CrafterName,
Crafters.CrafterAddress, Crafters.CrafterCity, Crafters.CrafterState,
Crafters.CrafterZip, Crafters.CrafterPhone, Sales.SalesDate,
Sales.SalesCrafterID
FROM (Crafters INNER JOIN Sales ON Crafters.CrafterID =
Sales.SalesCrafterID) INNER JOIN Items ON Crafters.CrafterID =
Items.ItemCrafterID
GROUP BY Crafters.CrafterID, Crafters.CrafterFName, Crafters.CrafterName,
Crafters.CrafterAddress, Crafters.CrafterCity, Crafters.CrafterState,
Crafters.CrafterZip, Crafters.CrafterPhone, Sales.SalesDate,
Sales.SalesCrafterID
HAVING (((Crafters.CrafterFName) Is Not Null) AND ((Sales.SalesDate)>=[Enter
Beginning Date] And (Sales.SalesDate)<=[Enter Ending Date]));

 

[XPS35]

I have a database set up for a consignment shop. I'm trying to print a list
of crafters that had sales during a time period that I enter. I'm able to get
the info, but if a crafter had more than 1 sale during that time period,
their name is listed more than once. I want the crafter listed only once.
Below is my SQL. Please help.

Thanks,
Jan

SELECT Crafters.CrafterID, Crafters.CrafterFName, Crafters.CrafterName,
Crafters.CrafterAddress, Crafters.CrafterCity, Crafters.CrafterState,
Crafters.CrafterZip, Crafters.CrafterPhone, Sales.SalesDate,
Sales.SalesCrafterID
FROM (Crafters INNER JOIN Sales ON Crafters.CrafterID =
Sales.SalesCrafterID) INNER JOIN Items ON Crafters.CrafterID =
Items.ItemCrafterID
GROUP BY Crafters.CrafterID, Crafters.CrafterFName, Crafters.CrafterName,
Crafters.CrafterAddress, Crafters.CrafterCity, Crafters.CrafterState,
Crafters.CrafterZip, Crafters.CrafterPhone, Sales.SalesDate,
Sales.SalesCrafterID
HAVING (((Crafters.CrafterFName) Is Not Null) AND ((Sales.SalesDate)>=[Enter
Beginning Date] And (Sales.SalesDate)<=[Enter Ending Date]));

I think you should not have the sales fields in the SELECT part and in
the GROUP BY part of the query.
No need to select/group by CrafterID twice.
Selecting/grouping by SalesDate causes that for each date a crafter made
a sale a row appears in the query.

 

[John Viescas]

Jan-

Like this:

PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT Crafters.CrafterID, Crafters.CrafterFName, Crafters.CrafterName,
Crafters.CrafterAddress, Crafters.CrafterCity, Crafters.CrafterState,
Crafters.CrafterZip, Crafters.CrafterPhone
FROM Crafters
WHERE Crafters.CrafterID IN
(SELECT SalesCrafterID
FROM Sales
WHERE (Sales.SalesDate >= [Enter Beginning Date])
And (Sales.SalesDate <= [Enter Ending Date]))
And Crafters.CrafterFName IS NOT NULL;

--
John Viescas, author
"SQL Queries for Mere Mortals"
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
http://www.viescas.com/
(Paris, France)