Hi - trying to solve this. Hope someone can help

I have a database where each customer has a 'start' and 'end' week and are assigned to a location. Several customers can be assigned to the same location for any length of weeks.

I need to create a report by location based on a start and end range. I.e. I tell the report "for location x, starting on week y and ending on week z, which customers will I have for each week"

So basically i need the report to create new rows starting with the start week and continuing for each subsequent week until the end week, populating the rows with the customer data (even if there are no customers for that particular week)

Hope thats clear and someone can point me in the right direction - driving me nuts!

One solution would be to create an unbound form with controls such as a combo box containing your locations and week numbers (if they are numbered?) or calendar control if you want to use dates. Its better to set the data source for these controls to values stored in tables (i.e. if your locations are stored in a table set the combo box data source to this table) as this will make updating/editing data easier.
You can then set up a query that uses the values you select in the controls as the criteria for selecting fields by setting the query field criteria to forms!yourformname!yourcontrolname meaning that the query will search based on the selections you make on your form.
If you base your report on this query you can then format the report so that it is sorted/grouped by location. Run this report from a command button on the form.

If you need any more help just let me know,
Hope this helps.

Dave

Thanks for the tips Dave. I've managed to set up the query to look for all customers whose start and end week fall within the range specified on my unbound form.

This uses the query criteria:

Start Week
<=[Forms]![unbound form]![end week]

End Week
>=[Forms]![unbound form]![start week]

Unfortunately the query won't also allow me to limit by the location specified on my form too as its obviously one crietria too many! Anyway, not a problem, as I can base my report on the query and use the command button to limit to my report to the location on my unbound form.

However, what i now want to do is set up my report to group all the customers together for each consecutive week, as specified in the range on my unbound form.

E.g. if I specify the start week as 2 and the end week as 4 on my unbound form, and the location as UK. So my query will return all customers who fall anywhere with that range, and my report will limit to the UK. Now I want to set up the report to show me the customers for week 2, then the ones for week 3, then for week 4 etc

Is this even possible??

An alternative to using your command button would be to simply use your query (with week criteria) as the data source for another query (with your location criteria).

As for your report, yes this is possible. If you open your report in design view. Near the top left hand corner you should see a black square. Right click on that square and select sorting and grouping (see attached screen shot1). From here you will be able to select either you StartDate or EndDate and then choose whether you want a group header/footer (i.e. for each week you can have a heading - Week Number and a footer counting the number of customers), group criteria, group intervals and whether or not you want to keep all data for one group together (see screen shot2).

Hope this answers your question,
Dave

Maybe I've understood incorrectly, but i don't think that works in the way i want.

If I group by start week for example, it groups together all the customers that fall within my week range (E.g. 1 to 5)

But what i want is to group the report out by individual week. So I would have a header for Week 1, then a list of customers, then another header for Week 2 and the list of customers and so on until the end of my range.

Does that make sense?

Cheers for the tip on querying a query too by the way! never thought of trying that :-)

My apologies, I misread your post.

I am assuming that your week numbers run on a yearly basis (i.e. 1-52)? If this is the case then you could set up a table with one field called Week which contains values 1 - 52.
You could then add this table to the query that is the source of your report. Drop the field week into your query and you'll see you duplicate each record 52 times. Clearly this isn't what your looking for but you are looking for each instance whereby Week is 'Between [StartWeek] and [EndWeek]'. This will give you an occurrence of each record during the range.
If you then sort/group your report by Week rather than StartWeek you should see a copy of each customer who falls in the range.

This isn't the tidiest solution in the world, it is just the one that sprung immediately to mind.

Hope this helps,
Dave

Wow - thanks, what a nifty workaround!! works perfectly

CHeers
Sid