Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Can't seem to get my head around this...

    I'm totally lost on this... I will try my best to explain and hope that someone can shed some light on how to steer this thing...

    I have to create a balance report that summarizes data from two tables.

    One table (collections) has 225,000 records. The second table (allowances)has 76,000 records.

    Following are the key fields that both tables have in common:
    PROVNO
    REPYEAR
    DATEPROC

    Additionally, there are the following elements that need to be represented in the report:

    (Collections_Table)
    Collect_amt - (Number)
    UPP_CHECK_month - (Number)

    (Allowances_Table)
    Ppodamt - (Number)

    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!

    Thanks for even considering to tackle this...

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    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'.

    MTB

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    MTB,

    You are a brave soul, indeed.

    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,

    mk

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You didn’t mention UPP_CHECK_DATE before!

    What is a numerical representation of a date (is it a date or not?).
    You have not indicated which table it is in.

    I assume it is in Allowances. If so, then I also assume that there as more than one (average of 3!) ‘Dates’ for each PPODAMT, giving multiple entries of SumOfPPODAMT.

    However, I think that the total of the SumOfPPODAMT for the whole recordset should be the same with and without the UPP_CHECK_DATE grouping !!??

    It is difficult to be sure without the knowing the PKs & Foreign Keys for the two tables.

    Hope that makes some sort of sense.

    MTB.

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Thumbs up

    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...

    Thanks for your help, MTB! I really appreciate.
    Last edited by jimmyswinger; 12-16-04 at 14:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •