Append Query - return data for each day within a date range

I have a table "Rates" with the following data:

StartDate: 5/15/08   End Date: 6/23/08  Rate: $53
StartDate: 6/24/08   End Date: 7/15/08  Rate: $86
StartDate: 7/16/08   EndDate:  9/19/08  Rate: $99

I have another table "Transactions".  I want to create an append query such 
that if I supply the StartDate and EndDate, the query will populate the 
"Transactons" table with date and rate data from the "Rates" table for each 
and every day within the date range.  Example:  If I supply the StartDate of 
6/23/08 and the EndDate of 6/25/08, the query would populate the 
"Transactions" table with three entries:

Date: 6/23/08,  Rate: 53
Date: 6/24/08,  Rate: 86
Date: 6/25/08,  Rate: 86

Thanks,
Bob

0
Utf
2/22/2008 6:59:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1059 Views

Similar Articles

[PageSpeed] 1

Bob,

Create another table (tbl_Numbers) with a single field intNumbers containing 
values from 0 to 9.  Create a query (qry_Numbers) from this that looks like:

SELECT Tens.intNumbers * 10 + Ones.intNumbers as intNumbers
FROM tbl_Numbers Tens, tbl_Numbers Ones

This query will now give you the numbers from 0 to 99.

Your new query will look like:

INSERT INTO tbl_Transactions (RateDate, Rate) 
SELECT Rates.StartDate + qryNumbers.intNumbers,
            Rates.Rate
FROM Rates, qryNumbers
WHERE Rates.StartDate + qryNumbers.intNumbers
BETWEEN Rates.StartDate AND Rates.EndDate

As long as the start and end dates in Rates are not more than 99 days apart, 
this will work.  If they could be further apart, rewrite qryNumbers to 
include another instance of tbl_Numbers aliased as Hundreds.  It would look 
like:

SELECT Hundreds.intNumbers * 100 + _
            Tens.intNumbers * 10 + _
            Ones.intNumbers as intNumbers
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

HTH
Dale
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"capemaybob" wrote:

> I have a table "Rates" with the following data:
> 
> StartDate: 5/15/08   End Date: 6/23/08  Rate: $53
> StartDate: 6/24/08   End Date: 7/15/08  Rate: $86
> StartDate: 7/16/08   EndDate:  9/19/08  Rate: $99
> 
> I have another table "Transactions".  I want to create an append query such 
> that if I supply the StartDate and EndDate, the query will populate the 
> "Transactons" table with date and rate data from the "Rates" table for each 
> and every day within the date range.  Example:  If I supply the StartDate of 
> 6/23/08 and the EndDate of 6/25/08, the query would populate the 
> "Transactions" table with three entries:
> 
> Date: 6/23/08,  Rate: 53
> Date: 6/24/08,  Rate: 86
> Date: 6/25/08,  Rate: 86
> 
> Thanks,
> Bob
> 
0
Utf
2/22/2008 8:59:00 PM
SELECT a.itemID, a.transactionDate, b.rate
FROM transactions AS a INNER JOIN rates AS b
    ON a.itemID = b.itemID
        AND a.transactionDate >= b.startDate
        AND a.transactionDate < Nz(b.endDate, 1+Date() )


should do.  Note that I assumed the rates are dependant of some ItemID (or 
type of service, or whatever).  I also assume that if your table of rates 
have an endDate set to NULL, that means you don't really know when the price 
will end, ie, it is the 'actual' rate, and until further notice, it is still 
the valid rate, for today.



Hoping it may help,
Vanderghast, Access MVP


