Query of 2 related Queries

[MadAtMicrosoft]

I'm relatively new at Access.

I previously posted a tread regarding Tips and am going about it in a different way now.

*******************************************************
Background
First, I know everyone never reports all their tips. That part isn't MY problem. I am trying to create a way for employees to report whatever it is that they report. That is all my responsibility is.

Employees must (used loosley) report all tips they received. THEN, they must report if they "Shared" any of that gross tip with another Employee.

An employee will most always only report their "Actual Tips" once per day in the aggregate. (As opposed to posting individual tips received). However, they may "Share" those tips with multiple people during a day.

*********************************************************

I have 3 tables to meet this goal.

A) tblEmployees
B) tblActualReportedTips
C) SharedTips

*********************************************************

+ tblActualReportedTips has a relationship to tblEmployees via EmployeeID (One to Many)

+ tblSharedTips has a relationship to tblActualTipsReported via ActualTipsReportedID (One To Many)


*****************************************************

I first created two queries to total:

1 ) from tblActualReportedTips - Total BY Employee BY Day the ActualReportedTips;

2) from tblSharedTips - Total BY Employee BY Day the SharedTips

​

This part works fine.
********************************************************

THE PART THAT ISN'T WORKING

I then took those two queries and tried to create a 3rd query that would give me the complete information that shows BY EMPLOYEE BY DAY, the Total Tips Received and ALSO the Total Tips Shared.

HOWEVER, if an employee has NO INFORMATION from query "tblSharedTips" That person or Day is left out of the new query.

Am I missing a Null setting ????


I AM COMPLETELY CONFUSED why it won't show it with a Zero (0) for someone that has no "SharedTips" instead of just leaving them off altogether.


Any thoughts?

 

[WayneRyan]

Mad,

You are probably using Inner Joins. You need Left Joins.

When you have yhour query in Design View, have the query of tips received
on the Left Hand Side.

Right-Click on the line joining the queries and choose Option #2:

Include all from (tips received) ...

That should do it. Then the right-hand side will be Null for the missing ones
and you can use the Nz function to provide a zero-value for them.

hth,
Wayne

 

[MadAtMicrosoft]

WayneRyan

Thank you for responding!

I fiddled with it and finally after hours figured that out about 30 minutes before you posted the response. Wish you had been around sooner!

Just for my educational purposes. Based upon the above description, do you see a better way of accomplishing what I am attempting?

Again, I really mean thank you for responding.

MadatMicrosoft