Automated Rotating Schedule (Part deuce) (1 Viewer)

[mbhw99]

I know there was a reply by @CJ_London to this, but got wiped out after the back-up before I could implement his suggestion.

Please see the enclosed attachment.

What I would like to accomplish is to make a report similar to this, except make it automated so I won't have to change the dates. Users would be able to bring up a current schedule without worry that it's updated or not. The schedule repeats itself every 6 weeks.

I made a cross-tab query, but I must admit that I'm not too familiar with working on them.

Thank you for your time.

 

[CJ_London]

you should have received my post in the email advice you get from the forum - have a check and if it is not there, let me know and I'll try to remember what I said!

 

[mbhw99]

you should have received my post in the email advice you get from the forum - have a check and if it is not there, let me know and I'll try to remember what I said!

I have your first reply, but the 2nd doesn't show.

First, you need to store the data in access in a table with the following fields:

EmpName as Text
WeekCommencing as Date
ScheduleItem as Text

Then, when you create your crosstab query, set the row EmpName as row heading, WeekCommencing as column heading and ScheduleItem as Value - and also set the total row to First rather than sum

 

[mbhw99]

Yeah, I don't have an email for your 2nd reply @CJ_London. If I remember correctly, you hit enter by accident. lol

Could I pick your brain again for that info? Thanks again for your help.

 

[CJ_London]

It was along the folloiwng lines

First, you need to store the data in access in a table with the following fields:

EmpName as Text
WeekCommencing as Date
ScheduleItem as Text

Then, when you create your crosstab query, set the row EmpName as row heading, WeekCommencing as column heading and ScheduleItem as Value - and also set the total row to First rather than sum for the scheduleitem column.

My other suggestion was to do with the fact that the schedule repeats every 6 weeks - so rather than having a weekcommencing field have a WeekNo field (numeric)

In this way by starting with a suitable reference start (say 8/4/2013) and by using a suitable algorythm, any week commencing date can be calculated by adding Weekno*7 to the reference date - recommend you use weeknos 0-5 rather than 1-6 to keep the calculation simpler.

This will keep the amount of data you need to store and update to a minimum

 

[mbhw99]

Thank you for the help. Although these suggestions did produce, I was wondering if there was any way to make this somewhat perpetual (ie: automatically insert the date based on the beginning of the week). This way the schedule won't have to be manually updated.

 

[CJ_London]

perhaps have another table containing a date that is a monday, then simply add 7 days * number of cycles?

 

[mbhw99]

Perhaps I was a little hasty with the first suggestions. They are not showing up like the spreadsheet. I added fields to the table ScheduleItem1-6, and added the data like in the spreadsheet. That is data that I would like to stay the same (and repeats in 6 weeks) with the employee names.

I'm not too sure if a cross-tab query would be the best course of action, but am still open to it if it will work.

 

[CJ_London]

I've attached a small db which you can adapt to do what you require

 

[mbhw99]

I've attached a small db which you can adapt to do what you require

I'm afraid I cannot open it.

 

[CJ_London]

which version of access are you using?

 

[mbhw99]

xp. dern work computers.

 

[CJ_London]

you do need to copy it to your desktop before opening

 

[mbhw99]

:/ The start menu say xp...about file says '02.

Says it is an unrecognized file format.

 

[CJ_London]

So are you using access 2003? or 2000 or 97?

 

[mbhw99]

In the About MS under the help tab, it says '02 SP3.
Running Windows 7.

 

[CJ_London]

That is the operating system.

Do you have Access?

When you open the Access program and go to help you should see something like the attached - it will be the same in Excel

 

[mbhw99]

I wish...for some reason or another, our Access doesn't mirror what version of Office we have. Everything but Access is Office '07.

 

[CJ_London]

So why can't you open access, click on help and see what version it is?

 

[mbhw99]

In the About MS under the help tab, it says '02 SP3.
Running Windows 7.

I'm sorry, I might not have explained it correctly. In Access, under help>about, it says the version is 2002.