"capemaybob" <capemaybob@discussions.microsoft.com> wrote in message 
news:E61B4B49-99E7-461E-A360-625664B1E74F@microsoft.com...
>I have a table "Rates" with the following data:
>
> StartDate: 5/15/08   End Date: 6/23/08  Rate: $53
> StartDate: 6/24/08   End Date: 7/15/08  Rate: $86
> StartDate: 7/16/08   EndDate:  9/19/08  Rate: $99
>
> I have another table "Transactions".  I want to create an append query 
> such
> that if I supply the StartDate and EndDate, the query will populate the
> "Transactons" table with date and rate data from the "Rates" table for 
> each
> and every day within the date range.  Example:  If I supply the StartDate 
> of
> 6/23/08 and the EndDate of 6/25/08, the query would populate the
> "Transactions" table with three entries:
>
> Date: 6/23/08,  Rate: 53
> Date: 6/24/08,  Rate: 86
> Date: 6/25/08,  Rate: 86
>
> Thanks,
> Bob
> 


0
Michel
2/22/2008 9:07:15 PM
Reply:

Similar Artilces:

Calendar Start Date
I need to make a yearly (academic) calendar that starts with the month of September. When I click on change the date after choosing the yearly option, I can only change the year, not the month. How can I make a yearly calendar with a different start month? What version Publisher are you using? Do you want a one page calendar? In Publisher 2000, there is a date on the taskbar. Change the date from Sept. to whenever you want the end. Publisher will make a number of pages for the requested months. When this is finished click yearly. You will have to ungroup the calendar and adjust the a...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

Line column on 2 axes
Hello, I am running Excel 2003. I am creating a line column chart on 2 axes. Revenue is on second y- axis; headcount on first y-axis. Headcount is grouped into a number of categories (13). I would like to have each of the 13 categories as a stacked column. However, when I try and stack the 13 categories, it works only as far as the 6th value. Can anyone suggest a solution. Thanks Don't use the built-in chart type. Use all of your data to create a stacked column chart. Select a series that you want as a line, go to Chart menu > Chart Type, and select a line type. Repeat for a...

Find Sub Folder Name within a Folder
Hi Finding folder extn's is fine but to just find a folder name within a folder has me beat. I have a folder named Music this has 900 sub folders by each artists name I want to check the date modified for each sub folder. I have looked at findfirst findnext API but not sure how to adapt it to find sub folder names. Can you help On 12/07/2010 11:51, LondonLad wrote: > Hi > Finding folder extn's is fine but to just find a folder name within a folder > has me beat. Dave O replied to your original question over an hour ago. -- Dee Earley (dee.earley@icode.co...

use data on master worksheet for sorting #2
The data is a list of people, the projects they are working on, th completion date, who they work for, their skill set, etc. My manage wants this data sorted several different ways ie by skill set completion date and manager. Several different people will be updating the data and I don't want t rely on them to copy and paste it to the additional worksheets the sort it properly. I saw some VBA code on this site that copies from multiple sheets t one sheet. How would I copy one sheet to multiple sheets then sort th data every time it is changed -- cwjoh ----------------------------...

Date Prompt for Worksheet range
I have a workbook and would to insert a worksheet that will capture and add data in a separate worksheet based upon the prompt for date range that is entered by the user. My question is two fold: 1. How can I create a prompt for the user to enter a date range? 2. How can I create a macro that will capture the data from another worksheet and create a new worksheet based upon the date range? Any help would be appreciated. Happy Holidays, In answer to question 1 this method from Ron de Bruin allows the user to select the date from a calendar. http://www.rondebruin.nl/calendar.htm F...

Data transfer problem
I am working on a project that deals with two sets of information. M first set of information includes: A list of all the state abbreviations in the first column, the secon column has prices. E.X. AL (A1), AR (A2) ....and so on all the way through the 5 states. EX. $1.14(B1), $1.20(B2) ex--so AL has a price of $1.14, $AR has a price of 1.2 and so on... My second set of information includes: A list of state abbreviations, out of alphabetical order. I want to be able to merge these two sets of information around th prices. I do not want to go through the list and find each A abbrev...

How do I set end-of-range to #rows from beginning
Specifically, I am creating a chart that will have a breadth (# data points) based on a variable (value of a cell). Start point is always the same. For instance, a table is created based on a formula. The chart should stop when values in the table reach a certain value, but this could be 20 or 200 datapoints (20-200 rows). I know how many datapoints should be included, but not how to terminate the graph based on this number. I would prefer to do this without VBA, as I am not particularly proficient, but will if the only way. Hi, It would be nice to see some sample data, with a...

Macros within formulas
Hey all, Please let me know if it is possible to insert Macros in a formula. That is "if this condition is true, then run macro1 else macro 2". If the above is not possible, let me know a procedure for this. 1> I need to create a macro which i) Segregates all the users by their name. ii) Calculate the time taken for their work. iii) Create a report for the time frame taken for the task. Now the problem is 1) when filtered, and sorted, i) The time when subratcted from the previous task to present task works. But when the first user data is finished, the time shows neg...

