 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008create date records
Hi, Been struggling with this to no avail. I have a begin date record and a end date record, and I would like to create the records in another table that corrospond. Example, table a has 1-1-80 for the begin and 1-10-80 for the end, I want to create 10 records in table b: 1-1, 1-2, 1-3, and so on. Is a query the correct way to do this? I tried append, and update but I am just not getting it. TIA This would be probaby an append query but your description is too vague for us to be able to understand what you want. what do you mean 1-1, 1-2. 1-3? Give us table names, field names and data types. Evi > Hi, > Been struggling with this to no avail. [quoted text clipped - 7 lines] > > TIA Sorry about being vague, I will try again. I have tableA which has 2 fields, StartDate, and EndDate, which both prompt for a date. Next, I want to create records in tableB, which are all the dates from StartDate to EndDate. for example, StartDate=January 1, 1980, EndDate=January 10, 1980. I want the query to create these records in tableB: January 1, 1980, January 2, 1980, January 3, 1980, and so on, up to January 10, 1980. Is this a job for a query? I've spent a lot of the day trying to figure this one out, and its eluding me!! > This would be probaby an append query but your description is too vague for > us to be able to understand what you want. [quoted text clipped - 17 lines] > > > > TIA Got it. You want all the combinations of start and end date. Time to use Dale Fye's number table again. Create a table TblNumbers with one field intNumber Enter the numbers 0 to 9 Create a new query (name it QryNumbers) Click on the View button and paste in the following (each line begins with a word in capitals so mend this if the email breaks it up SELECT hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber AS intNumber FROM TblNumbers AS Hundreds, TblNumbers AS Tens, TblNumbers AS Ones ORDER BY hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber; You will now have a series of numbers going 1 to 1000 Step 1: Click on the (closed) QryNumbers and go to Insert Query. Drag intNumber into this new query (QryStartDate) In the next column type StartDates: #01/01/2008#+[intNumber] to generate a sequence of 1000 dates starting from 01/01/08 Filter this query so that it only shows the dates you require Repeat the above from Step1 but this time, call the query QryEndDate and your function will now say QryEndDate, again, filter the query so that it shows the same series of dates Create yet another query. Add both QryStartDate and QryEndDate to the grid but don't join them in any way. Click the downarrow next to the QueryType button Choose Append Query Append these dates to your table Evi > Sorry about being vague, > I will try again. [quoted text clipped - 30 lines] > > > > > > TIA Got it, Thanks for helping me Evi!! > Got it. You want all the combinations of start and end date. Time to use > Dale Fye's number table again. [quoted text clipped - 86 lines] > > > > > > > > TIA > You will now have a series of numbers going 1 to 1000 Actually, the series goes from 0 to 999 ;-) > Got it. You want all the combinations of start and end date. Time to use > Dale Fye's number table again. [quoted text clipped - 14 lines] > > ORDER BY hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber; > Step 1: Click on the (closed) QryNumbers and go to Insert Query. > [quoted text clipped - 68 lines] >> > > >> > > TIA Thanks Dale - your number table has had so many uses since you taught me how to do it. It has found missing cheque numbers, ensured that I could view a whole series of dates with a left join, allowed me to append dates - all stuff I'd been coding previously Evi > > You will now have a series of numbers going 1 to 1000 > [quoted text clipped - 93 lines] > >> > > > >> > > TIA Yeah, it really does come in handy. Glad you have found it so useful. Dale > Thanks Dale - your number table has had so many uses since you taught me > how [quoted text clipped - 106 lines] >> >> > > >> >> > > TIA M.Murphy Are you looking to insert the values from multiple records in your table? If so, I think the final query will look something like: INSERT INTO SomeTable (KeyValue, DateValues) SELECT yourTable.KeyValue, DateAdd("d", yourTable.StartDate, qryNumbers.intNumber) FROM yourtable, qryNumbers WHERE DateAdd("d", yourTable.StartDate, qryNumbers.intNumber) <= yourTable.EndDate This assumes that you want to insert the PK from YourTable, along with each of the dates between the start and end dates, for each of the records in yourTable, and that you want to insert them into SomeTable. HTH Dale > Got it. You want all the combinations of start and end date. Time to use > Dale Fye's number table again. [quoted text clipped - 89 lines] >> > > >> > > TIA |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.