Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Join Queries (2000)

    I have a database which tracks boats and their maintenance, with the idea that it will produce reports/mailmerge when specific maintenance items are due (based on 6 or 12 months since they were last done). All working well as far as data entry and calculation of due dates.
    However when I try to produce a query showing items due in , say, the next month, I get into trouble.
    I am using a query showing the 6 separate maintenance items (eg antifouling, engine repair) with criteria for the due dates usings OR so that I can pick up any of the six items due in that month. However there is a logic flaw to the way I am doing it as the results show the due dates for all 6 items if any one of the items meets the query criteria. Thus is an antifoul is due in May, the query will also show all the other 5 items due dates even though they are in not in the month. I understand the program is doing exactly what I effectively asked for but wondered Is there a way I can pick up only the items that are due that month . Hope this makes sense
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Join Queries (2000)

    Can you tell us a bit more about the tables you are using for this, and about the query (for example its SQL)? Thanks.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join Queries (2000)

    Tables are:Customers,Boats,EngineManufacturer;BoatManufac turer
    Boats is the main table and has fields for CustomerName, BoatName, EngineManufacturer, BoatManufacturer and then a series of fields for maintenance recording eg Date6mthEngineServiceDone,Date12mthEngineServiceDo ne,Date6mthAntifoulingDone,Date12mthAntifoulingdon e, Antifoulingprodctcolour, Antifoulingqtyused, Antifoulingproductused etc.
    In addition for each of the "datedone"items, there is a date due. This date due is calculated in the form used for data entry and is stored in the equivalent datedue field for each category in the table. eg once you enter the date the 6 monthly engine service is done, it adds 6 months and stores this in the Date6mthEngineServiceDUE field. All this is working wonderfully (based on a lot of help from this forum). My issue is then getting a query to show what maintenance tasks are due in the next ,say,month.
    I have a query which sets as the criteria Date6mthEngineServiceDue in next month OR Date12mthEngineServiceDUE or Date6MthAntifoulingdue etc .
    This query works exactly as specified eg it finds all the Customers who have a 6 mth antifoul due next month. However because I am showing all the maintanence items in the query, it also shows the date when the Customers 12mthEngineserviceisdue and the 6mthantifoul and the 12mthAntifoul etc etc. - quite logical based on the OR ie if any crieria for any of the maintenance items is met, it shows the date of all the maintenance items
    I want it to show only those items due in the next month for that customer
    Hope this helps
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join Queries (2000)

    Sorry - SQL as requested - in this example I am looking for any maintence duw in next 3 months
    SELECT Boats.OwnerName, Boats.[Boat Name], Boats.BoatManufacturer, Boats.MotorManufacturer, Boats.Engines6mthServiceDue, Boats.Engine12mthServiceDue, Boats.AntiFouling6mthsDue, Boats.AntiFouling12mthsDue, Boats.Leg6mthServiceDue, Boats.Leg12mthServiceDue, *
    FROM Boats
    WHERE (((Boats.Engines6mthServiceDue) Between Date() And DateAdd("m",3,Date()))) OR (((Boats.Engine12mthServiceDue) Between Date() And DateAdd("m",3,Date()))) OR (((Boats.AntiFouling6mthsDue) Between Date() And DateAdd("m",3,Date()))) OR (((Boats.AntiFouling12mthsDue) Between Date() And DateAdd("m",3,Date()))) OR (((Boats.Leg6mthServiceDue) Between Date() And DateAdd("m",3,Date()))) OR (((Boats.Leg12mthServiceDue) Between Date() And DateAdd("m",3,Date())));
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Join Queries (2000)

    Your problem is mainly the result of the design of the Boats table. I would have used several tables:

    tblBoats to store "static" info about boats, with a primary key BoatID (AutoNumber), plus fields such as BoatName, but no service information.
    tblServiceTypes to store "static" info about the types of service, with a primary key ServiceTypeID (AutoNumber), and for example a text field ServiceType.
    tblBoatServicing as a link table, containing BoatID and ServiceTypeID (number, long integer) as a composite primary key, plus fields DateDue, DateDone, plus further info specific to this service instance. tblBoatServicing would be linked to the other two tables on the field of the same name (with referential integrity enforced, and cascading deletes)


    But you probably don't want to change the structure now. Create a query based on Boats. Add the "general" fields you want to display: probably OwnerName, [Boat Name], BoatManufacturer and MotorManufacturer. For each of the "due" fields, add a calculated field like this, taking Engines6mthServiceDue as example:

    Engines6mthServiceDue: IIf([Boats].[Engines6mthServiceDue] Between Date() And DateAdd("m",3,Date()),[Boats].[Engines6mthServiceDue],Null)

    It is essential to add the table name in the expression, otherwise you would create a circular reference to Engines6mthServiceDue. If you switch to datasheet view now, you will see that most of the due date fields are blank. Only those between today and three months from today are displayed. Back in design view, add Is Not Null as criteria for each of the "due" fields, on a different line for each, so that you create "Or" conditions. Your query will now select only those records who have a "due" date in the next three months, and only displays dates falling within that period.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join Queries (2000)

    Hans
    Thank you very much for the time and trouble - I'll give that a shot to get it going and work on the redesign as suggested next week
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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