It sounds to me like you're trying to do too much. Show the tables that you are using, and we will definitely help you. I believe you only need one aggregate query, most likely directly from the table, where you group by the FamilyHousehold and total the Fee field. Access is more than powerful enough to do what you want to do.

Hi Sami, there are 8 tables I am using to support the database and queries. I think this is the cause of my problems. I have start and end dates to determine the total fees due. And then two different types of fees. For example, The Smith Houshold has two foster children staying with them but each one for different lengths of time * the length of days * two different applied fees. It's complicated - too complicated so far for me to build in Access.

I am modeling from an Excel spreadsheet, which is the current "database" being used. I will upload the generic Excel file tomorrow (using fake names for HIPPA compliance) to determine if Access is the right tool or should I just stick to the Excel file, which I am leaning towards at the moment.

Thanks.

Hi David,

We are happy to assist you. In 99% of cases, Access will be the better solution. It is often the case that our knowledge and comfort with Excel clouds our ability to design a proper Access database. I am speaking from experience. I struggled in migrating my main system from Excel to Access. The other moderators at the time helped me reconceive my database.

Is this for a non profit?

Yeah let's see some sample data. I'm sure we can figure this out for you.

And remember... you'll usually do your filtering (criteria) FIRST and then take those results and aggregate them. That's the case 90% of the time.

Hi Sami, Richard.

The first image is the exported Excel file from my Query. The second image is the sum for each Foster Parent requested by my accounting department. I copied the exported file and then manually summed each Foster Parent for accounting to run the budget and send checks with the proper amount.

I have five tables. 1) Fee Tracker T. 2) Foster Family (Parent) T. 3) Foster Fee Rates T. 4) Staff T. 5) Youth Allowance T. 6) Youth T.

I told accounting that I can't create a query to sum each Foster Parent payables. Not sure if Access can do this, hence my relying on Excel to generate the final report.

Let me know what you think.

Thanks

Correction, 6 tables....

David, is this information confidential?

If so, we need to remove the images.

Please advise!

Look at the names, Sami. And you call yourself a Trekkie? :)

The joke on me. Did not even read the names.
Just saw the word 'Foster parents' and I panicked.

Hi Sami,

No confidential information here. I used TV character names including those from Star Trek. Yes, Richard. I am a huge Trekkie!

I am now worried that Richard might take my Trekkie Badge.

Haha, Sami. I might. :)

David: why have you not taken the Trekkie Quiz yet to get your badge?

As far as actually answering your question goes... it's late and I'm beat. I'll try to look at it tomorrow for you.

I found the chink in the armor, so to speak concerning my issue here. I turns out that I cannot use the Primary and Foreign keys when using the Totals button. I cannot apply the Totals (Group by, Count and other functions) to the ID fields and therefore, I cannot relate multiple queries to aggregate data without the ID fields. I think VBA is needed to produce the results I am attempting. Can anyone direct me to a resource for VBA code?

Thanks.

What do your tables look like?

Richard, I'm thinking I have too many tables here.

Yeah, help me to understand what these different tables mean. Why is there, for example, a separate table for Youth Allowance?

Hi, Youth Allowance is a separate accounting line item that may or may not be applicable. So, I created a separate table to pull from in the Form. Does that make sense?

I'm still very confused. Let's break this down into simple steps. What is the first thing that you want to be able to accomplish? Let's not try to kill all of the birds with one stone.

Hi Richard, I want to total the Total Fee plus the Total Allowance for each Foster Parent showing the Total sum for each Foster Parent.

EG. Riker Parents. [Total Fee: $1,085] + [Total Allowance: $93} = Total: $1,178.00
      Dax Parents.   [Total Fee: $1,085] + [Total Allowance: $0} = Total: $1,085

Basically, a line for each Foster Parent with Totals

I hope this makes sense.

The "Total Fee" and the "Total Allowance" are both on the FeeDayTrackerT. Total Fee comes from FosterFeeRateID and "Total Allowance" comes from YouthAllowanceID. Is this correct?

Hi Sami, Yes. "Total Fee" is pulled from the YouthAllowanceT and the FosterFeeRatesT into the FeeDayTrackerT using the Foster FosterFeeRateID and the YouthAllowanceID to relate.

So step one.
Build a query with FeeDayTrackerT, YouthAllowanceT, and the FosterFeeRatesT ' they should link correctly automatically using the IDs.
Select * from FeeDayTrackerT and only the amounts from the other 2 tables.

Step 2 feed this new query into a new aggregate query where you total on Parents.

Thanks, Sami,

This did the trick! I was close to figuring this out but didn't consider using the Select * from FeeDayTrackerT.

I really appreciate your help!

David, you are very welcome.