Query - Select Distinct 1 field, but display many?
in [Help]
|
From: Salad on 19 Jul 2010 13:31 Robert Jacobs wrote: > On Jul 19, 11:50 am, Salad <sa...(a)oilandvinegar.com> wrote: > >>Robert Jacobs wrote: >> >>>On Jul 19, 10:16 am, Salad <sa...(a)oilandvinegar.com> wrote: >> >>>>Robert Jacobs wrote: >> >>>>>Thanks in advance, expert advisers!!! >> >>>>>I have a query that I run a report from which returns all records that >>>>>have a 'Renew by Date' in the last 365 days, as well as the next 60 >>>>>days (showing me any software in my tables that have expired in the >>>>>past year, or need to be renewed within the next 60 days). This shows >>>>>me the Name of the software, the Renew by Date, and the CD Key. >> >>>>>My problem is, I have hundreds of the same the same product that renew >>>>>on the same date... so when I run the report, it shows hundreds of the >>>>>same thing needing to be renewed. >> >>>>>So, I'd like to filter this further, only showing me one instance of >>>>>this software, based on the CD Key (I can't do the name, as I might >>>>>have 5 different CD Keys for Microsoft Office Professional, all with >>>>>the same Renew by Date, but different CD Keys - so it won't remind me >>>>>to renew each instance...) >> >>>>>Here's what I have now: >>>>>SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(), >>>>>[RenewByDate]) AS Expr1, Software.* >>>> >>>>>FROM Software >>>> >>>>>WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), >>>>>[RenewByDate])) Between -365 And 60)); >> >>>>>(If you're wondering, the StopDisplay is a check box on the record >>>>>that you can check if you don't want it to be shown on the renewal >>>>>report - so this only shows those records that don't have that box >>>>>checked) >> >>>>>I have added DISTINCT after SELECT, but it doesn't change the >>>>>results. I have also tried to just do: >> >>>>>SELECT DISTINCT Software.swCDKey >>>> >>>>>FROM Software >>>> >>>>>WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), >>>>>[RenewByDate])) Between -365 And 60)); >> >>>>>But when I try to run my report, it asks me to enter data for each of >>>>>the fields listed in the report, instead of auto filling. What I >>>>>really need is for this query to do exactly what it does now, but only >>>>>show me 1 instance of a product with a specific CD Key... ANY HELP IS >>>>>GREATLY APPRECIATED!!! THANK YOU AGAIN, IN ADVANCE!!!! >> >>>>You have 2 SQL statements. Do both fail or just 1? I noticed on the >>>>first statement you specify some fields from table Software and the last >>>>"field" is Software.* or basically the entire table. Is "Software" a >>>>table or a query? The reason I ask is...where is the SoftwareKey >>>>parameter? I'm not sure why it would be prompting you to enter data for >>>>each field instead of echoing #Name or #Error. >> >>>I honestly can't give you the answer to this - I created the queries >>>in Wizard mode (I only know how to work with it in Design view - SQL >>>view is new to me...) The first SQL statement is the one that is in >>>use right now - and it was created by the wizard, so I have no clue. >>>I just need to know how to modify that SQL statement to only give me >>>one instance that includes that CD Key, and only if it meets the other >>>requirement (renewal date is within last 365 days to within next 90 >>>days) >> >>>I'm very sorry that I'm not very competent in this area... >> >>Hmmm. How do you run this query? From the database window, from within >>a report, or from a form? >> >>If you are running the query from the database window, an example where >>I am prompted to enter a parameter might look like this >> SELECT TableName.* FROM TableName WHERE ID = [Enter ID]; >> >>The [Enter ID], when run. prompts me to enter in the ID I am searching >>for. If calling the query/report coming from a form, it might be >> ID = Forms!YourFormName!IDFieldName >> >>I most likely would change >> SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(), >> [RenewByDate]) AS Expr1, Software.* >>to >> SELECT , Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1 >>as the .* will pick up every field in the table. > > > I run the query from a Report... It displays all records with expiring > or expired renewal dates. The query (when viewed in datasheet view) > shows me every field in the table, but only the records with expiring > or expired warranties. The Report uses this query to only show me > these same records, displaying only the Name of the software, CD Key, > and renew by date. I just can't get the Report to show me only 1 > instance of each CD Key. Where do you run the query from in the report? As the report's recordsource? Here;s an example where I enter an ID manually for a report not calling it from a form. Private Sub Report_Open(Cancel As Integer) Dim strID As String strID = InputBox("Enter ID") strID = Trim(strID) If strID > "" Then strID = "TableID = " & CLng(strID) Me.Filter = strID Me.FilterOn = (strID > "") End Sub Maybe what could do is create a new query. Add the Software table. Drag only the software key into the column. From the menu select View/Totals and select GroupBy for the column. Now you have 1 record per software key. Save as Query1. Create a new query. Add the table Software and Query1. Create a link between the two. Drag your fields down. Save. Use this query as your report's recordsource. > > I did what you said, and changed my Select statement. It now looks > like: > > SELECT Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1 > FROM Software > WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), > [RenewByDate])) Between -365 And 90)); > > And it gives me the same result, so I'll stick with your way. Is > there a way to add to the WHERE statement something like "AND swCDKey > is distinct/unique" Of course, that's not how it would be written, > but it's the result I would like to have. Is it possible even? I > don't want to type in the CD Key when I run the report, I want the > report to show me all of the CD Keys that have expired or are expiring > - just one instance of each CD Key...
From: Marshall Barton on 19 Jul 2010 13:31 Robert Jacobs wrote: >On Jul 19, 10:16�am, Salad wrote: >> Robert Jacobs wrote: >> > I have a query that I run a report from which returns all records that >> > have a 'Renew by Date' in the last 365 days, as well as the next 60 >> > days (showing me any software in my tables that have expired in the >> > past year, or need to be renewed within the next 60 days). �This shows >> > me the Name of the software, the Renew by Date, and the CD Key. >> >> > My problem is, I have hundreds of the same the same product that renew >> > on the same date... so when I run the report, it shows hundreds of the >> > same thing needing to be renewed. >> >> > So, I'd like to filter this further, only showing me one instance of >> > this software, based on the CD Key (I can't do the name, as I might >> > have 5 different CD Keys for Microsoft Office Professional, all with >> > the same Renew by Date, but different CD Keys - so it won't remind me >> > to renew each instance...) >> >> > Here's what I have now: >> > SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(), >> > [RenewByDate]) AS Expr1, Software.* >> > FROM Software >> > WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), >> > [RenewByDate])) Between -365 And 60)); >> >> > (If you're wondering, the StopDisplay is a check box on the record >> > that you can check if you don't want it to be shown on the renewal >> > report - so this only shows those records that don't have that box >> > checked) >> >> > I have added DISTINCT after SELECT, but it doesn't change the >> > results. �I have also tried to just do: >> >> > SELECT DISTINCT Software.swCDKey >> > FROM Software >> > WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), >> > [RenewByDate])) Between -365 And 60)); >> >> > But when I try to run my report, it asks me to enter data for each of >> > the fields listed in the report, instead of auto filling. �What I >> > really need is for this query to do exactly what it does now, but only >> > show me 1 instance of a product with a specific CD Key... ANY HELP IS >> > GREATLY APPRECIATED!!! THANK YOU AGAIN, IN ADVANCE!!!! >> >> You have 2 SQL statements. �Do both fail or just 1? �I noticed on the >> first statement you specify some fields from table Software and the last >> "field" is Software.* or basically the entire table. �Is "Software" a >> table or a query? �The reason I ask is...where is the SoftwareKey >> parameter? �I'm not sure why it would be prompting you to enter data for >> each field instead of echoing #Name or #Error. > >I honestly can't give you the answer to this - I created the queries >in Wizard mode (I only know how to work with it in Design view - SQL >view is new to me...) The first SQL statement is the one that is in >use right now - and it was created by the wizard, so I have no clue. >I just need to know how to modify that SQL statement to only give me >one instance that includes that CD Key, and only if it meets the other >requirement (renewal date is within last 365 days to within next 90 >days) > Seems to me that the cd key is irrelevant and getting in the way so you can not check for duplicates. Maybe knowing how many cd keys would be useful, but each individual cd key does not appear to be useful. If that thought is valid, then try something like: SELECT Count(swCDKey) As units, Software.swName, DateDiff('d',Date(), [RenewByDate]) AS Days FROM Software WHERE StopDisplay=0 AND DateDiff('d', Date(), [RenewByDate]) Between -365 And 60 GROUP BY swName, DateDiff('d',Date(), [RenewByDate]) -- Marsh
From: Robert Jacobs on 19 Jul 2010 14:20 On Jul 19, 12:31 pm, Salad <sa...(a)oilandvinegar.com> wrote: > Robert Jacobs wrote: > > On Jul 19, 11:50 am, Salad <sa...(a)oilandvinegar.com> wrote: > > >>Robert Jacobs wrote: > > >>>On Jul 19, 10:16 am, Salad <sa...(a)oilandvinegar.com> wrote: > > >>>>Robert Jacobs wrote: > > >>>>>Thanks in advance, expert advisers!!! > > >>>>>I have a query that I run a report from which returns all records that > >>>>>have a 'Renew by Date' in the last 365 days, as well as the next 60 > >>>>>days (showing me any software in my tables that have expired in the > >>>>>past year, or need to be renewed within the next 60 days). This shows > >>>>>me the Name of the software, the Renew by Date, and the CD Key. > > >>>>>My problem is, I have hundreds of the same the same product that renew > >>>>>on the same date... so when I run the report, it shows hundreds of the > >>>>>same thing needing to be renewed. > > >>>>>So, I'd like to filter this further, only showing me one instance of > >>>>>this software, based on the CD Key (I can't do the name, as I might > >>>>>have 5 different CD Keys for Microsoft Office Professional, all with > >>>>>the same Renew by Date, but different CD Keys - so it won't remind me > >>>>>to renew each instance...) > > >>>>>Here's what I have now: > >>>>>SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(), > >>>>>[RenewByDate]) AS Expr1, Software.* > > >>>>>FROM Software > > >>>>>WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), > >>>>>[RenewByDate])) Between -365 And 60)); > > >>>>>(If you're wondering, the StopDisplay is a check box on the record > >>>>>that you can check if you don't want it to be shown on the renewal > >>>>>report - so this only shows those records that don't have that box > >>>>>checked) > > >>>>>I have added DISTINCT after SELECT, but it doesn't change the > >>>>>results. I have also tried to just do: > > >>>>>SELECT DISTINCT Software.swCDKey > > >>>>>FROM Software > > >>>>>WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), > >>>>>[RenewByDate])) Between -365 And 60)); > > >>>>>But when I try to run my report, it asks me to enter data for each of > >>>>>the fields listed in the report, instead of auto filling. What I > >>>>>really need is for this query to do exactly what it does now, but only > >>>>>show me 1 instance of a product with a specific CD Key... ANY HELP IS > >>>>>GREATLY APPRECIATED!!! THANK YOU AGAIN, IN ADVANCE!!!! > > >>>>You have 2 SQL statements. Do both fail or just 1? I noticed on the > >>>>first statement you specify some fields from table Software and the last > >>>>"field" is Software.* or basically the entire table. Is "Software" a > >>>>table or a query? The reason I ask is...where is the SoftwareKey > >>>>parameter? I'm not sure why it would be prompting you to enter data for > >>>>each field instead of echoing #Name or #Error. > > >>>I honestly can't give you the answer to this - I created the queries > >>>in Wizard mode (I only know how to work with it in Design view - SQL > >>>view is new to me...) The first SQL statement is the one that is in > >>>use right now - and it was created by the wizard, so I have no clue. > >>>I just need to know how to modify that SQL statement to only give me > >>>one instance that includes that CD Key, and only if it meets the other > >>>requirement (renewal date is within last 365 days to within next 90 > >>>days) > > >>>I'm very sorry that I'm not very competent in this area... > > >>Hmmm. How do you run this query? From the database window, from within > >>a report, or from a form? > > >>If you are running the query from the database window, an example where > >>I am prompted to enter a parameter might look like this > >> SELECT TableName.* FROM TableName WHERE ID = [Enter ID]; > > >>The [Enter ID], when run. prompts me to enter in the ID I am searching > >>for. If calling the query/report coming from a form, it might be > >> ID = Forms!YourFormName!IDFieldName > > >>I most likely would change > >> SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(), > >> [RenewByDate]) AS Expr1, Software.* > >>to > >> SELECT , Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1 > >>as the .* will pick up every field in the table. > > > I run the query from a Report... It displays all records with expiring > > or expired renewal dates. The query (when viewed in datasheet view) > > shows me every field in the table, but only the records with expiring > > or expired warranties. The Report uses this query to only show me > > these same records, displaying only the Name of the software, CD Key, > > and renew by date. I just can't get the Report to show me only 1 > > instance of each CD Key. > > Where do you run the query from in the report? As the report's > recordsource? > > Here;s an example where I enter an ID manually for a report not calling > it from a form. > Private Sub Report_Open(Cancel As Integer) > Dim strID As String > strID = InputBox("Enter ID") > strID = Trim(strID) > If strID > "" Then strID = "TableID = " & CLng(strID) > Me.Filter = strID > Me.FilterOn = (strID > "") > > End Sub > > Maybe what could do is create a new query. Add the Software table. > Drag only the software key into the column. From the menu select > View/Totals and select GroupBy for the column. Now you have 1 record > per software key. Save as Query1. Create a new query. Add the table > Software and Query1. Create a link between the two. Drag your fields > down. Save. Use this query as your report's recordsource. > > > > > I did what you said, and changed my Select statement. It now looks > > like: > > > SELECT Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1 > > FROM Software > > WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(), > > [RenewByDate])) Between -365 And 90)); > > > And it gives me the same result, so I'll stick with your way. Is > > there a way to add to the WHERE statement something like "AND swCDKey > > is distinct/unique" Of course, that's not how it would be written, > > but it's the result I would like to have. Is it possible even? I > > don't want to type in the CD Key when I run the report, I want the > > report to show me all of the CD Keys that have expired or are expiring > > - just one instance of each CD Key... Alright - I got it to work in one query - I used your idea to GroupBy, and it's actually OK if I GroupBy all columns - because I'll never have a Name of a product different than another, using the same CD key - so it really didn't matter how I got it dwindled down. so basically, it shows only one instance if the Name (swTypeVersion), CD Key (swCDKey), and Renewal Date (RemewByDate) all match (which is perfect for what I'm doing). Here's how the code ended up looking: SELECT DateDiff('d',Date(),[RenewByDate]) AS Expr1, Software.swCDKey, Software.swTypeVersion, Software.RenewByDate FROM Software GROUP BY DateDiff('d',Date(),[RenewByDate]), Software.swCDKey, Software.swTypeVersion, Software.RenewByDate, Software.StopDisplay HAVING (((DateDiff('d',Date(),[RenewByDate])) Between -365 And 90) AND ((Software.StopDisplay)=0)); Don't ask me what it all does, but the designer put it all together for me. So, again, thank you very much - everything is working perfectly now!!!! You guys are awesome!
From: Marshall Barton on 19 Jul 2010 16:28 Robert Jacobs wrote: >Alright - I got it to work in one query - I used your idea to GroupBy, >and it's actually OK if I GroupBy all columns - because I'll never >have a Name of a product different than another, using the same CD key >- so it really didn't matter how I got it dwindled down. so >basically, it shows only one instance if the Name (swTypeVersion), CD >Key (swCDKey), and Renewal Date (RemewByDate) all match (which is >perfect for what I'm doing). Here's how the code ended up looking: > >SELECT DateDiff('d',Date(),[RenewByDate]) AS Expr1, Software.swCDKey, >Software.swTypeVersion, Software.RenewByDate >FROM Software >GROUP BY DateDiff('d',Date(),[RenewByDate]), Software.swCDKey, >Software.swTypeVersion, Software.RenewByDate, Software.StopDisplay >HAVING (((DateDiff('d',Date(),[RenewByDate])) Between -365 And 90) AND >((Software.StopDisplay)=0)); > That HAVING clause should be a WHERE clause. In this case the result will be the same, but it will be slower because HAVING is applied after the query does all the work for every record in the table. On the other hand, WHERE is used to select only the needed records before the query starts to do all the grouping work. It would also be faster to Index the StopDisplay and RenewalDate fields and change the HAVING to: WHERE RenewByDate Between DateAdd('d', -365, Date()) And DateAdd('d', 60, Date()) AND StopDisplay = 0 -- Marsh
First
|
Prev
|
Pages: 1 2 Prev: Hiding Headers In Multiple Column Reports Next: Convering from Access97 to Access2010 |