Query result error

[Noob]

ftp://ukcassassin:[e-mail]...ary%20Form.bmp

Hi all
I will appologise in advance for my lack of knowledge of access and its working as i am quite new to the program and have been self taught, so here goes.

As you can see by the image link i am creating an appointment diary for a garage,its going ok untill i created the "Hours remaing" text box The main subform adds up the hours booked in and displays them in the "Hours Booked" text box, this works fine. The Subform "Techs Absent" is based on a query that checks a "holiday table" that has a "Tech ID","Name","Holiday Start Date" , "Holiday End Date"and "Bookable Hours" field in it. Basically it checks the date selected on the calender control is >= the hol start date and <= the holiday end date and if so it displays the tech names and their bookable hours in this subform (not sure if thats clear) this runs realtime with each date selected in the calender control and shows me who is off on that particular day and how many hours they are worth. (hope your still with me...lol) I.E: if a tech is absent his bookable hours must be deducted from the total hours for that day which is normally 26

The total hours for the 4 techs is 26 hours, and my problem is that i based the record source for the "Hours Remaing" text box on a value of "26 hours" minus the "Total Hours Booked" value, Minus the sum of the "Bookable Hours" field in the Techs absent subform. This works fine when the "Techs absent subform" shows a value but when there are no techs absent on the date selected , the query shows no results in the subform and the "Hours Remaining" text box shows ERROR. Is there a way to still do the calculation even if the query result shows no value?

I know it was long winded and probably very confusing but your help would be so much appreciated
Many thanks in advance
Chris

[Phille]

Hi

I couldn't me more confused, I understood about 10% of your text (my head gets twisted halfway) but I think it should work with an IF sentence in the criteria field of your query. In that way you could tell your query to do the calculation if there is a value in the abcent field otherwise it just skips the calculation.

I'm in a hurry for work but hopefully this helps a bit

[MMcCarthy]

The total hours for the 4 techs is 26 hours, and my problem is that i based the record source for the "Hours Remaing" text box on a value of "26 hours" minus the "Total Hours Booked" value, Minus the sum of the "Bookable Hours" field in the Techs absent subform. This works fine when the "Techs absent subform" shows a value but when there are no techs absent on the date selected , the query shows no results in the subform and the "Hours Remaining" text box shows ERROR. Is there a way to still do the calculation even if the query result shows no value?

I know it was long winded and probably very confusing but your help would be so much appreciated
Many thanks in advance
Chris

You will have to change 'Minus the sum of the "Bookable Hours" field in the Techs absent subform' to

