help with query

[farid]

hi when i try this query system take a long time and i'll not get any answer from the system what is wrong with my query please help me
SELECT AN, count(*) AS total1, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010198" And "311298")) AS total98, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010199" And "311299")) AS total99, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010100" And "311200")) AS total00, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010101" And "311201")) AS total01, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010102" And "311202")) AS total02, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010103" And "311203")) AS total03, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010104" And "311204")) AS total04, sum(abs([anresien]>1) And ((Format([AN],"mmddyy")) Between "010105" And "310105")) AS total05
FROM GAUF, GAESTEST
WHERE (MEMO Is Null And ZIMMER<>"cxl")
GROUP BY an;


thanks

 

[namliam]

You have what is called a cartasian (not sure about the spelling) join.

This means you are not joining your tables which will result in every record of table GAUF beeing matched with a record of table GAESTEST. This will result in, if GAUF has 10 records and GAESTEST has 100 records, 10 * 100 = 1000 records. You can imanging if there are 1000 and 1000 that there is a lot to do...

Join the tables properly....

Regards

P.S. I have my doubts about your sums, your summing logical values?

PPS also you are grouping on AN and working with it.... strange...

 

[Jon K]

Besides, to use Between ... And properly, [AN] must be compared in "yyyymmdd" i.e.
Format([AN],"yyyymmdd") Between "19980101" And "19981231", etc.


Since what you want is [AN] of a whole year, you can use a simpler expression:-
Year([AN])=1998, etc.

The latter expression can run much faster than the former.
.