Calculation in query 1

[hdgirl]

Hi
i have a problem with a query i am trying to create. It is for a leave form the tables i have are Annual entitlement Previuosly taken, Present Balance,This leave & Balance. my problem is that i want the previously taken field to be 0 on the first record, then the next record would show the leave taken from the previous record and so on,as such

Entitlement Previously Taken Present Bal This Leave Bal
20 0 20 3 17
20 3 17 2 15
20 5 15 2 13

i can work out the present balance and the balance but cant get the previously taken. any help would be appreciated

CJB

 

[jksmi]

Hi, [Entitlement]-[Present Bal] will give you previously taken as you have described it in the table above (if Present Balance is calculated write out the whole calculation in place of [Present Bal])- but if you want to show the actual [This Leave] from the previous record then you will need to use some other queries or subqueries and some sort of sequence to follow (i.e. the first date that this leave applies to)... HTH, Jamie

 

[hdgirl]

Jamie

thanks for replying, should i put this in the criteria of the previously taken field?

CJB

 

[jksmi]

Hi CJB,

I'm a bit confused, which fields are you calculating and which ones come from the table? expressions should go in the field row like this>
Previously Taken: [Entitlement]-[Present Bal]
HTH, Jamie

 

[Golom]

This is not so simple as it may appear. The issue is that concepts like "previous" and "next" exist only with reference to some specified record ordering. There's nothing in this record that explicitly tells us how to order the records so that we can determine which one is "previous" to any particular record.

Do you have, for example, a date field on the records so that we can order them?

If the computation is to be based on fields entirely within a single record then the above suggestions should work. If, on the other hand, you want to do computations where fields are taken from different records then you need to resolve the "previous" issue.

 

[hdgirl]

Guys Thanks for answering,

let me try to explain a bit better (also i've changed it a bit)

the first part is the balance which is Expr1 that consists of Expr1: [tblLeave]![AnnualLeaveEntitlement]-[tblLeave]![ThisLeave]-[PreviouslyTaken]+[tblLeave]![LieuDays]---this is brill and gives me my balance, now based on what Jamie said earlier i need to deduct the balance from AnnualLeaveEntitlement but as the balance is Expr1 i am using this [tblLeave]![AnnualLeaveEntitlement]-Expr1 in the field PreviouslyTaken i then get a warning to say "The expression you entered contains invalid syntax".

Sorry i know i am being really thick but this has got me stumped

CJB

 

[lespaul]

Why don't you post your SQL?

Leslie

 

[jksmi]

Hi,

Sorry, I'm confused again... expr1 gives you new balance and includes previously taken in its calculation and it works [tblLeave]![AnnualLeaveEntitlement]-[tblLeave]![ThisLeave]-[highlight][PreviouslyTaken][/highlight]+[tblLeave]![LieuDays] >> where does the PreviouslyTaken come from here? can you post the tablenames and fields that are available as I think I'm struggling to see the bigger picture...


HTH, Jamie

 

[hdgirl]

Hi Guys thanks for answering and Jamie i've got myself confused but i'll try to explain.

The fields are Annual Leave Entitlement, Previously Taken, Lieu Days, This Leave & Balance. i need 2 formulas, 1 is Annual Leave Entitlement - This Leave - Previously Taken + Liey days = Balance (this works as an expression and becomes Expr1). i then want Annual Leave Entitlement - Balance so that this will give me the total previously taken but i can't get an expression that says Annual Leave Entitlement - Expr1 ????????????
this is the SQl

SELECT tblHR.StaffNo, tblHR.Title, tblHR.LastName, tblLeave.AnnualLeaveEntitlement, tblLeave.AnnualLeaveFrom, tblLeave.AnnualLeaveTo, tblLeave.PreviouslyTaken, tblLeave!AnnualLeaveEntitlement-tblLeave!PreviouslyTaken AS Expr2, tblLeave.ThisLeave, tblLeave!AnnualLeaveEntitlement-tblLeave!ThisLeave-[PreviouslyTaken]+tblLeave!LieuDays AS Expr1, tblLeave.LieuDays, tblLeave.LDaysFrom, tblLeave.LDaysTo
FROM tblHR INNER JOIN tblLeave ON tblHR.StaffNo=tblLeave.StaffNumber;

Any help would be appreciated

THX

CJB

 

[hdgirl]

Hi All

I am still trying to get this to work and what i am now getting is "Circular reference caused by alias in query definitions SELECT list (Error 3103). i can understand that i am creating the circle but am still not able to rectify it????????????????

CJB

 

[jksmi]

Hi,
I think I can help now...
using the columns,StaffNo, AnnualLeaveForm, AnnualLeaveTo, AnnualLeaveEntitlement, ThisLeave & LieuDays from tblLeave you should be able to get everything that you want
the following SQL creates a running total of previous days taken, a running total of total days taken (inc this leave), a running total of lieu days and a net balance (entitlement - total days taken + lieu days)

SELECT
    tblHR.StaffNo
    , tblHR.Title
    , tblHR.LastName
    , tblLeave.AnnualLeaveEntitlement
    , tblLeave.LieuDays
    , tblLeave.AnnualLeaveFrom
    , tblLeave.AnnualLeaveTo
    , tblLeave.ThisLeave
    , nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] < Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy')
        AND StaffNo = '" & [tblHR]![StaffNo] & "'"),0) AS PreviousDaysTaken
    , nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy')
        AND StaffNo = '" & [tblHR]![StaffNo] & "'"),0) AS TotalDaysTaken
    , nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy')
        AND StaffNo = '" & [tblHR]![StaffNo] & "'"),0) AS TotalLieuDays
    , [AnnualLeaveEntitlement]-nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy')
        AND StaffNo = '" & [tblHR]![StaffNo] & "'"),0)
        +nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy')
        AND StaffNo = '" & [tblHR]![StaffNo] & "'"),0) AS Balance
