Why will columns not add? They join like text! (1 Viewer)

[Paine]

Hey all,

I'm having quite a bizarre time trying to add two columns together in a query - can anyone offer any advice?

For the sake of simplicity, my query looks like:

- [Course Enrolments].[Student Number] (Group by)
- [Course Enrolments].[Enrolment ID] (Count)
- Passes: NZ([Number of passes per learner].[Passes],0) (Group by)
- Fails: NZ([Number of fails per learner].[Fails],0) (Group by)
- Results Pending: NZ([Pending results per learner].[pending],0) (Group by)

Then I had added a column which reads:

- Total: [Passes]+[Fails]

Now, here's where it all gets odd!

Let's say a student has 3 passes and 1 fail. You would expect the result in Total to be 4, right? Well - I'm getting 31! If a student gets 2 passes and 2 fails, the result is 22 rather than 4. The formula seems to be compounding the results rather than adding them.

Does anyone have any suggestions for getting around this?

The reason I need the total column is that I want to add in another column that reads... Pass rate: ([Passes]\[Total])*100 (perhaps I'm being too ambitious here, but I'm sure Access must have the power to do it!)

Thanks




Steve

 

[Galaxiom]

It is confused about the datatype. Is the field type a Number?
Sometimes it gets confused anyway and does an implicit conversion to text.

If so try this:
Total: CInt([Passes)] + CInt([Fails])

Pass rate: ([Passes]\[Total])*100
This probably won't work since it is refering to another calculated field. Generally one has to repeat the calculation.

BTW the backslash is the integer divide so you would get a surprise there too. Use the forward slash.

 

[JANR]

Nothing bizzare about it since your field "Number of passes per learner" in your table is defined as text and not number,

if you are going to do arithmetic on numbers cast it to a numberdatatype.

You can use Cint() or Clng() functions to convert "stringnumbers" to real numbers.

Passes: CInt(NZ([Number of passes per learner].[Passes],0)) (Group by)
Fails: CInt(NZ([Number of fails per learner].[Fails],0)) (Group by)


JR

 

[vbaInet]

Even Val() will do it too so no need for Nz()

Total: Val([Passes)] + Val([Fails])

 

[Paine]

OK, I've managed to get the total column. So, if I have to do the achievement rate in full then should it be....

Achievement rate: (CInt(NZ([Number of passes per learner].[Passes],0))/(CInt(NZ([Number of passes per learner].[Passes],0))+CInt(NZ([Number of fails per learner].[Fails],0))+CInt(NZ([Number of units lost to exclusion per learner].[Excluded before completion],0)))*100)

This brings up an error message that reads "Overflow" ... hmm

 

[Paine]

PS: apologies vbainet, only just saw your response - perhaps I should use Val() to tidy it up once I've worked out where I'm going wrong here!

 

[vbaInet]

Use Val() and see if that gets rid of the Overflow.

If it doesn't go back to your code in post #5 and change CInt to CLng.

If that doesn't work then you having a division by 0 problem.

 

[Paine]

OK, we're limping across the finish line now!

I didn't have any luck getting Val() to work and I think I was being too complex with what I was trying to achieve in the one query, so, I have created a second query which reads off the first query and does the very simple formula, ie it looks like:

[enrolments].[student number]
[enrolments].[First Name]
[enrolments].[Surname]
[enrolments].[Passes]
[enrolments].[Ach_total] (this is the CInt passes + fails on previous query)
Achievement rate: (CInt([Passes])/CInt([Ach_total]))*100

Now, this (broadly speaking!) gives me what I'm after, apart from the fact that the percentages go on forever. Is there a way of getting it rounded up to 1 decimal place? I had a go doing the / around the opposite way, but this then rounds all the results to either 100 or 0. Or, do I have to bite the bullet and use form property controls to control this unwieldy data? This will potentially give us problems because we use Excel exports from queries for reporting purposes...

Also, I am going to have a play to try and get rid of dividing by 0 errors using IIF - does that sound sensible?

Thanks all for your help so far!

 

[spikepl]

Also, I am going to have a play to try and get rid of dividing by 0 errors using IIF - does that sound sensible?

No. You create this error yourself by the Nz function, using 0 as the Null option in the divisors.. Use 1 in stead

 

[Paine]

I'm not sure that would work here. if I use 1 as null then that will mean every learner who has not yet finished their course will have "1" pass. In reality they have 0 passes because they have not completed yet...

Using IIF I have managed to convert these achievement rates back to null where appropriate. If a learner has not yet completed a unit of work then their achievement rate is neither 0% nor 100%, it is null, because they haven't failed or passed anything.

This means when we dump data into excel we can work out average achievement rates by gender, ethnicity, special needs, etc.

I hope this makes sense.

My last "mission" is to try and get the achievement rate calculation under control - is there any way to limit this to 1 or 2 decimal places?

Thanks!

 

[CBrighton]

Put the whole thing in a Round function:

Achievement rate: Round((CInt([Passes])/CInt([Ach_total]))*100,2)

The final 2 is the number of decimal places.

 

[Paine]

Every time I come on this forum I discover new things that I never knew existed - thank you CBrighton