Loss of data in query field when selecting the totals

[Lewe22]

I have a basic query [qryGenerateFacilPaymentsStep3] which is selecting all information from another query [qryGenerateFacilPaymentsStep2]. As soon as i set the query to show the totals (in order to perform a sum) all information held in a field named [forenames] is replaced with strage characters like "ᘈ
" or "ᘈ".

When removing the totals the forenames are all shown normally again.
I have checked the underlying table which contains the [forenames] field and there is nothing wrong with the data in there. I have also checked my other queries and everything is ok there too.

Does anyone know why this might be happening? It's really frustrating.

[puppydogbuddy]

Sounds like you are encountering nulls in your sum. Try using the NullToZero Numeric function in your summation formula. If you don'tknow how, post the summaion formula you currently have for one of your columns, and I will incorporate the NZ function for you.

[Lewe22]

Not really sure what you mean by summation formulae. What is that and where can i copy it from?

Also, even if i take the sum out but keep the grouping the same thing is happening...

[puppydogbuddy]

Not really sure what you mean by summation formulae. What is that and where can i copy it from?

Also, even if i take the sum out but keep the grouping the same thing is happening...

The summation formulae would be shown in the sql view of your totals query....but if the loss of data is occuring with the grouping, you probably need to apply the Nz function at the column level. To do that you will have to create an alias column as illustrated below:

fieldX ..............aliasOfFieldX: nz([fieldX], 0)

then group and sum the alias column, not the original column.

[NeoPa]

You may want to share some more details Lewe.

I'm guessing this may be a GROUPing issue, but we have so little actual information to go on that it's hard to check.

[Lewe22]

I don't really know what was happening here, but i will explain what i did to resolve it.

I had created a series of queries, lets call them query1, query2 and query3.
Each query was perfoming its own task and was then referenced in the next query in sequence. (Eg, query1 was called as a table into query2 etc. etc.

I had linked a table named [tblStudent] into query1. This was showing [title], [forename] and [surname]. That information was then pulled into query2 where a grouping was performed on the data. Then again into query3 where another grouping was performed.

My problem was that in the result set of query3 the data held within the [forename] field was being replaced with strage characters like "ᘈ_" or "ᘈ" for every record as soon as a grouping was applied. When the grouping was removed [forename] would revert back to its original state. Having checked back to query2 it was apparent that this was only happening at query3.

This baffled me for some time as i looked for a possible explanation but to no avail. Perhaps someone here might now know why this was happening? I'd be delighted if someone could enlighten me!

Anyway, to rectify this situation i simply removed [tblStudent] from query1, thus removing [title], [forename] & [surname] from all 3 queries.

I then created query4 and linked tblStudent in at that point. Bingo!

As easy, and maybe as obvious as my resolve was, my main problem is that i always want to know why something has happened. I've lost so many days on this going off on a tangent! Maybe i just need to let this one go!!

Thanks for all your assistance everyone.

Lewe

[puppydogbuddy]

The only thing that comes to mind is that the data in table1 is at a different level of detail than the data in one or more of your first 3 queries,thus creating a comparison of apple to oranges conflict.

[Stewart Ross]

I've come across name conflicts in cascaded queries where fields with valid aliases are not resolved correctly by the JET database engine. In the ones that have happened to me, JET has substituted the fully-qualified field name table_x.field_y for the aliased name provided.

This has only happened when there were more than one occurrences of a field within the cascaded queries. For example, academic year occurring in several student-related tables participating in the cascaded queries, leading to JET substituting a fully-qualified name for one of the aliases. This despite all names being correctly aliased in all SQL throughout. I've sometimes had to do daft things, like renaming the alias Session to TheSession to avoid such a name conflict interfering with the end result of the query.

The fact that you succeeded when you deleted a table from one of your cascaded queries then rejoined it back in a later query is quite significant, I think.

-Stewart

[Lewe22]

Thanks again for your time everyone. I'm just glad it's working now and i can move on!