I would like to create a report that does the following:
summarize Ppodamt, collect_amt
by provno, repyear, dateproc, upp_check_month.
So far, I have made some headway, but I feel like it was all by luck, and it needs to be scrapped for lack of understanding of the big picture. Where I get stuck is when I try to create a table that has all the correct info to report off of, but I get redundant values for either the COLLECT_AMT or PPODAMT fields, and waaay inflated tables...
Any suggestions? I've been whacking my head over this for three full business days now!! aaaugh!
Try pasting this into the query designer SQL view an see if it works (and does what you want)!?
(but check that I have table and field names correct?)
SELECT Allowances.PROVNO, Allowances.REPYEAR, Allowances.DATEPROC, Sum(Allowances.Ppodamt) AS SumOfPpodamt, Sum(Collections.UPP_CHECK_month) AS SumOfUPP_CHECK_month
FROM Allowances INNER JOIN Collections ON (Allowances.DATEPROC = Collections.DATEPROC) AND (Allowances.REPYEAR = Collections.REPYEAR) AND (Allowances.PROVNO = Collections.PROVNO)
GROUP BY Allowances.PROVNO, Allowances.REPYEAR, Allowances.DATEPROC;
Although your post is a little ambiguous I have assumed that the three common fields in the two tables are all 'joined'.
Your response sucessfully addressed creating the report, though it was shy one crucial element, that being the addition of one extra field from one of the tables.. I will try to explain:
Here is what I have now:
SELECT [Monthly Allowances].PROVNO, [Monthly Allowances].YEARREP, [Monthly Allowances].DATEPROC, sum ( [Monthly Allowances].PPODAMT) AS SumOfPPODamt, Sum (Collections.COLLECT_AMT) AS SumOfCollect_Amt
From [Monthly Allowances] Inner Join Collections ON ( [Monthly Allowances].dateproc = collections.dateproc) AND ( [Monthly Allowances].yearrep = collections.hosp_fye) AND ( [Monthly Allowances].provno = Collections.provno)
GROUP BY [Monthly Allowances].Provno, [Monthly Allowances].YEARREP, [Monthly Allowances].DATEPROC;
There is an additional field I need to incorporate into this equation, called UPP_CHECK_DATE, which is a numerical representation of a date.
Once I add that to the SELECT and GROUP BY statements, I get repeating values in the SumOfPPODamt field, which throws off my calc. The record count increases from ~10,000 records with your good SQL statement, and the count increases to about ~36,000 records with the UPP_CHECK_DATE field, which sounds like it may be right, tho the repeating value in SumOfPPODamt field is my main problem!! Is there any way through this?
That is the root of my current brand of DB misery.
Thanks, Mike. Here is what I did, which is probably a long way around:
I modified both the COLLECTIONS file and the ALLOWANCES tables, gave the key fields the same name ([PROVNO], [PROCDATE], [HOSP_FYE]). Then I exported the key fields from COLLECTIONS to a new table [combo1]. Then I APPENDED the key fields from ALLOWANCES to [combo1].
Next, I ran a make table query grouped by fields [PROVNO], [PROCDATE], [HOSP_FYE] on [combo1] to eliminate dupes, and called that table [uniques]. This table provided me with the complete list of all the necessary key fields, without duplicates.
Next, I ran a make table query for each of the [ALLOWANCES] and [COLLECTIONS] tables, grouping by the same as listed above, and summarizing on the dollar amounts [PPODAMT] and [COLLECT_AMT], respectively. The new tables were called [ALLOWANCES_SUM] and [COLLECTIONS_SUM].
Once I did this, I created a final query that used all fields from [uniques] as the pk, related to the identical fields in [ALLOWANCES_SUM] and [COLLECTIONS_SUM]. I then modified each of the relationships to reflect option 2, all records from uniques and only records from target table. The resulting file had no duplicates, and generated exactly the information I needed to create the report!
Y'all know that feeling of relief after a week of seemingly wandering around your database blindfolded, with your db ego wilting every minute, then finally seeing your efforts pay out? yeah. I'm a hack, but I get there...