multiple records from one

  • Thread starter Thread starter SuzyQ
  • Start date Start date
S

SuzyQ

I have a table that has an activity with a related table with start dates and
end dates for a particular category of activity such as planning phase,
construction phase, operating phase, completed, etc. I would like to create
a query for a report that will return the year of the activity to group on
the report by year, but the kicker is if the date range of multiple records
spans multiple years, I need a record for each date that the activity is
occuring. I know that I can use the min and max function when grouping the
related records together to get a single record with a single date range for
a given activity, but what I need is a record for each year that the activity
is activly scheduled. I.E.

In my parent table I have an activity such as Bridge Construction (bridge
name)

In my child table for the related records I have (dates not actual, but
representative)

Planning 05/01/2010 09/01/2010
Construction 12/01/2010 03/01/2011


In a query I can get

by using the min and max function in my grouping of the records (I should
mention that I'm using the query builder), I can get one record like this

Bridge Construction (bridge name) 01/01/2010 03/01/2011

but what I want in this case would be two records

Bridge construction (bridge name) 2010
Bridge construction (bridge name) 2011

So that on my report I can list all the projects that are being performed in
each year. I would group my report by year and list all of the projects
occuring in that year. If a project spans years, it will be listed again on
the report under every year that it is actively scheduled. For this report,
I do not care about the specific date, only the year that it is scheduled. I
know how to get the year returned, but how do I get a record for each year in
the span. Sometimes the activity even spans more than two years. In my
overall date range turns out to be 01/01/2010 - 01/01/2020, then I would need
11 records in my resulting query - one for each year.


I hope I made myself clear. Thanks for any help you give me.
 
The years have to come from somewhere, so you'll need a counting table. One
field named (say) CountID, type Number, marked as primary key. Save the
table as tblCount. Enter a record for zero, then 1, 2, and so on -- for the
maximum number of years you need to handle.

Now create a query using your original table and tblCount. There must be no
join between the 2 tables in the upper pane of table design. This gives
every possible combination. Now type an expression like this into a fresh
column in the Field row:
TheYear: Year([MaxDate]) - Year([MinDate])
In the Criteria row under this enter:
<= [tblCount].[CountID]
This limits the query to one record for each year. The field will show zero
for the original year, 1 for the first follow-up year etc.

If you need to create a table like this, you can turn the query into a Make
Table.
 
Back
Top