Replicating values for several columns

[jrayne]

I have a crosstab query: Column headings are Dates ; Row headings display job Titles and WorkDays. Values for each job are Hours/Day which fall under a particular "Start Date".

The numeric field "WorkDays" for each job indicates the number of times I'd like the "Hours/Day" to replicate itself from the first date forward. The crosstab query is based on another query that joins two tables and the "WorkDays" is likely to change based on user input.

My problem is that I don't know how to make the value "Hours/Day" display under any of the other days except for the "Start Date."

Example:
7/1/05 7/2/05 7/3/05
Job A 1 6.5
Job B 3 3.5 3.5 3.5

SQL
TRANSFORM First([Combo Query].[Hours/Day]) AS [FirstOfHours/Day]
SELECT [Combo Query].Title, [Combo Query].WorkDays
FROM [Combo Query]
WHERE ((([Combo Query].Date)>#7/01/2005# And ([Combo Query].Date)<#9/01/2005#))
GROUP BY [Combo Query].Title, [Combo Query].WorkDays
PIVOT [Combo Query].Date;

Any help is greatly appreciated.

 

[dhookom]

I would start by creating a table of dates (tblDates) with a date field (TheDate) and unique records for all dates. Add the table to your query and add TheDate to your query grid. Set the criteria under TheDate to:
Between [Date] and [Date]+WorkDays
Then divide the Hours/Day by WorkDays for the value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]