Join Queries (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Tables are:Customers,Boats,EngineManufacturer;BoatManufacturer
Boats is the main table and has fields for CustomerName, BoatName, EngineManufacturer, BoatManufacturer and then a series of fields for maintenance recording eg Date6mthEngineServiceDone,Date12mthEngineServiceDone,Date6mthAntifoulingDone,Date12mthAntifoulingdone, 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

Reply | Quote

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())));

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Tables are:Customers,Boats,EngineManufacturer;BoatManufacturer
Boats is the main table and has fields for CustomerName, BoatName, EngineManufacturer, BoatManufacturer and then a series of fields for maintenance recording eg Date6mthEngineServiceDone,Date12mthEngineServiceDone,Date6mthAntifoulingDone,Date12mthAntifoulingdone, 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

Reply | Quote

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

Reply | Quote