Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking Unanswered: Need help creating a Loop

    Hello everyone,

    Back again and with a new situation. I have a database with a set of work schedules that we use. They actually are based on the person's days OFF. Like so:

    G 13b 5TF - Meaning, (on the 24-hr. clock) this schedule is 1:30 pm with Thursday and Friday off. (FIXED)

    G v1SM 5 5 6 8 8 - Meaning, Variable hours with Sunday and Monday Off and hours are Tues - Sat. 5am, 5am, 6am, 8pm, 8pm. (VARIABLE)

    What is needed? There are some schedules that are incorrect, and only based on the days off...5TF, v1SM as someone entered 5TW when it should be 5TF. Oh, the schedules are based on Sunday as the start of the week, hence 1SM(Sun,Mon) and 5TF(Thur,Fri) and so on. Now that there are some incorrect ones as noted above what I did was created 2 DeleteQueries. Here is the query:

    Code:
    SELECT tblSchedulesTest2.Schedule
    FROM tblSchedulesTest2
    WHERE (((tblSchedulesTest2.Schedule) Not Like "*v1SM*" And (tblSchedulesTest2.Schedule) Not Like "*V2MT*" And (tblSchedulesTest2.Schedule) Not Like "*V3TW*" And (tblSchedulesTest2.Schedule) Not Like "*V4WT*" And (tblSchedulesTest2.Schedule) Not Like "*V5TF*" And (tblSchedulesTest2.Schedule) Not Like "*V6FS*" And (tblSchedulesTest2.Schedule) Not Like "*V7SS*"));
    This one picks out the bad ones from the (Variable) Schedules
    The one below picks out the bad ones from the (Fixed)Schedules

    Code:
    SELECT tblNotLike2MT.Schedule INTO tblNotGoodSchedules
    FROM tblNotLike2MT
    WHERE (((tblNotLike2MT.Schedule) Not Like "*1SM*" And (tblNotLike2MT.Schedule) Not Like "*2MT*" And (tblNotLike2MT.Schedule) Not Like "*3TW*" And (tblNotLike2MT.Schedule) Not Like "*4WT*" And (tblNotLike2MT.Schedule) Not Like "*5TF*" And (tblNotLike2MT.Schedule) Not Like "*6FS*" And (tblNotLike2MT.Schedule) Not Like "*7SS*"));
    Now, that's rather lengthy and to me seems a bit much. But, I don't know how else to achieve my objective unless I do something like maybe a LOOP perhaps??? If by VBA that's good too as long as you can explain or show it simply. That was my thought on how to do it. If you have any way of helping me out on this I would appreciate it very much.

    have a nice one,
    BUD
    A2K3

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    I don't see anything wrong with these queries.
    You could generate the 1SM, 2MT, ... sequence in a loop, but since you already have them that seems a lot of work for nothing.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by ivon
    I don't see anything wrong with these queries.
    You could generate the 1SM, 2MT, ... sequence in a loop, but since you already have them that seems a lot of work for nothing.
    Hi Ivon and thanks for your response. What I was wanting to do was NOT to generate the schedule but instead, to weed out the Incorrect ones. Correct ones are as such: 1SM, 2MT, 3TW, 4WT, 5TF, 6FS, 7SS.
    There are however some that were entered incorrectly such as:
    1MT, 4TW, 6SS and so on.
    I did create the queries as that's about the only way that I myself know how to do it. But I did want to know if there was a way to create a Loop and how to do it. Still trying to learn some things and needing assistance in that respect. And if I had to do even more extensive amounts I would not want to have to create such a massive query if there was a better way.

    thanks,
    Bud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •