1. Registered User
Join Date
Apr 2004
Posts
66

I'm really hoping someone can help me. I'm a bit of an Excel wizard, but am still learning access. I'm struggling with a query that was easy to handle just as calculations in Excel, but isn't possible in Access. Here is the sql behind the query...

SELECT Debtor.SSN, Debtor.LastName, Debtor.FirstName, Check.Amount, Check.Interest, Check.RCF, [Amount]+[Interest]+[RCF] AS Prin, Check.AttorneyFee, Check.StoreID, Check.Track, CtInfo.Case, CtInfo.SFee, CtInfo.SFeePd, CtInfo.SPSFee, CtInfo.SPSFeePd, CtInfo.GFee, CtInfo.GFeePd, CtInfo.GPSFee, CtInfo.GarnPSFeePd, CtInfo.IPSFee, CtInfo.IPSFeePd, CtInfo.OFee, CtInfo.OFeePd, PCROSS.[1], PCROSS.[3], IIf([SFeePd]=1,Nz([SFee]),0)+IIf([GFeePd]=1,Nz([GFee]),0)+IIf([SPSFeePd]=1,Nz([SPSFee]),0)+IIf([GarnPSFeePd]=1,Nz([GPSFee]),0)+IIf([IPSFeePd]=1,Nz([IPSFee]),0)+IIf([OFeePd]=1,Nz([OFee]),0) AS Client, IIf([SFeePd]=2,Nz([SFee]),0)+IIf([GFeePd]=2,Nz([GFee]),0)+IIf([SPSFeePd]=2,Nz([SPSFee]),0)+IIf([GarnPSFeePd]=2,Nz([GPSFee]),0)+IIf([IPSFeePd]=2,Nz([IPSFee]),0)+IIf([OFeePd]=2,Nz([OFee]),0) AS AttyFee, IIf(Nz([3])=0,0,IIf((Nz([3])<(Nz([AttyFee]))),Nz([3]),(Nz([AttyFee])))) AS PAttyFee, IIf(Nz([3])=0,0,IIf((Nz([3])-Nz([PAttyFee]))<Nz([Client]),(Nz([3])-Nz([PAttyFee])),Nz([Client]))) AS PClient, IIf(Nz([3])=0,0,IIf(((Nz([3])-(Nz([PAttyFee])+Nz([PClient])))/2)<Nz([Prin]),((Nz([3])-(Nz([PAttyFee])+Nz([PClient])))/2),Nz([Prin]))) AS PPrin, IIf(Nz([3])=0,0,(Nz([3])-(Nz([PAttyFee])+Nz([PClient])+Nz([PPRIN])))) AS PATTY, IIf(Nz([1])=0,0,(IIf((Nz([1])<(Nz([AttyFee])-Nz([PAttyFee]))),Nz([1]),(Nz([AttyFee])-Nz([PAttyFee]))))) AS CAttyFee, IIf(Nz([1])=0,0,IIf((Nz([1])-Nz([CAttyFee]))<(Nz([Client])-Nz([PClient])),(Nz([1])-Nz([CAttyFee])),(Nz([Client])-Nz([PClient])))) AS CClient, IIf(Nz([1])=0,0,IIf(((Nz([1])-(Nz([CAttyFee])+Nz([CClient])))/2)<(Nz([Prin])-Nz([PPrin])),((Nz([1])-(Nz([CAttyFee])+Nz([CClient])))/2),Nz([Prin])-Nz([PPrin]))) AS CPrin, Nz([1])-(Nz([CAttyFee])+Nz([CClient])+Nz([Cprin])) AS New
FROM Debtor RIGHT JOIN (([Check] INNER JOIN PCROSS ON Check.TransactionID = PCROSS.TransactionID) INNER JOIN CtInfo ON Check.TransactionID = CtInfo.TransactionID) ON Debtor.DebtorInfoID = Check.DebtorInformationID;

I read that it's best to shorten all the names cause this error may be caused by too many characters...but that hasn't seemed to really help.

I can get the query to run as is, but it won't as soon as I add another field (either in the query or in the report for which this query is needed)...I am trying to get it to then add three of these newly calculated fields, but it won't seem to.

Any experts out there willing to help me out?

Thanks a ton!

Susan

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
BREAK it down

you can run Query that read other query

Table -> Query1 -> Query2 ->Query3 -> TheAnswer

