Join Question?

I have two tables that I'm pulling information from, one for the current 
year, one for last year.  I am attempting to pull budget information for 
certain projects from both tables and add them together to get a cumulative 
funding picture for each project.  I can do this successfully as long as a 
particular project existed in both tables, however, new projects for the 
current year (that didn't exist last year) show blank budget values.  

I've tried all three kinds of joins and only #3 even shows the new projects, 
but again it only shows blank budget values.

Anyone know how I can get the new projects' budgets to show?
0
Utf
12/14/2009 8:40:02 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
735 Views

Similar Articles

[PageSpeed] 12

You surely have a table with all the projects, isn't it?

Bring that table in addition to the two others. Make a join keeping all 
records from the said table to each of the two other tables (option 2). Pull 
the data from any of the other two tables, except for the project name, take 
it from the table supplying all the projects.


Vanderghast, Access MVP


"Dez" <Dez@discussions.microsoft.com> wrote in message 
news:228ABA94-7A4D-4931-A96B-A1357713A217@microsoft.com...
>I have two tables that I'm pulling information from, one for the current
> year, one for last year.  I am attempting to pull budget information for
> certain projects from both tables and add them together to get a 
> cumulative
> funding picture for each project.  I can do this successfully as long as a
> particular project existed in both tables, however, new projects for the
> current year (that didn't exist last year) show blank budget values.
>
> I've tried all three kinds of joins and only #3 even shows the new 
> projects,
> but again it only shows blank budget values.
>
> Anyone know how I can get the new projects' budgets to show? 

0
vanderghast
12/14/2009 9:07:15 PM
The reason you are not getting a value for those projects present in only one
of the tables is that when you use a RIGHT OUTER JOIN (which is what option 3
in the dialogue in query design view represents) the returned budget column
position for those rows which don't exist in the table on the left side of
the join is Null.  In arithmetical expressions Null propagates, so anything +
Null = Null, so adding the budget from one year to the Null budget for the
previous year won't give you the value of the new year's budget, but Null.
This is because Null is not a value, like zero, but the absence of a value,
an unknown; it could be anything so the result of adding it to a value could
also be anything, i.e. Null.

This sort of situation is catered for by the Nz function which returns a
value in place of a Null, so in your case the expression to add the two
year's budgets would be:

Nz([LastYearTable].[Budget],0)+[ThisYearTable].[Budget]

If your expression includes more than one column from each table apply the Nz
function to each of the columns from last year's table.

However, the fact that you have to do this is due to having separate tables
for each year in the first place, which is not good design as it amounts to
encoding data as table names.  A fundamental principle of the database
relational model (the Information Principle) is that data is stored as values
at column positions in rows in tables and in no other way.

The correct model is to have a single table with a column from which the year
can be identified.  This can be a true date/time date type, in which the year
can be returned with YEAR([ProjectDate], or in the absence of specific dates
it could be an integer number ProjectYear column in which the year value per
project is stored.  Its then simply a matter of summing the budgets for the
years in question, grouping the query by project.

Ken Sheridan
Stafford, England

Dez wrote:
>I have two tables that I'm pulling information from, one for the current 
>year, one for last year.  I am attempting to pull budget information for 
>certain projects from both tables and add them together to get a cumulative 
>funding picture for each project.  I can do this successfully as long as a 
>particular project existed in both tables, however, new projects for the 
>current year (that didn't exist last year) show blank budget values.  
>
>I've tried all three kinds of joins and only #3 even shows the new projects, 
>but again it only shows blank budget values.
>
>Anyone know how I can get the new projects' budgets to show?

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
12/14/2009 10:21:00 PM
The Nz function got it.  Thanks!!

"KenSheridan via AccessMonster.com" wrote:

> The reason you are not getting a value for those projects present in only one
> of the tables is that when you use a RIGHT OUTER JOIN (which is what option 3
> in the dialogue in query design view represents) the returned budget column
> position for those rows which don't exist in the table on the left side of
> the join is Null.  In arithmetical expressions Null propagates, so anything +
> Null = Null, so adding the budget from one year to the Null budget for the
> previous year won't give you the value of the new year's budget, but Null.
> This is because Null is not a value, like zero, but the absence of a value,
> an unknown; it could be anything so the result of adding it to a value could
> also be anything, i.e. Null.
> 
> This sort of situation is catered for by the Nz function which returns a
> value in place of a Null, so in your case the expression to add the two
> year's budgets would be:
> 
> Nz([LastYearTable].[Budget],0)+[ThisYearTable].[Budget]
> 
> If your expression includes more than one column from each table apply the Nz
> function to each of the columns from last year's table.
> 
> However, the fact that you have to do this is due to having separate tables
> for each year in the first place, which is not good design as it amounts to
> encoding data as table names.  A fundamental principle of the database
> relational model (the Information Principle) is that data is stored as values
> at column positions in rows in tables and in no other way.
> 
> The correct model is to have a single table with a column from which the year
> can be identified.  This can be a true date/time date type, in which the year
> can be returned with YEAR([ProjectDate], or in the absence of specific dates
> it could be an integer number ProjectYear column in which the year value per
> project is stored.  Its then simply a matter of summing the budgets for the
> years in question, grouping the query by project.
> 
> Ken Sheridan
> Stafford, England
> 
> Dez wrote:
> >I have two tables that I'm pulling information from, one for the current 
> >year, one for last year.  I am attempting to pull budget information for 
> >certain projects from both tables and add them together to get a cumulative 
> >funding picture for each project.  I can do this successfully as long as a 
> >particular project existed in both tables, however, new projects for the 
> >current year (that didn't exist last year) show blank budget values.  
> >
> >I've tried all three kinds of joins and only #3 even shows the new projects, 
> >but again it only shows blank budget values.
> >
> >Anyone know how I can get the new projects' budgets to show?
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
12/15/2009 4:28:02 PM
Reply:

Similar Artilces:

Doing joIn with REGEXP? (MySQL)
Hi Im working with MySQL. I have similar data in 2 different schemas Im trying to relate. This particular example relates to US states being stored differently in both systems. So, say I have the 2 tables as follows: STATE_1 ----------------- | ID | STATE | ----------------- | 1 | AL | | 2 | AK | | 3 | AS | ................. STATE_2 ------------ | STATE | ------------ | US-AL | | US-AS | | US-AK | ............ I want to get the STATE_1.ID value where the STATE fields are equal. Note that STATE_2.STATE values are the same as those in ...

emails lost with compression error. Restore question.
I run Vista Home Premium and have Windows Home and Student. I use Windows mail. During a mail compression I got an error saying that the program was in use and an error had occurred. All of the e-mails were lost but there was some kind of automatic e-mail restoration. I was able to retreive what I needed from a restore e-mail folder. My question -- There are now a bunch of files in my trash bin relating to the restored e-mails. They showed up there by themselves. When I click on them they are listed as restored Windows Mail files and Recovered File Fragments. Since I have...

2 questions about CreateProcess(...)
Hi, How would I start an application hidden or minimized? PROCESS_INFORMATION pi; STARTUPINFO si; memset( &pi, 0, sizeof(PROCESS_INFORMATION) ); memset(&si, 0, sizeof(STARTUPINFO)); si.cb= sizeof(STARTUPINFO); si.dwFlags = STARTF_USESHOWWINDOW; si.wShowWindow = SW_SHOW; si.lpDesktop = ""; CreateProcess( NULL, (char*)"C:\\WinNT\\System32\\Calc.exe", NULL, NULL, FALSE, NORMAL_PRIORITY_CLASS, NULL, NULL, &si, &pi ); If I add "si.wShowWindow = SW_SHOW;", the application doe...

Question -Problem
Has anybody ever seen an error message (0x800cccF6) message? I cannot configure my Outlook to retrieve Hotmail. Works with other profiles on this computer running winxp. Stan-The-Man-2006 wrote: > Has anybody ever seen an error message (0x800cccF6) message? I cannot > configure my Outlook to retrieve Hotmail. Works with other profiles > on this computer running winxp. If you have a free Hotmail account, note that it won't work anymore (even your other profiles will eventually have problems) - see http://www.slipstick.com/addins/services/online.htm#hotmail ...

split question
Hello I want to split several lines of text in the TXT file "d:\Names.txt" They are seperated with an asterix (*). Like that: one*two three*four five*six seven *eight Please remember the added space after seven and after eight. I want to search for "seven " with a trailing space and replace it with "eight " with a trailing space. I have tried that: Const sNames = "d:\Names.txt" Set oFSO = CreateObject("Scripting.FileSystemObject") Set oNames = oFSO.OpenTextFile(sNames) aNames = Split(oNames.ReadAll, VbCrLf) I tr...

question about using the offset function
Is there a way to use the offset function (or another function) as a way to supply an expanding range to other functions like 'average'? For my purposes, there may be multiple series of numbers within say a column, with each 'series' (i.e. set of contiguous rows) separated by a few blank cells. The objective of the function would be to supply a limited set of numbers (which can expand) to the function whose result is the main interest. For example, within column D, I might have rows 5-10 representing adult male fish weights, rows 15-20 representing adult female fish weights ...

A few questions
Ok, I am starting to understand this monster... a few questions please: 1) I have all the emails (outgoing and incoming) from a certain account in the same folder grouped by conversation - The conversation title is taken from the subject of the first email, right? Can I change the title (name of) the conversation? 2) Is their a way I can have my outgoing messages in this folder have a special Icon (not just the open envelope) 3) Sorry for being picky but the indent space of the thread is to small. Is there a way I can increase the size of the indent? Tahnk Alot RB Raffi Bearmant ...

better to filter on join or where clause
If you have something in your where clause that could be put in the inner join would it be better there? SELECT * FROM tableA a JOIN tableB b on b.key = a.key Where b.status <> 15 and b.status <> 20 or SELECT * FROM tableA a JOIN tableB b on b.key = a.key and b.status not in (15,20) Any difference? Thanks, Tom There is no difference when using inner join. The optimizer will generate the same execution plan. It is more a preference/style of writing queries. I prefer to place filters in WHERE and leave only join predicates in the ON clause: SELECT ...

Question (Please)
One of users had a corruption of his profile under W2K3 Enteprise as an Active Directory user who logs on with his Smart Card. I'll call his Domain Name; Bob.Unlucky His profile is; c:\users\Bob.Unlucky When he logged on, he did not get his normal setup (desktop, icons, resolution, etc) and his MS Outlook wasn't setup which was a clear sign of a Profile corruption. I examined his PC and found a new profile; c:\users\TEMP All files in his Documents folder are encrypted using his Smart Card and he could not access any files in; c:\users\Bob.Unlucky\Documents ...

SumIf Question #2
Hi Can anyone tell me if the SumIf function can work with 2 different sets of criteria. eg: A1 = P B1 = M C1 = 20 A2 = P B2 = M C2 = 10 A3 = U B3 = M C2 = 10 I would like a formula to sum the C column numbers based on if the A column returns the letter U and the B column returns the letter M. But return a zero if the A column returns the letter P and the B column returns the letter M. I can get half way there using the SumIf function but I can't get a 2nd criteria in the formula. I run Excel 2000 Any help would be much appreciated =SUMPRODUCT(--(...

Publisher question... file types?
I am working on a beginner magazine and I want to import ads. I couldn't find a way to import a .pdf in publisher. What file type would people send in and can I import anything other than a .jpg? Thanks You can import a PDF into Publisher, quality might be an issue. Insert, Object, Adobe Acrobat Document, check Create from file. -- Mary Sauer http://msauer.mvps.org/ "llwriter828" <llwriter828@discussions.microsoft.com> wrote in message news:D3CE214B-A367-4C5D-B4EE-0D20046FEFED@microsoft.com... >I am working on a beginner magazine and I want to impor...

Urgent ADO question
Hi to all. First of all i wouldnt like posting questions for ADO in here but if you know a newsgroup for that i would be grateful. My question is how to execute stored procedures with ADO in c++ that return output parameters, using varchars as input and basically any information on that subject. Cheers, Dimi While there are people here that most likely can help you the new group you want is microsoft.public.data.ado -- ============ Frank Hickman NobleSoft, Inc. ============ "dim" <dt@imaj.co.uk> wrote in message news:uRmBtIxnDHA.360@TK2MSFTNGP12.phx.gbl... > Hi to all...

joining domain
hi! Is it possible to have the users rejoin xp,win7 to win2003 AD with the computer object already exist in the AD? i know that this can be done through administrator group, but can this be done by the user without adding them into the domain admain grp or accoutn operator? what's the best practice and previlege that i shd give to the support team who only need to able to join the pc into the domain? Thanks. They have to be able to delete and add. If you are talking about doing this to one machine not a real big deal but if you want folks to do this all the time it c...

doc/view question #2
[This was posted several days ago in m.p.vc.mfc.docview, but that forum appears to be all but dead.] I'm new to Doc/View, so pardon me for what's probably an amateurish question. I have an SDI app that needs to display two views of data simultaneously, one in the client area, and another in a docked dialog containing a list view (which uses the CCoolDialogBar class). I don't think I want two different CView classes, since both views are visible simultaneously. This leads me to believe that my existing CView class should handle updates of both the client area and the docke...

une question
je viens de me m'inscrire a votre groupe mais je ne parle que le fran�ais ,y a t-il des groupes fran�ais merci de me r�pondre cloclo jacqueline Bonjour. Essaye le group Francaise en bas news://msnews.microsoft.com/microsoft.public.fr.windows.vista.general -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "jacqueline degalet" <jacquelinedegalet@wanadoo.fr> wrote in message news:eFUFN26uKHA.1796@TK2MSFTNGP02.phx.gbl... > je viens de me m'inscrire a votre ...

Question Responses
Two Questions: 1. I have a program that is intended to run unattended. It runs, collects data and then closes all open workbooks. However, when it closes a workbook it asks "Do you want to save changes?". That requires an operator input. How do I avoid getting the question? 2. To start the process unattended I have to be in Excel. If my computer is on but Excel is not running, how do I get Excel open? Thanks for your help. Jim Walsh Hi, Q1. When you close the workbook use something along the lines of ActiveWorkbook.Close savechanges:=False or it it i...

Question regarding Linq to XML query
This is my first foray into Linq for XML, so I'm probably doing something relatively easy to correct. First off, I have some XML similar to below: <packet xmlns:ns2="http://www.swsoft.com/webservices/vzl/4.0.0/server_group" xmlns:ns1="http://www.swsoft.com/webservices/vzl/4.0.0/protocol" xmlns:ns4="http://www.swsoft.com/webservices/vza/4.0.0/vzatypes" xmlns:ns3="http://www.swsoft.com/webservices/vzl/4.0.0/types" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" time="2009-04-28T19:04:02+0000" priority="0" ...

GDI+ Image question
in my SDI application OnDraw(): .... GdiplusStartupInput gdiplusStartupInput; ULONG_PTR gdiplusToken; // Initialize GDI+. GdiplusStartup(&gdiplusToken, &gdiplusStartupInput, NULL); Gdiplus::Graphics graphics(pDC->m_hDC); Gdiplus::Pen myPen(Gdiplus::Color(255, 0, 0, 255), 2); graphics.DrawLine(&myPen, 0, 0, 600, 300); GdiplusShutdown(gdiplusToken); .... But when I run it(build with no error), I see nothing: no line!!! just blank client view. BTW, the GDI coordinates here in DrawLine() is logical or device? It looks good to me. But there is one thing that I am n...

ISINTEG questions
I am running Exchange 5.5 SP4 on NT4 SP6a for mail. In the last week we've had to run a hard recover (ESEUTIL /P) on our pub.edb and priv.edb due to some corruption. After running the repair and then a defrag (ESEUTIL /D) and checking consistency, we ran ISINTEG -FIX -TEST ALLTESTS on both databases (using the -PRI and -PUB switches) at least 3 times. After running the ISINTEG -PATCH to get rid of the -1011 we got when trying to restart the Information Store we were finally able to run the DS/IS Consistency Tool (all the above according to MS KB article 272570 XADM: How to Recover from ...

Quickbooks Integration: Questions for CPA
I've not yet integrated Quickbooks with RMS and it just so happens I'll be meeting with my accountant tomorrow to discuss some unrelated issues...to kill 2 birds w/ one stone, what are some of the pertinent questions that the CPA can help out with in terms of any mapping between the two applications? Thanks in advance! hitman, Call me, I think we're in the same area code. 539-3585 Ken "hitman" <hitman@discussions.microsoft.com> wrote in message news:B704D3CB-267A-46F5-A602-A24C50F8FCA9@microsoft.com... > I've not yet integrated Quickbooks with RMS an...

Joining worksheets / outer join
I've found some spreadsheet joining discussion threads, but I haven't found one which I think is applicable... Basically, I have 5 worksheets in an Excel file to be used as a mail merge. Each worksheet has lists of companies and the industries that they handle. Some companies have offices at different addresses, which should remain as seperate rows. Some companies handle more than one industry and are thus in more than one worksheet. Some of the companies have one business contact for all industries, some have seperate business contacts. I want one worksheet, which merges data from...

Timer question
I have this timer code I got courtesy of Chip Pearson (http://www.cpearson.com/excel/TimedClose.htm) and am wondering if it can be altered in such a way that when I get down to 3 minutes (NUM_MINUTES is set to 10 minutes) ClosingSplashScreen.Show will run? Private Sub Workbook_Open() On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Change the ONTIME timer to 1 minute instead of 10 minu...

Multiple left joins
I'm trying to develop a conditional join that will allow me lookup the data in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B&...

Question on mixing MFC and Winform
We had some problems (I posted earlier about overlapping windows that may be transparent, draw order etc). Well we got the drawing working more or less but there is quite a bit of flicker. I was curious if we switched to WinFroms if perhaps things might work better. Our app is an unmanaged MFC app (we don't really use any of the Doc/View support). I've started reading and researching mixing of WinForms with MFC. I'm curious if I replace the CView with CWinFormsView and then create child windows of the type CWinFormsControl if this may help. But I'm not finding mu...

A Question to the Gurus
Hi, I am developing an application with several components in it. The main application will have a GUI and there are several components like BHO, MS Office Addins, Clipboard trackers etc. I have a problem here. These components should communicate with the main application by some means. It has to pass some events or some values to the main application. I will be creating both the components and the main application and I would like to know, the best method to connect the components to the main application. I have pretty good exposure to COM and Windows programming. Also Is it possible...