Query problem

[mikew188]

I need to create a report that shows the hours a store is open.

I have a table that displays the store number, end of week date, Sunday Hours, Monday Hours, Tuesday Hours, etc. I have attached a small screenshot of the data format.

Some weeks the store hours for each day are the same. In that case, if the weeks are adjacent to each other, I would like to combine the dates into a date range. For example

Store Date Sun Mon Tues
A 11/23/08 10:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm

A 11/30/08 10:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm

A 12/07/08 9:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm

A 12/14/08 10:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm


Using the sample data above, I would like the output to be as follows.

Store Date Range Sun Mon Tues
A 11/23/08 - 11/30/08 10:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm
A 12/07/08 9:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm
A 12/14/08 - 11/30/08 10:00 am - 10:00 pm 8:00 am - 10:00 pm 8:00 am - 10:00 pm

Since the first two weeks have the same hours for each day, the two weeks are combined into one line with a date range.

The third week has different hours, so it is listed on a seperate line

The fourth week has the same hours as the first two weeks, but since it was seperated by week three, that had different hours, it is listed on a seperate line.

I am not sure how to accomplish this. If anyone has any way to make this happen I would greatly appreciate it.

screenshot.jpg

[FishVal]

Hello.

I recall something similar already discussed here.
However that is not an option for you until you normalize your database.

Regards,
Fish

[ChipR]

The only way I can see doing this is to open the recordset of the table and step through each record. Here's an outline of how it would work.

if records.eof then exit sub
lastStore = records!store
beginDate = records!date
lastDate = records!date
lastSun = records!Sun
lastMon = records!Mon
...
records.movenext
 
while not records.eof
  if not(records!store = lastStore & records!Sun = lastSun & records!Mon = lastMon ...) then
    'found the end of a date range
    strDateRange = iif(lastDate = beginDate, lastDate, beginDate & " - " lastDate)
    insert into NewTable values (lastStore, strDateRange, lastSun, lastMon...)
    lastStore=records!Store
    lastDate=records!Date
    beginDate=records!Date
    lastSun=records!Sun
    ...
  else
    lastDate=records!Date
  end if
  records.movenext
wend

So you start a date range, saving it's values. You move to the next record and compare it to the previous, and if it's different you need to insert into the table and start a new date range. If it's the same, you just move on to the next record, until you find the end of that range.