Trying to include all values from 2 tables in Select Query - even where there is no match

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I am pulling from a database (that is in very poor shape) trying to
determine weekly hours out of a project accounting system with little
luck. The problem is, I'm pulling each Friday and then have to
subtract YTD Hours from Week 1 out of Week 2 (Current YTD - Previous
YTD).

The fields I have to work with are Name, Cost Center, and Cost Center
Type.

In my query I concatenate these values to create a unique field, so
that I can link the tables together. Where I run into problems, I
have 3 options...Show where they are the same, Show only from table 1,
or show only from table 2.

Often what is happening, after I pull week 1 the users are adjusting
thier hours. My most current ytd table should include all hours...but
if I link based on my concatenation and they move hours, then in
theory everything in Previous YTD table should be in Current YTD
table...but because items were moved I no longer have a match.

Is there a way to say...show me Everything from Table 1 and Everything
from Table 2. Where there is a match, subtract Previous YTD from
Current YTD. Where there is not a match give me YTD value from
whichever table has entry?
 
I am pulling from a database (that is in very poor shape) trying to
determine weekly hours out of a project accounting system with little
luck. The problem is, I'm pulling each Friday and then have to
subtract YTD Hours from Week 1 out of Week 2 (Current YTD - Previous
YTD).

The fields I have to work with are Name, Cost Center, and Cost Center
Type.

In my query I concatenate these values to create a unique field, so
that I can link the tables together. Where I run into problems, I
have 3 options...Show where they are the same, Show only from table 1,
or show only from table 2.

Often what is happening, after I pull week 1 the users are adjusting
thier hours. My most current ytd table should include all hours...but
if I link based on my concatenation and they move hours, then in
theory everything in Previous YTD table should be in Current YTD
table...but because items were moved I no longer have a match.

Is there a way to say...show me Everything from Table 1 and Everything
from Table 2. Where there is a match, subtract Previous YTD from
Current YTD. Where there is not a match give me YTD value from
whichever table has entry?
Click to expand...

A UNION query should help. See UNION in the Access help - it's pretty good.

THat said... this database does indeed appear to be in poor shape. "Previous
YTD" and "Current YTD" fields or tables should simply *not exist*! These are
(at least from the names) derived data which should be calculated on demand
from a table of hours actually spent.

John W. Vinson [MVP]
 
Back
Top