for each from/to date in table, select days between from/to date?

Hi

I have a 'NAMES' table:

NAME COLOUR FROMDATE TODATE
Fred Green 01-JAN-2006 04-JAN-2006
Bob Red 07-JAN-2006 09-JAN-2006
Alf Blue 12-JAN-2006 13-JAN-2006

In one query, for each row, I want to return a row for each day between the
FROMDATE and the TODATE, inclusive.

So, for the first row, you'd get:

NAME COLOUR DAY
Fred Green 01-JAN-2006
Fred Green 02-JAN-2006
Fred Green 03-JAN-2006
Fred Green 04-JAN-2006

etc. So overall, you'd get:

NAME COLOUR DAY
Fred Green 01-JAN-2006
Fred Green 02-JAN-2006
Fred Green 03-JAN-2006
Fred Green 04-JAN-2006
Bob Red 07-JAN-2006
Bob Red 08-JAN-2006
Bob Red 09-JAN-2006
Alf Blue 12-JAN-2006
Alf Blue 13-JAN-2006


Ideally, it'd be in one query. It looks easier than it is, unless I'm being
dumb.

Thanks you so much if you can help!!


.

Relevant Pages

  • Re: Help with counting only one time
    ... You want to exclude all but the first row for each order? ... In the following example, I will assume there is a primary key field named ID, and the table is called Table1. ... but I am clueless on what to do in the query even ...
    (microsoft.public.access.queries)
  • Re: "Numeric field overflow."
    ... Number data type, which really screwed up the works because the column in ... What was really nutty was that the append query ... However, if you tell it that the first row is a header row, ... If the source query has calculated fields, you may need to typecast the ...
    (microsoft.public.access.queries)
  • Re: "Numeric field overflow."
    ... Then query the temp table to ensure the data in every ... area - linked Excel spreadsheets. ... However, if you tell it that the first row is a header row, ... If the source query has calculated fields, you may need to typecast the ...
    (microsoft.public.access.queries)
  • Re: Easiest way to get first and last
    ... There is no guarantee that a Query or Recordset will remain ... Similarly, but in reverse, if you sort ... So basically, the first row, and last row, but 4 seperate records. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Crosstab Query Null Values
    ... due to the nature of my query I can't access the design view ... this means that the first row is not displayed when there are no records ... > in the Criteria row under the Column Heading field. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.queries)