FROM    tblLeave INNER JOIN tblHR
    ON tblLeave.StaffNo = tblHR.StaffNo
ORDER BY tblHR.StaffNo, tblLeave.AnnualLeaveFrom;

This SQL uses DSum to calculate running totals so whenyou get to '000s of records it will impact on the performace of the query - however you could easily turn this into an update query if this turns out to be a problem

HTH, Jamie

 

[hdgirl]

Jamie,

once again thanks for responding, i pasted the above code into my sql and all it has given me is a load of #error's, apologise for being a bit thick

CJB

 

[jksmi]

Hi,

is your staffNo a string or a number? The dummy table I set up used a string so if yours uses a number then wherever you see this statement
AND StaffNo = [highlight]'[/highlight]" & [tblHR]![StaffNo][highlight] & "'"[/highlight]),0)
replace it with
AND StaffNo = " & [tblHR]![StaffNo]),0), I've highlighted the bits that need to be deleted... I did think of this before but I forgot, honest...

HTH, Jamie

 

[hdgirl]

Jamie

i am using a string so it should be the same as yours, though none of your code is highlighted

CJB

 

[hdgirl]

Jamie

i have messed about a bit and now get figures in every field except balance still shows #error, plus i am unable to add any data, when i close the query i get the message "syntax error (missing operator) in query expression '([AnnualLeaveFrom] <= Format (#27/03/2002#, dd/mm/yyyy') AND StaffNo = '1".

CJB

 

[jksmi]

Hi,

looks like its just a typo - in changing the date format you missed an ', that is you've written dd/mm/yyyy' rather than [highlight]'[/highlight]dd/mm/yyyy' - I'm surprised you can't see the highlighting, I can?

Also, the reason that I've added the format to month/day/year is that SQL uses this American date format -since you are using UK format (day/month/year)to change it in the formulas will bring you back incorrect results. (can you see the bold?)

I've tested this on some dummy tables and had no probs adding rows and updating fields. Perhaps if you pasted your new SQL and I can see waht you had to change...


HTH, Jamie

 

