4th Tier Query Returning No Data

[Elysyn]

Greetings!

I have a 4th Tier Query that is returning no data. This query pulls from 4 separate queries above it for the data. The problem is that one of the "Bucket" queries also has no data. My question is, how do I get my query to understand that "No Data" is okay and return the values for the other three? I've tried working with Nz and IsNull, and I just can't seem to get it to work.

Please let me know if you need more details... I think this adequately describes my problem - but I could be wrong...

Thanks in advance for your input and advice, it is greatly appreciated!!

 

[Joe4]

Two tips:

1. You can modify the relationships on the queries to suit your needs. For example, let's say you have two queries, Query1 and Query2 and they are linked together in another query. In Design mode of your new query, iIf you double click on the line that links the two queries, you can edit the realtionship to select "Include ALL records from Query 1 and only those matching records from Query2" or vice versa.

2. You can do each query separately and then use a Union Query to combine all your results together. To see instructions on creating a Union query in Access, see: http://support.microsoft.com/default.aspx?scid=kb;en-us;114731&Product=acc

Hopefully, one of these methods will help you.

 

[Elysyn]

Thanks, jmiskey.

I'm going to try the Union Query and see how that goes. I think that's the direction I need to go, as editing the relationships doesn't seem to fix the problem.

I may be back if I can't figure out the Union...

 

[Elysyn]

Okay...

The Union Query works... but it doesn't give me the format I need. I need to have the data broken out into 4 distinct fields. Here's a little more background explaining where this is going.

-Tier One Collects the data from each separate Bucket, and performs Calculation One.

-Tier Two takes the data from Tier One and performs Calculation Two.

-Tier Three takes the data from Tier Two and Performs Calculation Three.

-Tier Four takes the resultant calculation from Tier Three for all 4 buckets.

The buckets are based on call skills - not every associate is going to have the same skill set, thus one bucket may be empty, and that empty bucket will vary. (Which is why the relationships don't work, so far as I can tell.)

I am working towards getting a final query so I only have to reference one query in the form to get all the data that I need. (This may be inefficient, I am not certain...) I have 3 different data sets that I am pulling into this final query. Is this the right direction to go, or would referencing several queries in the form be the better option?

Thanks again!