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
|
|||
|
|||
Determining EVERY month between 2 given dates
Hi everyone, I have a table with the following data upon which I would
like to report: SomeDt SomeValue 1-sep-09 100 12-dec-09 200 02-feb10 50 14-apr-10 75 I need to report on this data showing values for all months within the given min and max dates on the file so: These dates need to be part of a *rolling* report. I have data for Sep, Dec,Feb and Apr, but I want to display on the report ALL months regardless of whether they have data in the table and return 0 where they do not. Thus: Month Value Sep 09 100 Oct 09 0 Nov 09 0 Dec 09 200 Jan 10 0 Feb 10 50 Mar 10 0 Apr 10 75 I can get the min and max and the count of months easily enough: select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt, EndDt) as CountofMonthsBetween from SomeTable What I cannot get is how to then determine what the months are in between. What I want if possible is a resultset that looks something like this StDt EndDt Mth 01/9/09 14/4/10 Sep 09 01/9/09 14/4/10 Oct 09 01/9/09 14/4/10 Nov 09 01/9/09 14/4/10 Dec 09 01/9/09 14/4/10 Jan 10 01/9/09 14/4/10 Feb 10 01/9/09 14/4/10 Mar 10 01/9/09 14/4/10 Apr 10 How can I do it? As much as possible I want to use QUERY resultsets, rather than creating intermediary tables etc which are just messy in MS Access, but will use them if no other choice. Happy to write a function if needs be to calc the Mth column. Am absolutely desperate so all help greatly appreciated. If it can't be done at all via queries alone, please let me know this too. Edwina63 |
#2
|
|||
|
|||
Determining EVERY month between 2 given dates
The missing months have to come from somewhere, so you need a table to
generate them. 1. Create a new table with just one field named (say) CountID, type Number, and mark it as primary key. Save the table as (say) tblCount. 2. Enter records into this table, from zero to the largest number of months you will need for your report. If there could be a large number of months, here's some code to enter the records for you: http://allenbrowne.com/ser-39.html 3. Create a query using tblCount as the source table. Drag CountID into the grid. In the Criteria row under this field, enter: [How many months?] 4. Type this expression into the next column, the Field row: TheMonth: DateAdd("m", [CountID], [StartMonth]) 5. Declare the Parameters (Parameters on ribbon or Query menu.) Access opens the Parameters dialog. Enter 2 rows, using exactly the same names as you used above: [How many months?] Long StartMonth Date/Time 6. Test. This should generate a record for every month. Save the query as qryCount. 7. Create a query using your existing table. Enter this expression into the Field row: MonthStart: [SomeDt] - Day([SomeDt]) + 1 8. Depress the Total button on the toolbar/ribbon. Access adds a Total row to the design grid. Accept Group By under this field. 9. Drag SomeValue into the grid. In the Total row under this field, choose Sum. Test: the query will give you one total for each month that has data. Save the query as qryMonthData. 10. Create another query using qryCount and qryMonthData as input 'tables'. 11. Drag qryCount.TheMonth onto qryMonthData.MonthStart. Access draws a line between the two tables. 12. Double-click the line between the 2 dialogs. Access opens a dialog with 3 options. Choose the one that says: All records from qryCount, and any matches from qryMonthData. (Technically, that's known as an outer join, and it's what gives you every month.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Edwinah63" wrote in message ... Hi everyone, I have a table with the following data upon which I would like to report: SomeDt SomeValue 1-sep-09 100 12-dec-09 200 02-feb10 50 14-apr-10 75 I need to report on this data showing values for all months within the given min and max dates on the file so: These dates need to be part of a *rolling* report. I have data for Sep, Dec,Feb and Apr, but I want to display on the report ALL months regardless of whether they have data in the table and return 0 where they do not. Thus: Month Value Sep 09 100 Oct 09 0 Nov 09 0 Dec 09 200 Jan 10 0 Feb 10 50 Mar 10 0 Apr 10 75 I can get the min and max and the count of months easily enough: select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt, EndDt) as CountofMonthsBetween from SomeTable What I cannot get is how to then determine what the months are in between. What I want if possible is a resultset that looks something like this StDt EndDt Mth 01/9/09 14/4/10 Sep 09 01/9/09 14/4/10 Oct 09 01/9/09 14/4/10 Nov 09 01/9/09 14/4/10 Dec 09 01/9/09 14/4/10 Jan 10 01/9/09 14/4/10 Feb 10 01/9/09 14/4/10 Mar 10 01/9/09 14/4/10 Apr 10 How can I do it? As much as possible I want to use QUERY resultsets, rather than creating intermediary tables etc which are just messy in MS Access, but will use them if no other choice. Happy to write a function if needs be to calc the Mth column. Am absolutely desperate so all help greatly appreciated. If it can't be done at all via queries alone, please let me know this too. Edwina63 |
#3
|
|||
|
|||
Determining EVERY month between 2 given dates
Hi Allen,
Thanks for your solution, it is very neat. Now let me ask the truly stoopid question: is it possible to generate those eight records that are put into tblCount via a query alone ie: based on the the record count (in this example 8 months between Sep and Apr), is it possible to force a *query* to return 8 records (containing 1 to 8, 0 to 7 or whatever)? I am certain the answer is no, but I am interested to have that confirmed by someone who knows Access well (or queries in general and likes a challenge!). As a quick background, I am used to doing these sorts of things in MS SQL stored procs. I would create the kind of table you suggested as a temp table within the procedure and outer join that table back to my query containing the data all as you have outlined. The advantage of stored procs is that when they go out of scope, all the temp tables I have created are destroyed with them. I am worried about creating the table because once I have the final resultset I want all the intermediary steps destroyed. This is coupled with the fact that the owner of this database wants it to be as codeless as possible. Hence the idea of forcing a query to return 8 records in a resultset (which seems to be the nearest thing to a temp table I can find in Access). Any suggestions as the best way to approach this? Edwinah63 ps not sure how i would go about forcing a query to return 8 empty records in ms sql either!! :-) so over to the floor! |
#4
|
|||
|
|||
Determining EVERY month between 2 given dates
ps not sure how i would go about forcing a query to return 8 empty
records in ms sql either!! :-) so over to the floor! Bite the bullet. Create the tblDates table and then use it in your query. Then you'll create a deliberate partial cartesian product, constraining the dates to be between a start and end date. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
#5
|
|||
|
|||
Determining EVERY month between 2 given dates
Do you already have a table in your database that contains an autonumber
field? If so, use that instead of creating a new table. Otherwise, a dynamic solution is not possible. You can of course hard-code some unioned queries to force the missing records to be included. As an alternative, create a new database in code, create the Numbers table in it, link to it, and use it in your query, deleting the database when finished. I'm sure Allen has an example of VBA code to create a database on his website. If not, try www.rogersaccesslibrary.com As for SQL Server, given the existence of a Numbers table, the exact same solution is possible without the overhead of temp tables. Edwinah63 wrote: Hi Allen, Thanks for your solution, it is very neat. Now let me ask the truly stoopid question: is it possible to generate those eight records that are put into tblCount via a query alone ie: based on the the record count (in this example 8 months between Sep and Apr), is it possible to force a *query* to return 8 records (containing 1 to 8, 0 to 7 or whatever)? I am certain the answer is no, but I am interested to have that confirmed by someone who knows Access well (or queries in general and likes a challenge!). As a quick background, I am used to doing these sorts of things in MS SQL stored procs. I would create the kind of table you suggested as a temp table within the procedure and outer join that table back to my query containing the data all as you have outlined. The advantage of stored procs is that when they go out of scope, all the temp tables I have created are destroyed with them. I am worried about creating the table because once I have the final resultset I want all the intermediary steps destroyed. This is coupled with the fact that the owner of this database wants it to be as codeless as possible. Hence the idea of forcing a query to return 8 records in a resultset (which seems to be the nearest thing to a temp table I can find in Access). Any suggestions as the best way to approach this? Edwinah63 ps not sure how i would go about forcing a query to return 8 empty records in ms sql either!! :-) so over to the floor! -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#6
|
|||
|
|||
Determining EVERY month between 2 given dates
FWIW,
here's Tony's explanation of how to use Temp Tables in Access.... http://www.granite.ab.ca/access/temptables.htm not quite the same as SQL Server, but the best you're going to do in Access... -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Determining EVERY month between 2 given dates
You can of course hard-code
some unioned queries to force the missing records to be included. D'oh! I completely forgot about creating a dynamic union query! Thank you for reminding me! Maybe something like this? public sub CreateAQuery(mthsBetween as integer) dim i as integer dim sql as string for i = 0 to mthsBetween sql = sql & "select " & i & " as Mth union " next i ------Can I do this next bit??--- Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name = 'MyUnionQuery' DROP QUERY MyUnionQuery;" CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql end sub The code above isn't quite right but you get the idea. Had a hunt around the internet for a "Create Query" statement. Is there one? Would prefer to stick to SQL statements wherever possible but will use querydefs etc otherwise. A big thank you to everyone who responded :-) |
#8
|
|||
|
|||
Determining EVERY month between 2 given dates
Edwinah63 wrote:
You can of course hard-code some unioned queries to force the missing records to be included. D'oh! I completely forgot about creating a dynamic union query! Thank you for reminding me! Maybe something like this? public sub CreateAQuery(mthsBetween as integer) dim i as integer dim sql as string for i = 0 to mthsBetween sql = sql & "select " & i & " as Mth union " next i ------Can I do this next bit??--- Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name = 'MyUnionQuery' DROP QUERY MyUnionQuery;" CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql end sub The code above isn't quite right but you get the idea. Had a hunt around the internet for a "Create Query" statement. Is there one? Would prefer to stick to SQL statements wherever possible but will use querydefs etc otherwise. A big thank you to everyone who responded :-) Aside from the test for existence: absolutely. In this case, it would be CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement If you parameterize it or create an action query, it would be CREATE PROCEDURE procedure [param1 datatype[, param2 datatype[, ...]] AS sqlstatement Online help has a well-hidden section on JetSQL, but I did manage to find it :-) Just hit F1, find the again well-hidden link on the help screen to get to the table of contents, and find the node called "Microsoft Jet SQL Reference" - the last bit isn't so hard :-) You would have to look at the querydefs collection (if using DAO) or the ADOX Views or Procedures collection (if using ADO) to find out if the query exists. But you know ... since you're already into the Querydefs collection, you might as well simply use a querydef object ... it will truly be temporary if you omit the qryname argument from the CreateQuerydef statement. Since you are using VBA, you're code won't be portable anyways, so you might as well do it the easy way. Oh! And don't forget: unlike Transact-SQL, Jet SQL requires a FROM clause with table expression. You don't have to actually retrieve any data from the table, but you do have to name one. Transact-SQl: Select 'a' as col1, 2 as col2 union Select 'b', 4 Jet SQL: Select TOP 1 'a' as col1, 2 as col2 FROM existingtable union Select TOP 1 'b', 4 FROM existingtable -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#9
|
|||
|
|||
Determining EVERY month between 2 given dates
Thanks for your reply. I am having trouble now with the Create View
Statement. To check I had the right syntax I created a dummy table with 1 field, 1 record. I then created the sample statement in the SQL view of both an ordinary and so called "Data Definition" query create view MyView as select dummyid from tblDummy Both come back with the error "Syntax error in Create Table statement" What am I doing wrong? Had a google around and it seems that Access 2007 will not recognize create view statement for this database unless the ANSI92 compatibility is turned on and the ANSI92 option under Access Options/ Object Designers/Query Design/Sql Server Compatible Syntax is greyed out. Database is an Access 2007 database looking at Access 2007 tables within the same database. I really want to avoid the whole querydefs thing if I can. Are you sure that JetSQL supports the DDL statements Create View/ Procedure? If so may I have the full steps please? |
#10
|
|||
|
|||
Determining EVERY month between 2 given dates
Edwinah63 wrote:
Thanks for your reply. I am having trouble now with the Create View Statement. To check I had the right syntax I created a dummy table with 1 field, 1 record. I then created the sample statement in the SQL view of both an ordinary and so called "Data Definition" query create view MyView as select dummyid from tblDummy Both come back with the error "Syntax error in Create Table statement" What am I doing wrong? Had a google around and it seems that Access 2007 will not recognize create view statement for this database unless the ANSI92 compatibility is turned on and the ANSI92 option under Access Options/ Object Designers/Query Design/Sql Server Compatible Syntax is greyed out. Database is an Access 2007 database looking at Access 2007 tables within the same database. I really want to avoid the whole querydefs thing if I can. Are you sure that JetSQL supports the DDL statements Create View/ Procedure? If so may I have the full steps please? I can't give you any more than what is in the online help .. sorry. In A2003, I did have to check the ANSI92 option for the database I was testing with in order to make the CREATE VIEW statement work in the SQL View of a query builder window. Without that option turned on, I got the same error you did. This snippet of code also failed until I turned on ANSI92: Sub testcreateview() Dim db As DAO.Database Set db = CurrentDb db.Execute "CREATE VIEW testqry AS select * from table1", dbFailOnError End Sub If you cannot turn on that option, it appears you will have to resort to the querydef (or ADOX Views) method. I'm baffled as to why you are so desperate to avoid it. Is it because of the need to create a Reference to DAO? If so, you can do that in code by using the Application object's References collection, which has two methods for creating references: AddFromFile and AddFromGuid. Here is an example of the latter: Dim ref As Reference, DAOfound As Boolean DAOfound = False For Each ref In Application.References If ref.Name = "DAO" Then DAOfound = True exit for End If Next ref If Not DAOfound Then Application.References.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 3, 6 End If They are very simple to use. You don't even have to drop the querydef if you've already created it - just set the .SQL property to the new sql statement, like this: sub createqry() dim db as database,qdf as querydef,strsql as string 'build your string set db=currentdb on error resume next set qdf=db.querydefs("qryname") if err 0 then set qdf=db.createquerydef("qryname") qdf.sql=strsql end sub -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
|
Thread Tools | |
Display Modes | |
|
|