[hdgirl]

Jamie,

At the moment i have no errors but it is still not working, balance is showing the same figure ass entitlement and i can't edit any of the fields only entitlement. THank you very much for all the help you are giving me on this

This is my SQL

SELECT tblHR.StaffNo, tblLeave.AnnualLeaveEntitlement, tblLeave.LieuDays, tblLeave.ThisLeave, nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] < Format(#" & [AnnualLeaveFrom] & "#,'dd/mm/yyyy') AND StaffNo ='" & tblHR!StaffNo & "'"),0) AS PreviousDaysTaken, nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'dd/mm/yyyy') AND StaffNo ='" & tblHR!StaffNo & "'"),0) AS TotalDaysTaken, nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'dd/mm/yyyy') AND StaffNo ='" & tblHR!StaffNo & "'"),0) AS TotalLieuDays, [AnnualLeaveEntitlement]-nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'dd/mm/yyyy') AND StaffNo ='" & tblHR!StaffNo & "'"),0)+nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'dd/mm/yyyy') AND StaffNo = '" & tblHR!StaffNo & "'"),0) AS Balance
FROM tblHR INNER JOIN tblLeave ON tblHR.StaffNo = tblLeave.StaffNumber
ORDER BY tblHR.StaffNo, tblLeave.AnnualLeaveFrom;

CJB

 

[jksmi]

Hi CJB,
I think we're getting there now...
The first thing is, I didn't realise that tblHR.StaffNo=tblLeave.StaffNumber, I had StaffNo on both tables so I've fixed that - though you really should try and keep primary/ foreign key field names the same.

The second thing (as mentioned previously) you must have the date formated to the American day/month/year.

Finally, with regards updating and adding records to the query. In order to add records to the query the key fields need to be present. You can hide the ones you dont want people to see by using a form. On a new record the calculated fields will show as #error until the 2 dates fields are as they are required by the calculation, once they've been entered it will calculate and the #error will go. I notice that you had taken references to Title & LastName out of the query so I have removed the tblHR from the query alltogether

SELECT tblLeave.StaffNumber, tblLeave.AnnualLeaveEntitlement, tblLeave.LieuDays, tblLeave.AnnualLeaveFrom, tblLeave.AnnualLeaveTo, tblLeave.ThisLeave, nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] < Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy') AND StaffNumber = '" & [tblLeave]![StaffNumber] & "'"),0) AS PreviousDaysTaken, nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy') AND StaffNumber = '" & [tblLeave]![StaffNumber] & "'"),0) AS TotalDaysTaken, nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy') AND StaffNumber = '" & [tblLeave]![StaffNumber] & "'"),0) AS TotalLieuDays, [AnnualLeaveEntitlement]-nz(DSum("ThisLeave","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy') AND StaffNumber = '" & [tblLeave]![StaffNumber] & "'"),0)+nz(DSum("LieuDays","tblLeave","[AnnualLeaveFrom] <= Format(#" & [AnnualLeaveFrom] & "#,'mm/dd/yyyy') AND StaffNumber = '" & [tblLeave]![StaffNumber] & "'"),0) AS Balance
FROM tblLeave
ORDER BY tblLeave.AnnualLeaveFrom;

HTH, Jamie

 

[hdgirl]

Hi Jamie,

I' getting there, first thx for pointing out my error in the naming of the fields i've been working on this project for a while now and i think you get blind to things i hadnt even noticed the mistake in the staffNo etc but thx. i hadnt taken out the Title & LastName that was strange. Now i am getting the balance part to work but not the PreviousDaysTaken and i have copied & pasted your code exactly so hopefully we'll be there soon

Thx 4 all your help again

Cheryl

CJB

 

[hdgirl]

Hi Jamie,

Don't know what happened but have come into work this morning & tried again by creating a new query and exactly copied and pasted your last code and it's actually working and working exactly as i wanted. So thank you very much and thanks for staying with me through this, now i can move on and i hope you can help me again sometime.

cheryl

CJB