problem with query logic

[Stuartp]

I'm having a problem getting this query to work. I am trying to report on holidays people have taken and will take. Employees are allowed a quota of days holiday. The database records days they have taken in one table, together with how many days they have had. But people are also allowed to pre-book holidays, the start dates and end dates for which are stored in another table.

I have managed to make it calculate how many days holiday they have left using a query, but I have problems where they book hols for the following year. Using queries it either returns holidays for this year and next, taking it off this year's total, which is wrong, or it does not return anything unless they have taken some holiday AND booked some for the same year.

I want the user to be able to enter the year to query and for it to return entitlement - holiday taken this year - holiday pre booked this year, and ignore entries in each table concerning any other year.

Hope I've explained this clearly enough.

Thanks in advance.

Stuart

 

[BoxHead]

Stuart, it would seem that a textbox, combobox or input box would serve your purpose by setting that control to be the criteria for both 'taken holidays' and 'booked holidays'.

Or, is it more complicated than that?

John

 

[Stuartp]

I think it is more complicated that this. I did make the query ask for thr year using [year] for both taken holidays and booked holidays, and since it only asked me to enter a value once I assume that it used the same value for both. But I still had the problem.

It either returns holidays taken and booked from both years, or it returns nothing unless a holiday has been taken and booked in the same year depending on whether I enter an OR or an AND query.

I need it to return holidays taken and/or booked in the year entered but to ignore entries for the following year. Can I get it to return null values in some way e.g. return values for 2001 but return null for 2002 because they dont match the criteria or is there some other way I should attempt this?

Thanks.

Stuart

 

[BoxHead]

Stuart, I think the assumption that the query will use the value for both fields is the problem. The query will only use the criteria you specify.

Let's test it.

Since you say you're asked for a year, I assume you have your criteria set to [Enter the Year] for 'taken holidays'. Whether you did it in SQL or on the QBE grid, do it again for 'booked holidays'. If you're prompted to enter a year, you should now be prompted twice.

If this works, then the problem is almost solved. There doesn't seem to be any reason to enter the same year twice.

To be honest, I'm not sure how to reference the entered criteria value when the query prompts for it. I'd end up using a text or combo box to capture the info and in QBE grid, set that textbox as the criteria for both fields.

There shouldn't be any AND/OR that I can see from your posts.

HTH


John

 

[Stuartp]

I have tried this and still can't get it to work. Perhaps it would help if I post the query code then you can follow what I have been trying to do and hopefully it might make more sense:

SELECT Register.Name, [Holiday Entitlement].Entitlement, Register.Days AS [Holiday Taken], Register.Date, [HolEnd]-[HolStart] AS [Holiday Booked], [Holiday Bookings].HolStart AS [Start Date]
FROM ([Holiday Entitlement] INNER JOIN [Holiday Bookings] ON [Holiday Entitlement].Name = [Holiday Bookings].Name) INNER JOIN Register ON [Holiday Entitlement].Name = Register.Name
WHERE (((Register.Reason)="Holiday"))
GROUP BY Register.Name, [Holiday Entitlement].Entitlement, Register.Days, Register.Date, [HolEnd]-[HolStart], [Holiday Bookings].HolStart
HAVING (((DatePart("yyyy",[Date]))=[year]) AND ((DatePart("yyyy",[HolStart]))=[year]));

This is what QBE produces by the way, I have not written this!

Thanks for your help so far.

Stuart

 

[BoxHead]

Stuart,

I'm sorry. I'm really having a difficult time getting my mind around this one.

When all else fails, go back to simplicity...

First build a simple query to generate a list of all your people and the total number of days they are entitled to for the year.

Name          DaysEntitled

Second, build a query (qryTaken) listing your people and the holidays they've taken so far.

Name          HolStartComp          HolEndComp

Set your criteria for HolStartComp as [year]

Third, build a query (qryBooked) listing your people and the holidays they've booked.

Name          HolStartBook          HolEndBook

Again, set your criteria for HolStartBook as [year].

Finally, build a fourth query (qryTotals) based upon these three queries. On the QBE, add the three queries, bring the first down in its entirety to populate the first two columns with Name & DaysEntitled. Drag HolStartComp & HolEndComp from the second query into the third and fourth columns. In the field space of the fifth column, type

Days Taken : HolEndComp - HolStartComp

Drag the HolStartBook and HolEndBook into the sixth and seventh columns. In the field space for the eighth column,

Days Booked : HolEndBook - HolStartBook

As you go through this, stop to look at the table view to check the results and take a look at the SQL. In the ninth column, you should be able to calculate days left, etc...

I really wish I could be more helpful.


John

 

[Stuartp]

Thanks John,

This works well to a point, but it still only works if there is an entry in both tables. In other words only peole who have both taken holidays and booked holidays are returned. People who have only done one or the other are missed out.

Is there any way to make a query return a null value where nothing exists, or is this too complicated. Would it make more sense just to put all holiday entries into one table?

Stuart

 

[BoxHead]

Stuart, when you view your first query, do you see the full list of all people entitled to vacations? And, is that the field you selected for your first column in the fourth query?


John

 

[Stuartp]

Yes, the first query shows all members of staff and their holiday entitlements, and these two fields (staff name and entitlement) are the first two columns in the fourth query.

Stuart