3. Registered User
Join Date
Apr 2004
Posts
66
Tried that - wrote a query for the first part (pulling all the fields together), then ran equations in the second query with those fields...still get the exact same error at the same point...Am I missing the point here?

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
sh..t it must be complex LOL

Table ->Query1 ->MakeTable1 = newtable

Newtable -> Query2 -> Query3 -> Maketable2 = Newtable2

Newtable2 -> Query4 -> Report

Then Wrote a Macro

Setwarning off
run MakeTable1
run Maketable2
setwarning on
print Report

5. Registered User
Join Date
Apr 2004
Posts
66
First - I have to say - thank you so much for being willing to help me - I can't tell you how much I appreciate it.

And no...it's really not that complex...just a bunch of if statements! But...I have to do something different, so....

okay - I clearly missed the point of query to a query to a query - I didn't realize that I should do that make table thing...I'll try that (never done it before!!) and see if that works!

Thanks a ton!

Susan

6. Registered User
Join Date
Jan 2004
Posts
492
Wow - that code is very hard to read....

I know Access writes the code for you, but since you seem eager to learn, I'll give you some invaluable advice. Though as a disclaimer, it seems that Access loves to overwrite the SQL you write with their own, so save often!

This is a sample of how Access writes code for you. They always use full table names everywhere. They also allow for spaces in table and table names, as long as you put the name in brackets [ ]. My advice to you (and it looks like you are already doing this) is to either make all the columns/tables one word, or use an underscore. The less brackets and parentheses in your query, the easier it is to read. SQL really does not need parentheses around most statements for it to work.

Code:
```Select full_table_name1.col1, full_table_name2.col1
from full_table_name1, full_table_name2
where full_table_name1.id = full_table_name2.id```
Instead, you can use aliases. An alias represents the table name, and can be used throughout the entire query. Here is that same query using aliases.

Code:
```Select f1.col1, f2.col1
from full_table_name1 f1, full_table_name2 f2
where f1.id = f2.id```
Simply put the alias after the table name with a space (no commas). This will tell SQL that you are going to refer to the table using that alias. When you are selecting many columns as you are in your query, this will cut down the amount of code you have yet does the exact same thing.

Also note that I dont use the inner join syntax, but the join in the where statement is the same concept. For me, its a lot less coding - I dont see the point in adding any unnecessary junk in my queries....but that is a matter of my own preference.

Hope this gives you some ideas!

7. Registered User
Join Date
Apr 2004
Posts
66
Thanks for the pointers - I am very eager to learn and frustrated by the lack of good coding tips in books. I'll try using alias too!

Susan

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Originally posted by mommyof4
First - I have to say - thank you so much for being willing to help me - I can't tell you how much I appreciate it.

And no...it's really not that complex...just a bunch of if statements! But...I have to do something different, so....

okay - I clearly missed the point of query to a query to a query - I didn't realize that I should do that make table thing...I'll try that (never done it before!!) and see if that works!

Thanks a ton!

Susan
hay that "I didn't realize that I should do that make table thing..."
it what I would do who say thats right as long as the print out are printed

What I've done in the pass

eg wrote a database to selected events in our work place

got it talking to our payroll system got it to query and do a maketable of hours (good its in access can play with it know)

then i could put hour/payroll => to a Event that happen on site base on that emp bla bla

i broke the problem down into small query / sometime had to do a maketable then query them again get answer

then just wrote a macro to run the Queries in the right order and then
print the .... report

i've joined

AS400 data =>access
ourpayroll => access

so there I got As400 => Ourpayroll

just have to get the joins right

9. Registered User
Join Date
Oct 2003
Posts
706
I feel that this query is simply ... incomprehensible.

You just can't really do queries that have iif() function-calls like that. Not do so and ever hope to maintain what you did.

I strongly advise you to rewrite the query, using more than one query (perhaps a macro?) and design it from the outset to be maintainable. Which simply means that you have a ghost of a chance of understanding it tomorrow.

Understand, please, that I'm not saying anything personal whatsoever about you or anyone: it's simply strong advice. You can box yourself into "maintenance hell."

10. Registered User
Join Date
Apr 2004
Posts
66
Hello all,

Thank you to everyone who responded so quickly. I am grateful for all the advice (and no, I don't take it personnally at all - I know I have a lot to learn and am lucky to have people willing to share their thoughts!)

I was able to make this all work by taking StePHan's advice - much, much thanks for clarifying it all for me!

I'm also using alias's now, and thinking more about maintence!

With much thanks!

Susan

#### Posting Permissions

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