0

I have a table date_days which contain of 1 field attdate as date

I want to create a query that accept 1 or 2 parameter. ie :

QueryGetDays(#01/06/2013#,#30/06/2013#)
QueryGetDays(#01/06/2013#) and the query can tell the end of month by itself.

This query will delete all existing records in date_days, and insert dates from 01/06/2013 to 30/06/2013 in table date_days

ofcource I can generate the dates and do the insert from vb.net

But I wonder if that can be done using query in ACCESS ?

Samir Ibrahim
  • 92
  • 9

2 Answers2

1

I like a challenge. If we create a dummy table (named dummytable) with a single field (dummydate), and add a single record of 0 (or any other number), we can run the following query. Enter the first date of the month (1/6/2012) in the parameter box that pops up and it will populate all dates between 1/6/2012 and 30/6/2012. Entering 1/2/2012 will enter dates up-to 29/2/2012.

PARAMETERS [Enter date] DateTime;
INSERT INTO date_days (attdate) 
SELECT [Enter date]+dummydate  
FROM (
        SELECT 0 AS dummydate FROM dummytable
        UNION SELECT 1 FROM dummytable 
        UNION SELECT 2 FROM dummytable 
        UNION SELECT 3 FROM dummytable 
        UNION SELECT 4 FROM dummytable 
        UNION SELECT 5 FROM dummytable
        UNION SELECT 6 FROM dummytable 
        UNION SELECT 7 FROM dummytable
        UNION SELECT 8 FROM dummytable 
        UNION SELECT 9 FROM dummytable 
        -- fill in the rest here..
        UNION SELECT 24 FROM dummytable 
        UNION SELECT 25 FROM dummytable 
        UNION SELECT 26 FROM dummytable 
        UNION SELECT 27 FROM dummytable
        UNION SELECT 28 FROM dummytable 
        UNION SELECT 29 FROM dummytable
        UNION SELECT 30 FROM dummytable 
) WHERE Month([Enter date]+dummydate) = Month([Enter date]);

A DELETE query would need to be run separately.

Andy G
  • 19,232
  • 5
  • 47
  • 69
  • @SamirIbrahim Thank you for the nice comment ;). It took a little effort but, now that it works, it could be extended to do other similar things. – Andy G Jul 03 '13 at 18:34
  • @JohnnyBones. Yes, it is inefficient ;). I would perhaps, instead, create a table containing the numbers 1-31. I just enjoyed the challenge. However, it is only ever going to create a maximum of 31 new rows, so I wouldn't, personally be too concerned about its efficiency, unless I were using it repeatedly. – Andy G Jul 03 '13 at 18:59
  • @JohnnyBones : "incredibly ineffeicnet peice of code". I advice you not to calim knowledge of things that your understanding of the requirement is not more than ZERO. I had a user input 2 date, and the filter will run on attendance table, then by collecting data by running 4 query, finaly I apply crosstab query using the created table with Andy answer to produce a grid showing day 1 to 31 on single grid row. So its bettwe that you stay silent better than say negative usless un-needed comment such the above or else my response to you or any one like you will not be nice as this response. – Samir Ibrahim Jul 03 '13 at 19:34
  • @AndyG : I have this table created, and I have fill it with dates from 01/06/2013 to 30/06/2013.. now If i want to work on July, I had to delete the table and fill it by day 01/07/2013 to 31/07/2013 and apply it to crosstab query. The question is how to fill it? by code? I have done it, and its working good and the result are correct. But you know something? Deleteing rows and Inserting rows are slower than creating the table in way you did by preparing the table by query (it is much faster)... any way.. if you also think your code is inefficient, please do it a lot :) .. thanks again. – Samir Ibrahim Jul 03 '13 at 22:50
  • Sorry, Samir, putting 30 UNION SELECTs in a query is just not practical. Does it work? Obviously. Is it efficient? Nope. Andy admitted as much. So, please check your facts before you try to tell me what I do or do not know. – Johnny Bones Jul 04 '13 at 19:00
  • @JohnnyBones : I don't want to enter with "Aflatonic argument" with you about effeicient or not effecient. Just take a look at this post which look like my question but it is in SQL Server using 28 UNION and with 59 upvote [link]http://stackoverflow.com/questions/2157282/generate-days-from-date-range so what is accepted in sql server is not accepted in ACCESS? – Samir Ibrahim Jul 04 '13 at 21:32
  • sorry, the above link its for MySQL, not SQL Server solution. but my question still stand. – Samir Ibrahim Jul 04 '13 at 21:52
  • You're not getting it. Just because something CAN be done, doesn't mean it SHOULD be done, or it's the best way to do it. Please, don't make me laugh at you more. You obviously don't know much about coding, which is evident because you had to ask this question in the first place. Making 2 tables to store a months worth of dates is STUPID. End of story. – Johnny Bones Jul 05 '13 at 03:42
  • You are the one who is not getting it. You are still continue to be stupid enough to prove that something is still is not effecient while it is effecient just because you said so? who do you thing yourt self? I am the one who asked this question, I am the one who is judging the on the result of the post not an ignorant person like you who all what he said is seeing a useless code. You know, go pickup someone from your stupid level and argue with him. I realy hate and disguise people on your shape who they consider "useless" and "uneffecient" is there best answer. Go hang your self. – Samir Ibrahim Jul 05 '13 at 07:23
  • There is nothing wrong, or stupid, about creating a table to store a list of determined values. It is sometimes the best (the simplest AND most efficient) solution. – Andy G Jul 05 '13 at 11:09
  • Andy - This method requires TWO tables to store 30 values. If that doesn't sound inefficient to you, then you need a class on database design. – Johnny Bones Jul 09 '13 at 19:47
  • This sentence is very efficient. – Andy G Jul 10 '13 at 11:51
0

Well, a much more efficient way to do this is to create a table named tblNos with a single field named Nos containing ONLY the numbers 0 to 30. Then run this query:

PARAMETERS [Enter date] DateTime;
INSERT INTO date_days ( attdate )
SELECT [Enter date]+Nos FROM tblNos
WHERE Month([Enter date]+Nos) = Month([Enter date]);
Andy G
  • 19,232
  • 5
  • 47
  • 69