Unmatched query wizard??
I have succesfully made a Unmatched query using the wiz. I changed the query to a delete query, but when I run it, it asks me which table do i want to delete the records from. I keep screaming at it 'what do you mean which table isn't it obvious', but that doesn't work. Can anyone tell me how to overcome this please. Regards Geeves1293 Use a subquery to select the unmatched records This example deletes records in tblInvoice that have no matching records in tblInvoiceDetail: DELETE FROM tblInvoice WHERE NOT EXISTS (SELECT InvoiceID FROM tblInvoiceDetail ...

Date Extension...
FYI: Saw this on the Quicken support forum - converting MS Money section. They asked for an extension for expiration, and got it from MS. To request, email Microsoft Support from within MS Money - Help Menu --> Contact Us and request an extension to your expiration date. I did that via email. They replied the next day with an email on how to find the extend.exe and then a phone number to call. They asked for some info and then emailed me an extension code. Plugged it into Extend.exe and I am now good to go out to 2011. I was going to expire in 10/2009 otherwise. I hate to see...

Return date of an action
Hi, I have a gantt chart which has columns headed by dates A B C D E F 1 10/12 17/24 24/12 31/12 1/7 2 Do A 3 Finish B 4 Start C 5 6 7 I would like to put a formula in colum F to show the date that the action in the row will be completed by. The real worksheet has 52 columns in it so using an IF statement isn't an option. Each row will only have one entry in it. Thanks for any help Ben 9 10 So simple but I would never have got there. Thanks very much ...

Returning Formatting on a Lookup
I have a named range with values containing special formatting (i.e., underlined initial letters, bolding, etc) that I would like to have returned to another range based on a lookup(key) value. The lookup is successful (using VLOOKUP) however, the special formatting is not returned. Both field are text fields. Is there some way to perform a lookup and have the formatting returned as well as the value? You would need a macro to "look back" and copy the format from the source to the destination (vlookup() result). -- Gary''s Student - gsnu201001 "K...

ActiveWorkbook.Protect password:=range("rangename") does not work
I am trying to have macro protecting a workbook, with a password specified by an administrator in one of the hidden worksheets. The cell in the hidden worksheet in which the password is specified is named, so I want to use the range name in the macro that protects/unprotects the workbook. It all works well when I try to unprotect the workbook, using the code : ActiveWorkbook.Unprotect password:=range("rangename"). However, I get an error when I try to protect the workbook with the code: ActiveWorkbook.Protect password:=range("rangename") The error message is: Runtime Err...

couting occurence using multiple dates in single cell
Hi, I need to count the no. of meeting that took place in each month. My worksheet is January Feb to Dec column A column B Column C to Column M Column C (data) 1 name no of meeting No of meeting 2 Jack 1/1/09, 1/3/09, 2/1/09, 2/5/09, 3/1/09.4/1/09 3 Peter 2/1/09, 3/1/09,3/15/09 4 Paul 3/1/09, 4/1/09, 4/2/09 is there a way to count the ...

How many OR conditions can be in a query?
I know you can only have up to 9 OR conditions in a query if you use the grid, but if you type the OR conditions all on the criteria line, how many can you have? Kim There are functions you might use instead of multiple OR's -- for example, in the selection criterion "cell", you might use: In('Red','Green','Yellow','Blue','Puce') Regards Jeff Boyce Microsoft Office/Access MVP "KimPotter" <KimPotter@discussions.microsoft.com> wrote in message news:94905619-D13A-4B2B-84ED-8D5338CC8D37@microsoft.com... >I know...

No answer yet... Data Import from Many Worksheets to on file...
I am having 12 files in different network locations. Each havinf one worksheet inside. I just want to know whether I can create one file in my hard drive and create 12 worksheets and Import External Data to each worksheet from the different files so that I can see all of those worksheets in indifferent network locations into one file. I tried the External Data Import & Refresh but the command refreshes only the cells that contained data at the time of setting up. New cells edited doesn't appear in my amalgamated worskbook (also cells that contain formulas in the source files, wh...

Help writing an SQL query.
Hi all. I'm new to access and trying to write a contacts database for a college project. A contact can have many disciplines, and each discipline can have many subdisciplines. This means the contact can have many subdisciplines. I have created a disicpline and subdicipline table, created a realtionship between the 2. I have a subform which allows the user to select the discipline and subdiscipline from a table that links the two tables. What I cannot do though is filter the subform so that when the user selects a discipline, only the relevant subdisciplines are shown. How can I do th...

FRx Range Limitations
I am planning to use the FRx reporting tool to build reports where I need to filter based on From To situation, in the accounts field and segment field, any one have an idea about that ... -- Technical Manager ZFP-IT +966509765966 ...

Can't access data base
I was just writing some code for a form that I wanted to limit its use based on the group that the current user belongs to. I was attempting to test this after creating a user that was not in the group. After doing so, when I start the data base (the fe) I can't access it. It says I don't have proper permission to get to it, without ever letting me attempt to log on. I need help bad if someone can help me. Thanks, Rob ...

HOWTO query by passing a parameter from Infopath 20007 to a Microsoft Access 2007 database?
Hello, I have a Microsoft Access 2007 database foobar.accdb with two tables Student(ID|FirstName|LastName) and Scores(ID|ScoreA|ScoreB) with a primary-foreign key relation on the field 'ID'. I want to create an Infopath form that can submit new students to the 'Student' table and also be able to retreive information from the 'Scores' table. The Infopath form should be able to take 'ID' field as user input and then query based on it. I created a data connection to the database using the wizard. I created a submit connection first to the 'Student&...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Data Type Mismatch in Criteria Expression
In Access 2003 I have a calculated field in a query that is converting a string of numbers into a date format. The formula for doing this is... TscDate: DateSerial(Left([DateInput],4),Mid([DateInput],5,2),Right([DateInput],2)). The formula works fine but when I enter criteria for the calculated field (criteria example Between #2010/01/01# and #2010/01/31 I receive a Data Type Mismatch in Criteria Expression. I am stumped about what I am doing wrong. Any help would be appreciated. Thanks. JoeP I would try the following. Field: RealDate: IIF(IsDate(Format(DateInput,"...

Getting the max date after filtering out the deleted ones
Hello i have a little problem. my tblServiceReports has 3 fields that i need to manipulate. the fields are ProductID, ServiceReportDate and SRDateDeleted i also have a ServiceReportID field as the key for the table. each product in my db can have one to many service reports. what i am trying to do is create a query that pulls the max date for the service report so that i can show the last time the product was serviced. there is a catch, cause i know you think that what i just asked how to do in the last paragraph was easy. i also need to filter out bad data. what? filter BAD data, w...

Local Data Group
I'm familiar with creating Local Data Groups in Outlook. With the Offline client, a Local Data Group is automatically created to sync the owners contacts with the offline database. The problem is that when the user clicks the Contacts chekbox from Outlook | CRM | Options | Synchronization Tab it uses the same Local Data Group that is used for the Offline database. The problem is that I want ALL the contacts for all the accounts that I'm the owner for in my offline database. I do NOT want all those contacts im my Outlook contacts as there can be tens of thousands of contacts. In...