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
981 Views

Similar Articles

[PageSpeed] 35

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:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

How to get paper size from dmPaperSize returned from GetDevMode?
For example, dmPaperSize returns 1 which is DMPAPER_LETTER, how do I calculate the paper size like in this case 8.5"x11"? There are so many paper sizes, is there any function call to calculate based on the return value of dmPaperSize or mapping between the value of dmPapersize and actual size? Thank you. The reason I asked the question was because dmPaperLength and dmPaperWidth are 0 for some printers. Also, my HP laserjet returns as a color printer (dmColor = 2) from GetDevMode call. Anyone knows why? ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

Appending in a sub form
I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has info about Venue, date, time, event with a VenueEventID (key field) which is linked to tblTicketInfo which has section, seat, row, etc. I set up an entry form completing VenueEvent info with a sub form for ticket info. Some of the venues have a standard set of seats that I want to "auto populate" into the sub fom. I have set up a Venue table with the standard seats for the venue. I can set up an Append Query on the sub form, which does append the tblTicketInfo, but does not capture the VenueEven...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Money 30 Day Money Back Guarantee; instructions.
I tried out Money 2004 Small Business with an idea I might switch from quicken 2003 Home and Business. For a number of reasons that involve primairly invoices and Stock transaction updates, I'm sticking with Quicken. I purchased Money at Staples. At that time I asked the clerk about returning the product and he said I 'd have to deal directly with Microsoft. On the Money box there is a Money-Back 30 Day Guarantee Seal and that's all. No specific instructions as to what to do, where to send etc. If someone has this information please share it or directl me to where I might it...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

makro to return email
I want a makro that if a check box is checked, a email is send to a recipient. See your other mail and http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Esrei" <anonymous@discussions.microsoft.com> wrote in message news:174801c50514$05025190$a401280a@phx.gbl... >I want a makro that if a check box is checked, a email is > send to a recipient. ...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

Combining Lists within excel.
I'm hoping someone can help here. I'll try to explain the problem I have. I have 2 worksheets both containing a product list. I have entries on one worksheet which I need on the other, so basically my question is... might there be a way of combining these two lists so one updates the other with the missing entries. Help on this would be great, as they are very large lists which I dont much fancy the thought of looking through them manually. Are you familiar with the VLOOKUP function? I use it all the time for this type of problem. vlookup(Product cell in first sheet, Array...

Import excel named range into Access error 3011
Hi, I've read a few posts on this topic, but none addresses my particular problem. I use Office 2003 on an XP SP3 machine. I am using transferspreadsheet to import a named range in an excel file into Access. This has worked beautifully until suddenly it stopped working. I find now that if I extract the address of the named range from within the Access VBA code, it looks like this: =#REF!$A$1:$Z$166 rather than this, which is what it should be: =Data!$A$1:$Z$166 When I open the spreadsheet and get the address of the named range in the immediate window, it also has the #REF error....

merging data
Hi all:) I'm new to this forum and to to Excel (2000) and not sure that I'm asking this question correctly but here is what I'm trying to do. The data on my work sheet is pasted from another sorce and includes information: name, ref #, time, date. I use this information looking up the status of an order and enter a "status note" in 5th (last) column. As the day progresses, the source that the data comes from is copied and pasted under the current list I already have going and much of the data on the lower list is a duplicate. How can I merge the to list so that I hav...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

Null date parameter
Hi How can I pass a null value to a date parameter in a Sub? Thanks Regards hi John, John wrote: > How can I pass a null value to a date parameter in a Sub? you have to pass it as Varian as normal data types cannot be Null: Public Sub yourSub(ADateNullable As Variant) On Local Error GoTo LocalError Dim DateValue As Date If IsNull(ADateNullable) Then Else DateValue = CDate(ADateNullable) End IF Exit Sub LocalError: If Err.Number = 13 Then ' Type mismatch. End If End Sub mfG --> stefan <-- ...

auto fill in data when changing fields
i new to crm 4 and i would like to; once i selected the account i need it to grab the main phone and put it in the phone field, and could you tell me where i need to do this, thanks -- j.hardy you can modify the mappings of the corresponding relationship "moon" wrote: > i new to crm 4 and i would like to; once i selected the account i need it to > grab the main phone and put it in the phone field, and could you tell me > where i need to do this, thanks > -- > j.hardy ...