If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filtering data
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])); |
#2
|
|||
|
|||
filtering data
=?Utf-8?B?SmFu?= wrote:
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. -- Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
filtering data
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) "Jan" wrote: 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])); |
Thread Tools | |
Display Modes | |
|
|