473,888 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Strange query results

AP
We commonly use this method to "pivot/crosstab" table results. For
example lets say there are two offices, and we want the total sales
for each ofice in its own column(Next to each other). We do not want
to use a crosstab query so please do not recommend it.

Typically we do this. If the data is structured as follows

Office Sales Month
A 5006.432 1/1/02
B 5056.987 1/1/02
A 8882.625 2/1/02
B 5006.558 2/1/02

Result looks something like this

Month OfficeA OfficeB
1/1/02 5006.432 5056.987
2/1/02 8882.625 5006.558

We build the query with totals like this:

Field: Month (Group By)
Field: ASales: = Sum(iif([office]="A",[Sales],0))
Field: BSales: = Sum(iif([office]="B",[Sales],0))

This usually works great with normal numbers, however this particular
table stores all the numbers with the 3 decimal, and they are exact,
they never have more or less than 3 digits such as 5006.4325879. What
is really odd is sometimes the results will be close, but include
addiional decimals past the 3 spot, it is almost as if the 0 is not
really a zero. Changing the field type from single or double does not
help. Multiplying the number *100 and then later dividing back does
not help either.

Any idea of what is causing this to occur?
Nov 13 '05 #1
1 1435
AP wrote:
We commonly use this method to "pivot/crosstab" table results. For
example lets say there are two offices, and we want the total sales
for each ofice in its own column(Next to each other). We do not want
to use a crosstab query so please do not recommend it.

Typically we do this. If the data is structured as follows

Office Sales Month
A 5006.432 1/1/02
B 5056.987 1/1/02
A 8882.625 2/1/02
B 5006.558 2/1/02

Result looks something like this

Month OfficeA OfficeB
1/1/02 5006.432 5056.987
2/1/02 8882.625 5006.558

We build the query with totals like this:

Field: Month (Group By)
Field: ASales: = Sum(iif([office]="A",[Sales],0))
Field: BSales: = Sum(iif([office]="B",[Sales],0)) This usually works great with normal numbers, however this particular
table stores all the numbers with the 3 decimal, and they are exact,
they never have more or less than 3 digits such as 5006.4325879. What
is really odd is sometimes the results will be close, but include
addiional decimals past the 3 spot, it is almost as if the 0 is not
really a zero. Changing the field type from single or double does not
help. Multiplying the number *100 and then later dividing back does
not help either.

Any idea of what is causing this to occur?


Who knows. Maybe by switching back and forth between currency, double,
and single the precision values have been inserted.

In debug
? Left(cstr(5006. 4325879),Instr( cstr(5006.43258 79),".") + 3)
returns
5006.432

Maybe create a new column set to currency 3 with an input mask to allow
only 3 digits. Update the new column with the formula
Ccur(Left(cstr( CurNumber),Inst r(cstr(CurNumbe r),".") + 3))
where CurNumber is your current number field. Now run your query on
that field. If that returns the correct results, kill the old field,
save the table, then re-open and rename the new field back to the old field.

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
1656
by: smilesinblues | last post by:
Hi all, I have a table with a lot of songs. All songs have a field called hits. Everytime a song page is seen hits becomes hit++, everything smooth going. I also have a page where I run the query select * from table order by hits DESC
13
2751
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP (running off Apache 1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data base is using DB2 V5R3M0. The client is WinXP machine using the iSeries Client Access Driver ver 10.00.04.00 to connect to the database. The problem is that executing the exact same SQL select statement more than twice int a row stops produces results. The first two instances will always produce the...
0
2370
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
4
2363
by: Ryan | last post by:
Bit of an obscure one here, so please bear with me. I have two copies of a database which should be identical. Both have a complex view which is identical. I can open the views and the data is as expected and match. I can query it in several ways as detailed below. The 5th version of the simple query below based on the second copy of the view fails, but works under the first copy. /*1 Statement below works*/ SELECT * FROM AgentHierarchy
6
4954
by: Vance Kessler | last post by:
I am sure this is a configuration or permissions problem, but I cannot figure out what it might be. I have 2 SQL 2000 database servers: one is a linked Windows 2003 based server using a specified login account and the main server is Windows 2000. The user on the linked server is an SA. Both are running SP 3a. I EXEC a select statement that selects data from this linked server (shown below in detail) that runs just fine when executed...
2
1940
by: Mal | last post by:
Greetings. I have a perplexing problem....please help. I am having a problem with an insert query. (SQL below) When I run the query via code (executing the SQL string) it crashes Access and gives me a Page Fault error in OLEAUT32.dll When I run the query from the query window it gives the normal "You are about to run an action query..." then appears to run, yet DOES NOT
6
1707
by: Gary | last post by:
I have an application that has been working just fine for a couple of years. It queries a SQL database and returns some formatted data back to the client. I have a new client, who has a larger database than any of our previous customers. For example, the query to build the datatable now takes about 2 minutes instead of one minute or less. This is a third party database we are integrating with. He is getting very strange results. For...
0
1481
by: Wescotte | last post by:
<?php global $TABLE_GL_DATA; global $connect; $PREPARED_SQL = odbc_prepare($connect, "INSERT INTO $TABLE_GL_DATA VALUES (?,?,?,?,?,?,?)"); function Generate_GL_Data() {
5
2283
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed the query produced and opened it from a MS Query it started giving strange results. The first query when run alone returns 22 records, some of which have identical values in all fields. This is 100% correct. The second query returns nothing....
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11180
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.