UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Join 'all' Both Ways?, Access 2010    
 
   
firlandsfarm
post Mar 19 2017, 04:47 AM
Post#1



Posts: 246
Joined: 28-April 02
From: Heathfield, England


I can 'Join' I can 'Left Join' and I can 'Right Join' but how can I 'Both Join' as I'm going to refer to it for want of a technical term. Let me explain ...

I have 4 datasets that I want to combine into one dataset ... each contains data that is Date orientated but none contain all dates. I want to bring them together in one combined dataset (as a query, not a fixed table) with columns as ...

Date : dataset1 : dataset2 : dataset3 : dataset4 : SumDatasets

Some dates may have an entry for all 4 datasets but some only 3, 2 or 1 entries. Some dates may have no entry in any of the datasets in which case I would like to eliminate those dates.

Is there a way with Access to 'Both Join'?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
LPurvis
post Mar 19 2017, 05:20 AM
Post#2


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

The final line: "Is there a way with Access to 'Both Join'?" is the key part.
In that, not directly as it would be with other SQL dialects. (Where the FULL OUTER JOIN is supported.)

However, you can use a (non-updatable) workaround.
If you Google "FULL OUTER JOIN in Access" then you should get decent examples of the method. (Non-updatable by definition once you're UNIONing result sets.)

Cheers

--------------------
Go to the top of the page
 
firlandsfarm
post Mar 19 2017, 06:27 AM
Post#3



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Thanks Lee and yep, I'm pleased to say I had that line of thought after my posting (the Union query, not Google), should have explored that first maybe but I just assumed you guys would have a neater solution. smile.gif I did a Union query (not Union All) and then did Left Joins directly to each table (not pass through Joins) and am pleased to see it works.

The only problem I'm getting now is having added a simple Total column by Col1 + Col2 + Col3 + Col4 it's blank unless there is a value in each sourcing field. I can put in 'if Null then 0' functions but just wonder if there is a better way of dealing with arithmetic on Null values?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Jeff B.
post Mar 19 2017, 08:21 AM
Post#4


UtterAccess VIP
Posts: 9,881
Joined: 30-April 10
From: Pacific NorthWet


If you're using a query to calculate that total of columns, are you using the Nz() function or an IFF() function?

You have my curiosity up, though ... what kind of values are you storing 'across' columns that you need to add?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
firlandsfarm
post Mar 19 2017, 10:44 AM
Post#5



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Well Jeff, I was using IIF after my response to Lee pending a better idea, and it was working but your suggestion of Nz() certainly does it for me so am now using that ... thanks, another function learned smile.gif I could get each formula to return zero in place of a Null value where no calculation is performed but then that would not tell the full story of a zero value or no calculation. Also I find a lot of zeros untidy, that's just a personal preference thing.

Now for the what am I doing bit. I don't feel there is much to be gained by going into the detail so I'll explain generically.
I am applying 4 mutually exclusive conditions to a set of data daily every day.
Where one or more or none of the conditions may be met each day.
If a condition is met a formula is applied, again the formulae are mutually exclusive, one formula per condition. A value is derived for any condition that is met.
So up to four values can be derived each day and each value is required for off database work so needs to be calculates independently.
As a summary I wanted a schedule that would list the values calculated under each condition for each day and for the daily total to be summed as an extra column

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 11:00 AM