Multiple Table Join Issue

[tehbaker]

Hello!



First time posting, and the first thing I want to mention is I literally have almost zero knowledge in Access and SQL. I took an SQL class in college and It was relatively simple.

My Situation:
I work at a bank. There was a request to one of our employees for a report that listed accounts that were overdrawn from. The list would only include accounts that had overdrawn three or more months.

My issue:
With my very basic understanding of Access and using SQL, I have managed to come up some "code" to show accounts that appear in more than 1 month.

SELECT distinct January.Account, February.Account, March.account
FROM march
INNER
    JOIN (february INNER JOIN January
            ON february.account = January.Account) 
            ON march.account = January.Account;

I can not figure out how to do more than those three months.

This provides me with the accounts that appear in all of the listed months. If I do not use the distinct modifier, I run into an issue where , if an account number appears in January 7 times, that account number will carry over into April’s query for all 7 times, even though that account was only overdrawn 3 times in April.

The next issue I'm running into is, I know that once I start adding months April-December, it is only going to give me accounts that show up in every month, instead of 3 or more.

Any help on getting this to work right would be greatly appreciated. I will continue looking for resolutions myself.

Thank you!

-John

Edit:

SELECT DISTINCT January.Account, February.Account, March.Account, April.Account

FROM January, February, March, April
WHERE (((January.Account)=[February].[Account])
     AND ((February.Account)=[March].[Account]) 
     AND ((March.Account)=[April].[Account]));

Played around in design view, and that came up with this. Again, my issues falls into where, it's only showing accounts that are in every month, rather than 3 or more.

[weekend00]

How many table do you have? do you mean you need to change you query every month because more tables are added in every month?

had overdrawn three or more months: 3 continuous months or any three months?
do you need the account which was overdrawn more than 3 months sometime but now it's not overdrawn?

[tehbaker]

How many table do you have? do you mean you need to change you query every month because more tables are added in every month?

had overdrawn three or more months: 3 continuous months or any three months?
do you need the account which was overdrawn more than 3 months sometime but now it's not overdrawn?

-There's a table for every month, so 9 total right now.

-No, I won't need to change the query monthly. At least not at this point. The request was made for January-September. If needed, I could change the query if found necessary.

-Any three (or more) months.

-The list only shows overdrawn accounts. I hope this answers this question.

I've been poking at this all day! Thanks for your post

[weekend00]

Following is the solution:
1 put all records from 9 tables into one table named "AllAccount";
2 create query:
SELECT AllAccount.Account FROM AllAccount
GROUP BY AllAccount.Account, AllAccount.overdrawn
HAVING (((Count(AllAccount.Account))>=3) AND ((AllAccount.overdrawn)=True));

[tehbaker]

Following is the solution:
1 put all records from 9 tables into one table named "AllAccount";
2 create query:
SELECT AllAccount.Account FROM AllAccount
GROUP BY AllAccount.Account, AllAccount.overdrawn
HAVING (((Count(AllAccount.Account))>=3) AND ((AllAccount.overdrawn)=True));

Very good. I see how this would work. My only issue now is that, unfortunately, the tables provided do not match columns and rows. Thats something I'm going to have to manually alter.

Thank you. I will let you know how it goes when I get there. If you can think of anything else, have at it.

Thanks!


Edit:
I thought of an easier way to do this using excel counts and duplicate removals.. However, I will continue fooling around with this just for the sake of learning.