Combined Table Query

[SydsPop]

I'm an Access beginner/intermediate level user and I'm trying to merge data from a performance table (3 of calls per day) and a cash collected table (cash per day) I've tried several different combinations within the query but am unable to get the result to show zero cash on a day calls were taken, but money wasn't collected. Any help would be greatly appreciated!

 

[MSAccessRookie]

I'm an Access beginner/intermediate level user and I'm trying to merge data from a performance table (3 of calls per day) and a cash collected table (cash per day) I've tried several different combinations within the query but am unable to get the result to show zero cash on a day calls were taken, but money wasn't collected. Any help would be greatly appreciated!

In order to create a Query to properly display all of the data that you want, the Tables will need something in common to Join on. Can you show the STRUCTURE (not the contents) of each of the Tables involved? That way we can help you further

 

[SydsPop]

The Table fields are:
Tbl MonthlyCash - Cash Date, Agent Name, Amt Coll, Supervisor
Tbl PerfRPT - Date, Agent Name, Team, Group, cash, ACD Calls, Staffed Hours

I have them joined by "Agent Name" and then was trying the following IIf statement, but am getting "#Error" as a result: Cash: IIf([TBL Monthly Cash]![Cash Date]=[TBL PerfRPT]!DATE," [TBL Monthly Cash]![Amt Coll] "," ")

The query duisplay fields are all fields from TBL PerfRPT with "cash" being populated by the IIf

 

[MSAccessRookie]

The Table fields are:
Tbl MonthlyCash - Cash Date, Agent Name, Amt Coll, Supervisor
Tbl PerfRPT - Date, Agent Name, Team, Group, cash, ACD Calls, Staffed Hours

I have them joined by "Agent Name" and then was trying the following IIf statement, but am getting "#Error" as a result: Cash: IIf([TBL Monthly Cash]![Cash Date]=[TBL PerfRPT]!DATE," [TBL Monthly Cash]![Amt Coll] "," ")

The query duisplay fields are all fields from TBL PerfRPT with "cash" being populated by the IIf

I have One SUGGESTION, and one QUESTION.

SUGGESTION: Try replacing the "!" with "." and see if that makes any difference.

Cash: IIf([TBL Monthly Cash].[Cash Date]=[TBL PerfRPT].DATE," [TBL Monthly Cash].[Amt Coll] "," ")

QUESTION: Why did you place the quotes around " [TBL Monthly Cash].[Amt Coll] "? The effect of this will be to have the result if True be the string [TBL Monthly Cash].[Amt Coll], not the cash value. If you want/need the cash value represented as a string, you can use the cStr() Function to get that for you.

 

[SydsPop]

Thanks for the reply rookie, I got the same result, though. All it populates with is "#Error"

 

[SOS]

DATE is an Access Reserved Word and should NOT be used as a field, or object name. Change your field that is named DATE to something else like PerfDate or something.

 

[SydsPop]

Thanks, SOS, I made the change and also removed the space on "Cash Date", it still didn't work...