Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best approach (Access 97)

    I have a maintenance database. There are 26 preventive maintenance periods in the year. A piece of equipment may need to have PM done 1, 2, 3 or more times a year. I currently have one field for each period, as yes/no check boxes. But I am coming to the conclusion that may be a nightmare when trying to get results for period 12 or whatever. Is there a better approach to the issue. If I just use a text box with numbers entered like 1,4,8,12 you have to use *1* in the criteria to get the results. I don't want the user to have to do this. I created a query to test. I tried Like "*[Forms]![frmReports]![txtTest]*" and that didn't work. How do I filter for one period entered as a 1 in a textbox, data type of text, where there are multiple entries 1,4,8,12 etc. I am having the user come from a form that allows them to enter values for different reports.

    Thank you.
    Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best approach (Access 97)

    >>But I am coming to the conclusion that may be a nightmare

    That's what I meant in <post#=277308>post 277308</post#>. I would create a separate table with two fields: EquipmentID and MaintenancePeriod, plus (if necessary) additional fields containing period-specific info. This table would contain multiple records for one piece of equipment - if equipment item # 9002 has preventive maintenance in periods 1, 4, 8 and 12, there would be records

    <table border=1><td>EquipmentID</td><td>MaintenancePeriod</td><td align=right>9002</td><td align=right>1</td><td align=right>9002</td><td align=right>4</td><td align=right>9002</td><td align=right>8</td><td align=right>9002</td><td align=right>12</td></table>
    This table would be linked to tblEquipment on EquipmentID, with referential integrity set.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Best approach (Access 97)

    Try using :

    <pre>Like "*" & [Forms]![frmReports]![txtTest] & "*"</pre>


    But if you enter 1 in txtTest, then the query results would find anything with a 1 in it , including 12 etc.

    If you don't want to keep the 26 yes/no fields, the alternative would be to put the maintenance periods into a separate table. This would just have a key field, an itemid as a foreign key and a number (1 to 26).
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best approach (Access 97)

    I can't see the forest for the trees. I should have known that. Thanks. Sorry to take your time. Fay

Posting Permissions

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