... - IIf(Not IsNull([Bookable Hours],[Bookable Hours],0)

Therefore if there is nothing in this field the calculation can still take place. The other option is to set the default value of the field to 0 in the table.

Mary

[NeoPa]

...Or the shorthand for that is :

... - Nz([Bookable Hours],0)

[Noob]

Hi
Firstly thanks for your replies, I have tried all your suggestions but I am still struggling at the mo,it still shows ERROR in the text box on the form,I dont think I explained it very well either,so i'll try again. The TECHS ABSENT SUBFORM (top left on the picture ) is basing its record source on this query:

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (((JobTable.Date)>=[HolidayTable]![Holiday Start Date] 
  And (JobTable.Date)<=[HolidayTable]![Holiday Finish date]));

This displays a result if a tech is absent on any day that work is booked in on the job table.(subform is in datasheet view) on the footer of this subform is an unbound text box named (TotalBookableHours) with its record source as" =Sum([Bookable Hours])" this works fine when a value is shown on the subform but when no tech is absent on the date selected the subform shows no values in the datasheet view and the (TotalBookableHours) text box is blank.

On the Main Diary form is an unbound text box with the following as its record source =26-[TotalHoursBooked]-[TechAbsences subform].[Form]![TotalBookableHours] which again works ok untill there is no value in the subformthen the dreaded ERROR shows in this box.I have tried with your suggestions but I may be putting them in the wrong place or just being plain stupid,hope this explains better what i am trying to achieve.

Many thanks Chris

[NeoPa]

Just to quickly post a slightly better version of your SQL.
This uses the Between ... And ... format to check the dates.

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (JobTable.Date Between HolidayTable.[Holiday Start Date] 
                         And HolidayTable.[Holiday Finish date]);

[NeoPa]

Hi
Firstly thanks for your replies, I have tried all your suggestions but I am still struggling at the mo,it still shows ERROR in the text box on the form,I dont think I explained it very well either,so i'll try again. The TECHS ABSENT SUBFORM (top left on the picture ) is basing its record source on this query:

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (((JobTable.Date)>=[HolidayTable]![Holiday Start Date] 
  And (JobTable.Date)<=[HolidayTable]![Holiday Finish date]));

This displays a result if a tech is absent on any day that work is booked in on the job table.(subform is in datasheet view) on the footer of this subform is an unbound text box named (TotalBookableHours) with its record source as" =Sum([Bookable Hours])" this works fine when a value is shown on the subform but when no tech is absent on the date selected the subform shows no values in the datasheet view and the (TotalBookableHours) text box is blank.

On the Main Diary form is an unbound text box with the following as its record source =26-[TotalHoursBooked]-[TechAbsences subform].[Form]![TotalBookableHours] which again works ok untill there is no value in the subformthen the dreaded ERROR shows in this box.I have tried with your suggestions but I may be putting them in the wrong place or just being plain stupid,hope this explains better what i am trying to achieve.

Many thanks Chris

Try setting the RecordSource of [TotalBookableHours] to "=Nz(Sum([Bookable Hours]),0)".

[Noob]

Try setting the RecordSource of [TotalBookableHours] to "=Nz(Sum([Bookable Hours]),0)".

Tried this but still shows ERROR. Any idea's of another way to achieve the same result ?

Cheers Chris

[MMcCarthy]

Tried this but still shows ERROR. Any idea's of another way to achieve the same result ?

Cheers Chris

Check the name of the [Bookable Hours] textbox in the properties under the other tab. The name may not be what you think.

Secondly check the datatype and make sure it is actually a number.

Mary

[Noob]

I have checked both of these and they are all ok,Is there a completely different approach to achieve the same result possibly? I'm a little baffled now even if my query showed a row with zero in it when there were no techs absent ,that would allow the calculation to complete and hey presto my head would not be so battered..lol,

Thanks for your help so far

Chris

[MMcCarthy]

I have checked both of these and they are all ok,Is there a completely different approach to achieve the same result possibly? I'm a little baffled now even if my query showed a row with zero in it when there were no techs absent ,that would allow the calculation to complete and hey presto my head would not be so battered..lol,

Thanks for your help so far

Chris

Sorry Chris but I've just realised that your query has an INNER JOIN which will only return techs that have a record in the holiday table. If you change this to a LEFT JOIN so that all techs are returned does this help.

Mary

[Noob]

Sorry Mary it says "Join not supported" when i try to save the query, I wish I could think of a way round it but im well baffled.......lol.

Thanks anyway

Chris

[MMcCarthy]

You're not the only one Chris ...

OK,

What is the relationship between JobTable and TechnicianTable?

[NeoPa]

Sorry Mary it says "Join not supported" when i try to save the query, I wish I could think of a way round it but im well baffled.......lol.

Thanks anyway

Chris

Does this code ( I assume you haven't changed it greatly from the original question) work for you, or does it give a "Join not supported" error?

SELECT DISTINCT T.[Technician Name],
                T.[Bookable Hours],
                J.Date
FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
  ON T.[Technician ID] = H.[Technician ID], JobTable AS J
WHERE (J.Date Between H.[Holiday Start Date] And H.[Holiday Finish date]);

[Noob]

Hi

It also gives join not supported Error.

There is no relationship between Job table and Technician table

Perhaps I may have to scrap the whole hours subtraction thing,It seems that because the text box value is based on a query result,the whole calculation falls down when the query returns no data which will be more often than not,except when a tech is absent on the day in question.

Cheers Chris

[NeoPa]

Try this then :

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#),
             Nz(H.[Holiday Finish date],#1/1/9999#)
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Between subQ.[Holiday Start Date]
                    And subQ.[Holiday Finish date]);

[Noob]

Hi
I tried that code and it now opens a parameter box for the holiday start date and another for holiday finish date when i change the day using the calendar control on the main form, and also again when i open the database .


Sorry .....Chris

[NeoPa]

Yes, that makes sense.
I will post a fix as soon as I get some time.
Don't worry though - it's a simple fix.

[NeoPa]

This should be fixed now. I name the results of the Nz() functions so they can be used later (in the outer query WHERE clause).

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#) AS HolStart,
             Nz(H.[Holiday Finish date],#1/1/9999#) AS HolEnd
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Between subQ.HolStart And subQ.HolEnd);

[Noob]

Hi

Thanks for your response but im not sure what has happened now as it is displaying quite a lot in the Tech's Absent subform and Im a tad baffled....lol. As your code went ,,,way...way...way over my head and im a total newbie at this, I have attached the file to this post, just in case you can spot my mistake straight away, once you have stopped rolling around on the floor laughing at my total Amateur attempt at a database.

Really appreciate you all taking the time to help me out with this, as i said originally I am totally new to this and this is my first attempt at Acces and as you probably can tell "I aint no programmer or VB pro"....Lol.

Thanks Again
Chris

[Noob]

sorry took too long

[NeoPa]

You can post you database if you like Chris (you will need to get it to under 40KB I think). You can try Compact & Repair as well as Zipping it up. Bear in mind though, I will try to get to it, but as it takes a bit longer it may slip down my list until I get the time spare.

[NeoPa]

I hope this enables you (or maybe only those that come along and read this thread later) to understand the posted SQL better.

Lines 4 through 9 define a subquery (See Subqueries in SQL) named as subQ.
Line 10 shows the extra table (JobTable) added as an outer join.
Line 11 is the WHERE clause that selects only the records that are required from the full list provided by the outer joined tables.
Lines 1 and 2 refer to the fields returned by the subquery (subQ).
Lines 6 and 7 take the Start and End dates and replace them with extreme dates if they contain Nulls (Nz). These resulting fields are then used in line 11.

[Noob]

Hi
Ive been thinking about this for a day or two and have made an attempt at tackling this from another angle but without much sucess. How about a new query that shows the name and hours of each technician that is present for each date in the job table and excludes the tech that is on holiday for each day of his holiday, this way the results of the query would never be empty as there would never be a day that all 4 techs are off at the same time, thus my calculation can take place every time.
Tables are as follows
Job Table
"Date"
TechnicianTable
"Technician ID"
"Technician Name"
"Bookable Hours"
HolidayTable
"Technician ID"
"Holiday Start Date"
"Holiday Finish Date"

The technician table and the holiday table are linked with a one to many relationship on the Thechnician ID field

Thanks for your help
Chris

[NeoPa]

Chris,
You don't explain why you're trying to tackle this from another angle?
I answered the question, if this is a new one then it should probably be in a separate thread :confused:

[Noob]

Hi
Sorry for the confusion the code you gave me on the last post is almost perfect

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#) AS HolStart,
             Nz(H.[Holiday Finish date],#1/1/9999#) AS HolEnd
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Between subQ.HolStart And subQ.HolEnd);

What it is doing is showing me all the techs that are available to work for each date in the job table, which is brilliant. but when i enter a Technician Name, Holiday start date and holiday finish date in the Holiday Table. That technician is ommited from the query and is only shown on the Dates he is supposed to be off (for example I selcted Dave to be off from 23/01/07 to 24/01/07 and the subform showed all the other techs and their hours on every day selected but ommited Dave from the results untill 23/01/07 and 24/01/07 where upon he was include and dates after that he was omitted again.I would be over the moon if it could work the other way round and show all the Techs who are available to work for each date in the Job table and just to omit the Technician from the results on the dates he is off, in the holiday table.

Im really grateful for your efforts so far

Many Thanks Chris

[NeoPa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

[Noob]

LOL
Thanks I'll look forward to your reply

Just one additional thing would it be possible to base the query on a Date box on the main form which is updated from the Calender control Active X
the box is " Forms![Diary Form]![date] " so that each time the date in the box is altered the query runs and bases its outcome on the Date in the Box ??
Don't worry if its a nightmare, as what youve done so far is brilliant.

Thanks again

Chris

[NeoPa]

To fix your earlier problem (hopefully), try :

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#) AS HolStart,
             Nz(H.[Holiday Finish date],#1/1/9999#) AS HolEnd
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Not Between subQ.HolStart And subQ.HolEnd);

The difference is the introduction of the "Not " on the last line ( WHERE clause).

As for new problems, I'm going to pretend I didn't even see it. If I worked that way without closing the open door first (metaphorically speaking) I'd never get anything done properly (hence my reluctance to dive off at a tangent earlier).
If we get this problem sorted, that will be the time to introduce other matters.

[Noob]

Hi

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#) AS HolStart,
             Nz(H.[Holiday Finish date],#1/1/9999#) AS HolEnd
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Not Between subQ.HolStart And subQ.HolEnd);

This code now shows no techs even when i show them in the holiday table.
This is the code you gave me last time

SELECT DISTINCT subQ.[Technician Name],
                subQ.[Bookable Hours],
                J.[Date]
FROM (SELECT T.[Technician Name],
             T.[Bookable Hours],
             Nz(H.[Holiday Start Date],#1/1/1900#) AS HolStart,
             Nz(H.[Holiday Finish date],#1/1/9999#) AS HolEnd
      FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
        ON T.[Technician ID] = H.[Technician ID]) AS subQ,
     JobTable AS J
WHERE (J.[Date] Between subQ.HolStart And subQ.HolEnd); 

What it is doing is showing me all the techs that are available to work for each date in the job table, which is brilliant. but when i enter a Technician Name, Holiday start date and holiday finish date in the Holiday Table. That technician is ommited from the query and is only shown on the Dates he is supposed to be off .It would be perfect if it was vice versa (for example I selcted Dave to be off from 23/01/07 to 24/01/07 and the subform showed all the other techs and their hours on every day selected but ommited Dave from the results untill 23/01/07 and 24/01/07 where upon he was include and dates after that he was omitted again.I would be over the moon if it could work the other way round and show all the Techs who are available to work for each date in the Job table and just to omit the Technician from the results on the dates he is off, in the holiday table.

Im really grateful for your efforts so far

Many Thanks Chris

[NeoPa]

I need to know from you :

1. When you say 'What it is doing...' are you referring to the first block of code (in your post) or the second? Bearing in mind the second was provided originally before the first.
2. Remind me, how does the Technician get selected and how is this applied to the SQL of the form? Is there some form of Filter applied?

BTW I think your last post has probably helped to clarify things :)