Very Challenging Question

[arnodys]

Hello, I have a table in my database which display supply and return of product from mon-fri. Field names are:

Date, Mon(Sup),Mon(Ret),Tue(Sup),Tue(Ret),Wed(Sup),Wed(Ret),
Thu(Sup),Thu(Ret),Fri(Sup),Fri(Ret)

The date is always a sunday's date which indicate the week ending where the sales reports is done.

I'm trying to display them in the following format:

    ThisWeek    |     LastWeek    |   WeekBefore    |
    | Sup | Ret |     | Sup | Ret |     | Sup | Ret |
-----------------------------------------------------
Mon |     |     | Mon |     |     | Mon |     |     |
Tue |     |     | Tue |     |     | Tue |     |     |
Wed |     |     | Wed |     |     | Wed |     |     |
Thu |     |     | Thu |     |     | Thu |     |     |
Fri |     |     | Fri |     |     | Fri |     |     |

The database will have a form to input current week ending and display the previous 2 weeks for analysis purpose. The report will then be printed for meetings copy.

The problem is, I don't know how to design the report to look into different dates from the same table.

Naturally, I would separate the table week by week and get a subreport to open each one of them. However, because the table is given by another department, I can't really touch it.

Can somebody help?

Cheers

 

[shudini]

Which date is being entered into the date field?

 

[arnodys]

Sorry for the confusion, this week report is always about previous weeks data. Hence, the input would be the last week ending date. For example, if I do the report today, the data would be for Week ending 4 March, 25Feb and 18 Feb. Such like:

WkEnding 04Mar07|WkEnding 25Feb07 |WkEnding 18Feb07 |
----------------|-----------------|-----------------|
    | Sup | Ret |     | Sup | Ret |     | Sup | Ret |
----------------|-----------------|-----------------|
Mon |     |     | Mon |     |     | Mon |     |     |
Tue |     |     | Tue |     |     | Tue |     |     |
Wed |     |     | Wed |     |     | Wed |     |     |
Thu |     |     | Thu |     |     | Thu |     |     |
Fri |     |     | Fri |     |     | Fri |     |     |

Thanks

 

[stopher]

Normally I'd suggest using a pivot query to shape the date. However, the layout of this report is quite fixed so I would suggest filling in the blanks with 30 Dlookup statements. With a date parameter and a little date manipulation, you can get all the required data for each day.

hth
Stopher

 

[Peter Reid]

What about having a query to return the 3 records and using columns on a report?

 

[stopher]

What about having a query to return the 3 records and using columns on a report?

damn good idea. Although there's still the matter of getting the days to appear under each other. A union query should fix that.
Stopher

 

[Peter Reid]

damn good idea. Although there's still the matter of getting the days to appear under each other. A union query should fix that.
Stopher

I thought the records were held by a week-ending date so the query condition would be something like,

[WeekEnding] between ([ReportDate]-15) AND [ReportDate]

(This assumes that there is only one record per week and that the correct week ending date is entered)