mmmelissa717
New Member
- Joined
- May 26, 2015
- Messages
- 4
I'm really fairly new to writing and building stuff in access so bear with me please.
I have two different tables that store cash transactions by year for each company deriving information from two different sources, (cash in & out the door vs. receipts) Eventually I'd like a report that would sum up all of 201X cash transaction (Rec'd/Distbursed) by year for a specific fund and compare that and populate a difference to what the receipt says. So basically it looks like this.
Per Receipt Per Cash Difference
Cont. Dist. Cont. Dist. Cont. Dist. Net
2009 161,978 0 168,091 - (6,113) - (6,113)
2010 71,254 (1,752,916) 71,129 (1,758,906) 125 5,990 6,115
As of right now I was able to build two queries that I can get the sum of cont. and dist. by year for both the cash and receipt table. (sorry formatting is messing with the look of the table) I tried to bold alternating columns to be able to read it better.
My questions is how to build them or combine them to get that Difference section of the table.
Here is the SQL for the queries that I built:
SQL
SELECT DISTINCTROW [qry_Cash Rec 001].Year, [qry_Cash Rec 001].Rollup, [qry_Cash Rec 001].[Family of
Fund Name], [qry_Cash Rec 001].TTYPS, Sum([qry_Cash Rec 001].Amount) AS [Sum Of Amount]
FROM [qry_Cash Rec 001]
GROUP BY [qry_Cash Rec 001].Year, [qry_Cash Rec 001].Rollup, [qry_Cash Rec 001].[Family of Fund Name],
[qry_Cash Rec 001].TTYPS
HAVING ((([qry_Cash Rec 001].Rollup)="1400") AND (([qry_Cash Rec 001].TTYPS)="Cash Disbursed")) OR
((([qry_Cash Rec 001].Rollup)="1400") AND (([qry_Cash Rec 001].TTYPS)="Cash Received"));
I have some extra stuff in there to add rollups and what not but I don't think that changes the results at all (or shouldn't anyways.) They are set up exactly the same just different table names.
Any help even getting a query going and then I export to Excel would be helpful at this point.
Thanks!
I have two different tables that store cash transactions by year for each company deriving information from two different sources, (cash in & out the door vs. receipts) Eventually I'd like a report that would sum up all of 201X cash transaction (Rec'd/Distbursed) by year for a specific fund and compare that and populate a difference to what the receipt says. So basically it looks like this.
Per Receipt Per Cash Difference
Cont. Dist. Cont. Dist. Cont. Dist. Net
2009 161,978 0 168,091 - (6,113) - (6,113)
2010 71,254 (1,752,916) 71,129 (1,758,906) 125 5,990 6,115
As of right now I was able to build two queries that I can get the sum of cont. and dist. by year for both the cash and receipt table. (sorry formatting is messing with the look of the table) I tried to bold alternating columns to be able to read it better.
My questions is how to build them or combine them to get that Difference section of the table.
Here is the SQL for the queries that I built:
SQL
SELECT DISTINCTROW [qry_Cash Rec 001].Year, [qry_Cash Rec 001].Rollup, [qry_Cash Rec 001].[Family of
Fund Name], [qry_Cash Rec 001].TTYPS, Sum([qry_Cash Rec 001].Amount) AS [Sum Of Amount]
FROM [qry_Cash Rec 001]
GROUP BY [qry_Cash Rec 001].Year, [qry_Cash Rec 001].Rollup, [qry_Cash Rec 001].[Family of Fund Name],
[qry_Cash Rec 001].TTYPS
HAVING ((([qry_Cash Rec 001].Rollup)="1400") AND (([qry_Cash Rec 001].TTYPS)="Cash Disbursed")) OR
((([qry_Cash Rec 001].Rollup)="1400") AND (([qry_Cash Rec 001].TTYPS)="Cash Received"));
I have some extra stuff in there to add rollups and what not but I don't think that changes the results at all (or shouldn't anyways.) They are set up exactly the same just different table names.
Any help even getting a query going and then I export to Excel would be helpful at this point.
Thanks!