Query to find Current AR

[notanexpert]

Name: access can jump off a bridge.jpg  Views: 16  Size: 99.5 KB  ID: 32561" class="thumbnail" style="float:CONFIG" />
I am having a really hard time figuring this out and please excuse me because I'm self/Google taught. lol

I need to figure out Current AR by adding the sums of Member Charges & Misc Charges and subtracting the Payments but separate for each Member ID.

There are some members who do not have Misc Charges so I need the Nz function in the equation but for the life of me I cannot figure this one out.

Also, when I Sum the Misc Charges, each Member gets an incorrect value. If I Group By it gives them the right numbers. Theoretically there should never be more than one Misc Charge for each Member ID.

Thank you and please reply as though you are teaching a kindergartener.

[ranman256]

you need outer joins.
some may not have payments, some no misc charges.

dbl-click the join line ,
select OUTER JOIN, ALL RECORDS in MEMBERS, some records in the sub table.

on all subtables.

[ssanfu]

Welcome to the forum.


I would recommend removing ALL SPACES in object names. This includes field, table, form, report and query names.

I would recommend combining tables "ChargesMember" & "ChargesMiscellaneous".
Consider:

tblCharges
-----------
ChargeID_PK (Autonumber)
MemberID_FK (Number - Long)
ChargeItem (Text) (ex. Membership, Charge 1, ... don't know what the other Miscellaneous charges might be ...)
ChargeDate (Date/Time)
ChargeAmount (Number - Double)


Would be so much easier to calculate charges.........



And why can "access can jump off a bridge"????

[notanexpert]

I've done the outer joins, but you can explain why and what it will do? Again...kindergartener level.
And thank you.

Will removing the spaces in my field names make a difference when I can select them in builder?

[ssanfu]

I'm guessing that this is directed to me

Will removing the spaces in my field names make a difference when I can select them in builder?

Don't know.
I never use "builder". Having spaces in object names is just poor programming practice (IMHO) and makes a lot more typing. Can cause errors because you forget to add a space or add a space where it shouldn't be.

And if this dB ever gets converted to SQL Server Express, SQL Server, MySQL, etc, you will end up doing a rewrite because of the spaces.....

[notanexpert]

I'm guessing that this is directed to me

Don't know.
I never use "builder". Having spaces in object names is just poor programming practice (IMHO) and makes a lot more typing. Can cause errors because you forget to add a space or add a space where it shouldn't be.

And if this dB ever gets converted to SQL Server Express, SQL Server, MySQL, etc, you will end up doing a rewrite because of the spaces.....

Any thoughts on how the formula should be written? I will happily take all the spaces out. lol

Also...it can jump off a bridge because I've been trying to figure this out for over 2 weeks and it's making me grumpy. Also naming my jpeg that made it really easy to find to attach.

[notanexpert]

Yippppeeeee!!!!

I had no idea what an outer join was so I googled it to see if I did it right. And I didn't. That's beside the point.
This. https://support.office.com/en-us/art...1-07061a1478f6

I did a separate query to find the sum of each table with a left outer join in each. Then whammo! It worked when I put all three baby queries into the new one and my simple math equation worked.

Thank you for basically telling me what to google! I'll be smiling all weekend after this success!