I have a table [AAAM] of 15 columns as specified below:
AA100200:Projec t Code
AAAM140:Staff Code (Individuals' ID #)
AAAM010: Year
Then 12 Columns for each month starting AAAM020 to AAAM130 where the time spent by each individual on the given project in a particular year is entered. I want to aggregate time in all the columns from AAAM020 to AAAM130 grouped by Project Code, Year and Staff Code .
With this (http://www.thescripts. com/forum/thread534172.ht ml) thread in mind , I made two separate queries.
Qury 1 on the Table Named AAAM:
-------------------------------
SELECT dbo_AAAM.AA1002 00, dbo_AAAM.AAAM01 0, dbo_AAAM.AAAM14 0, Sum(dbo_AAAM.AA AM020) AS SumOfAAAM020, Sum(dbo_AAAM.AA AM030) AS SumOfAAAM030, Sum(dbo_AAAM.AA AM040) AS SumOfAAAM040, Sum(dbo_AAAM.AA AM050) AS SumOfAAAM050, Sum(dbo_AAAM.AA AM060) AS SumOfAAAM060, Sum(dbo_AAAM.AA AM070) AS SumOfAAAM070, Sum(dbo_AAAM.AA AM080) AS SumOfAAAM080, Sum(dbo_AAAM.AA AM090) AS SumOfAAAM090, Sum(dbo_AAAM.AA AM100) AS SumOfAAAM100, Sum(dbo_AAAM.AA AM110) AS SumOfAAAM110, Sum(dbo_AAAM.AA AM120) AS SumOfAAAM120, Sum(dbo_AAAM.AA AM130) AS SumOfAAAM130
FROM dbo_AAAM
GROUP BY dbo_AAAM.AA1002 00, dbo_AAAM.AAAM01 0, dbo_AAAM.AAAM14 0;
------------------------------------
Then based upon Query 1, I am running this query:
SELECT Query1.AA100200 , Query1.AAAM010, Query1.AAAM140, Sum([SumOfAAAM020]+[SumOfAAAM030]+[SumofAAAM040]+[SumOfAAAM050]+[SumOfAAAM060]+[SumOfAAAM070]+[SumOfAAAM080]+[SumOfAAAM090]+[SumOfAAAM100]+[SumOfAAAM110]+[SumOfAAAM120]+[SumOfAAAM130]) AS Expr1
FROM Query1
GROUP BY Query1.AA100200 , Query1.AAAM010, Query1.AAAM140;
But there are no results. Can anyone please guide me as to where is the mistake.
Please help. Thanks.
Jinx
4 4642 jimatqsi 1,276
Recognized Expert Top Contributor
Hi Jinx,
Tell us, does the query1 give any result? Is it only after you run the second query that the results disappear?
What I do whenever I get this sort of thing I start a process of elimination. First I copy by query to a temporary name. Then I start eliminating criteria one at a time until I get some results. Then I look more carefully at the criteria and at the data being tested in that criteria.
If that doesn't lead me to the problem, then I look at my JOIN statements. I'll eliminate one table or another from the JOINs to see what data is delivered under each circumstance. Or I will change the details of the join, change from RIGHT to LEFT, that sort of thing, to see the difference in the results.
Eventually I see and understand the problem.
Jim
Thanks Jim.
This is what I did. Created a new database with only one table and 15 Columns. Again created two queries and started entering dummy data. And this is my observation:
"With any cell in a column having a 'NULL' value, if you apply the aggregate function of SUM() with two or more columns as the function arguments in a Total Query, Access query simply returns no values. There should either be a zero or some value in each and every cell of the columns of data you are trying to process.
Is it a bug or a deficiency in Access 2003. I don't know.
Anyway, thanks a lot for your time and guidance.
I really appreciate this.
Jinx
jimatqsi 1,276
Recognized Expert Top Contributor
Hi Jinx (I love saying that :)),
Sounds like you need to test for nulls and turn the nulls into zeros before summing. Instead of simplying references the field that comes out of the first query you need to reference it like this: - SomeName:IIF (IsNull([thefieldname]),0,[thefieldname])
That will return a 0 in SomeName if you have a null, and the value of SomeName if you don't.
Crosstab queries are famous for posing this problem. It got me when I was a little newer than I am now (I'm still kinda new to Access/VB).
Jim
Thanks Jim.
This is what I did. Created a new database with only one table and 15 Columns. Again created two queries and started entering dummy data. And this is my observation:
"With any cell in a column having a 'NULL' value, if you apply the aggregate function of SUM() with two or more columns as the function arguments in a Total Query, Access query simply returns no values. There should either be a zero or some value in each and every cell of the columns of data you are trying to process.
Is it a bug or a deficiency in Access 2003. I don't know.
Anyway, thanks a lot for your time and guidance.
I really appreciate this.
Jinx
Thank You Jim....Yes this is what I need to do. I have a lot of NULLs in the table and of course I don't want to replace them with ZEROs.
Thanks for the help....I really appreciate this.
J..
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ralph Freshour |
last post by:
I'm not sure the follow multiple table query is the right way to do
what I need to do although it seems to be working:
$php_SQL = "SELECT * ".
"FROM basics, personal, photos ".
"WHERE basics.member_name = personal.member_name ".
"AND basics.member_name = photos.member_name ".
"AND basics.account_creation_date >= DATE_SUB(NOW(),
INTERVAL 30 DAY)";
|
by: JT |
last post by:
Hello all, I've been using sql and mysql in particular for several
years now, but I'm having some trouble coming up with a query that
will pull the data that I need from a mysql table without using
multiple queries. Here's what I'm attempting to do:
Table1
column1-a, column2-a, column3-b, column4-b, column5-c, column6-c
in this example, each the table is storing 2 values for one record,
|
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
|
by: Aaron |
last post by:
Complete code follows.
I am new to .NET programming (and programming in general) and I am having a
difficult time understanding how to fill a variable in one sub, and then
access it from another. I have tried declaring them as shared, public,
friend, etc and I always get an error stating that something is not valid
on a local variable declaration.
For example, in the following code for Sub DataGrid_Select, I have
CurrentID and...
|
by: Bill |
last post by:
I have a 200 record database that includes a date/time field,
AnnivDate, for a wedding anniversary. AnnivDate has nulls and some
incorrect year data. I have been creating the Access database using
data imported from an Excel file and the person entering into Excel
only cared about the month and day portion and Excel added the current
year to the field value. I want to produce a report that shows
upcoming anniversaries using a parameter...
| |
by: n |
last post by:
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
|
by: adtvff |
last post by:
Hi,
Given a large ascii file (delimited or fixed width) with one ID field
and dimensions/measures fields, sorted by dimensions, I'd like to
"flatten" or "rollup" the file by creating new columns: one for each
combination of dimension level, and summing up measures over all
records for a given ID.
If the wheel has already been invented, great, please point me in the
right direction. If not, please share some pointers on how to think
|
by: Sham |
last post by:
I am trying to perform the following query on a table that has been
indexed using Full Text Search.
The table contains multiple columns than have been indexed. (Below,
all xml columns are indexed).
dbo.maintable(ProfileID int pk)
dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml)
I want to perform a query that will return any rows that contain ‘x’
and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
|
by: uno7031 |
last post by:
Help Please!!! Adding 5 Days to another Date in an access query
Good Morning,
Help please…. I am new to access and trying to write a query that will add 5 days between a RecDate and a DLPayDate
I created the query in design view of access.
Current Query: SELECT PaymentCalculator2.ID, PaymentCalculator2.RecDate, DateAdd("w",5+1,) AS DLPayDate, DateAdd("w",5,) AS DLPayDate2
FROM PaymentCalculator2
WHERE...
|
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...
|
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,...
| |
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |