ASP/SQL Query Build - Myth Breaking

[Laphan]

Hi Guys

I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:

1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.

Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.

Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.

The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.

I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.

2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?

I really appreciate your help on this.

Regards

Laphan

[Jeff Cochran]

>2) Are there any good web sites that explain in kiddie form how to do this

>sort of thing?

sqlcourse.com

Jeff

[Bob Barrows]

Laphan wrote:

> Hi Guys
>
> I don't want to keep asking for your help all the time on each
> individual query, so could you please help me to break the myths on
> the following:

"myth"? I do not think that word means what you think it means ... ;-)
<blatant Princess Bride ripoff>

>
> 1) I have 2 tables. Once is called ACCOUNTS and the other
> ACCOUNTBUDGET. ACCOUNTS includes all of the usual details for
> customers and suppliers, eg the account code (as ACCOUNTID), the
> account name (as NAME), etc. ACCOUNTBUDGET basically holds a
> transaction line for each month and it's corresponding year to store
> the turnover for that month, eg one row contains ACCOUNTID, PERIOD
> (ie the month), YEAR and TURNOVER.
>
> Now a lot of the SQL 6.5 tables that I deal with are in this vein and
> the usual query is that I want to list all of the ACCOUNTIDs and
> NAMEs that exist in the ACCOUNTS table and then show for example what
> their TURNOVER is for a applicable PERIOD and YEAR, which are all
> held in the ACCOUNTSBUDGET table.
>
> Now if I do a quick query using MS Query all I get are rows that have
> related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I
> have specified say a certain PERIOD and YEAR.
>
> The main point of my current reporting problem is that I want to show
> all the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
> particular PERIOD and YEAR.

As usual, a narrative explanation leaves me completely baffled. Show us a
few sample rows of data from your table(s) (in tabular format if you must,
but insert scripts would be preferable - we do NOT need to see the data for
every column in your tables: boil it down to the minimum number of columns
needed to explain your requirements.) followed by the results you wish your
query to return given that sample data (again, in tabular format).

Then, if needed, explain the numbers you show in the results.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Astra]

Dear All

Many thanks for your valued feedback.

To try and explain myself, I have listed my current query as follows:

SELECT ACCOUNTS.ACCOUNTID, ACCOUNTS.NAME, CONTACTDETAILS.TELEPHONE,
ACCOUNTSBUDGET.TURNOVER

(I think the above is standard faire, but please let me know if it doesn't
make sense)

FROM ACCOUNTS, ACCOUNTSBUDGET, CONTACTDETAILS, PEOPLE

(ditto)

WHERE PEOPLE.PEOPLEID = ACCOUNTS.PEOPLEID AND CONTACTDETAILS.PEOPLEID =
PEOPLE.PEOPLEID AND ACCOUNTSBUDGET.ACCOUNTID = ACCOUNTS.ACCOUNTID AND
((ACCOUNTS.LEDGER=1) AND (ACCOUNTSBUDGET.YEAR=2002) AND
(ACCOUNTSBUDGET.PERIOD=6) AND (CONTACTDETAILS.SALESACCOUNTSCONTACT=1))

The 'ACCOUNTS.LEDGER=1' part simply filters customers from suppliers in the
ACCOUNTS table, ie customers = 1 and suppliers = 2.

The 'ACCOUNTSBUDGET.YEAR=2002' part is a Year filter, which I will make
variable later on so that I can let the user pick a specific year. I just
left it to 2002 to check that the query worked.

The 'ACCOUNTSBUDGET.PERIOD=6' part is a Month filter, which I will make
variable later on so that I can let the user pick a specific Month. I just
left it to 6 (which is June) to check that the query worked.

The 'CONTACTDETAILS.SALESACCOUNTSCONTACT=1' part simply filters account
contacts from other types in the CONTACTDETAILS table, ie
SALESACCOUNTSCONTACT=1 is a sales accounts contact and
SALESACCOUNTSCONTACT=0 isn't.

I want to list all of the ACCOUNTIDs and NAMEs in the ACCOUNTS table and
TELEPHONE in the CONTACTDETAILS where they either have a TURNOVER of zero in
the ACCOUNTSBUDGET table for the PERIOD and YEAR that the user specifies or
they don't have a value at all in the ACCOUNTSBUDGET table for the PERIOD
and YEAR that the user specifies, as this record 'no show' denotes that they
haven't any turnover either.

I'm sure its a join that I'm missing, as I'm trying to do 2 sorts of queries
in one - right, but I just can't get my head round how this is done.

Any ideas?

Rgds

Robbie


"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:402e140f.294749447@msnews.microsoft.com...

>2) Are there any good web sites that explain in kiddie form how to do this
>sort of thing?

sqlcourse.com

Jeff