FYI Mr/Ms McDermott. sorry if i'm giving too much info and thank you
for replying to my query. hope this will make my problem clearer.
The term_cont table contains the contact records (with contact codes)
which I count per primary code (from stud_basic), per secondary code
(from stud_basic) and multiply with their relevant weights.
Hope this helps. TIA.
the report is based on the following queries
1. SELECT dis_codes_multi.dis_sort, dis_codes_multi.dis_code, [count
contact by primary code].cdeprim, Nz([Count contact by primary
code].[CountOfcont_dte],0) AS Pcnt, dis_codes_multi.wt_prim,
(([Pcnt])*([wt_prim])) AS pwt, [count contact by secondary code].cde2nd,
Nz([Count contact by secondary code].[CountOfcont_dte],0) AS Scnt,
dis_codes_multi.wt_sec, (([Scnt])*([wt_sec])) AS Swt
FROM dis_codes_multi LEFT JOIN ([count contact by primary code] LEFT
JOIN [count contact by secondary code] ON [count contact by primary
code].cdeprim = [count contact by secondary code].cde2nd) ON
dis_codes_multi.dis_code = [count contact by primary code].cdeprim
ORDER BY dis_codes_multi.dis_sort;
2. count contact by primary
SELECT stud_basic.cdeprim, Count(term_cont.cont_dte) AS CountOfcont_dte,
term_cont.dis_typecde
FROM stud_basic RIGHT JOIN term_cont ON stud_basic.studid =
term_cont.stud_id
GROUP BY stud_basic.cdeprim, term_cont.dis_typecde
HAVING (((stud_basic.cdeprim)<>"") AND ((term_cont.dis_typecde)="P"))
ORDER BY stud_basic.cdeprim;
3. count contact by secondary code
SELECT stud_basic.cde2nd, Count(term_cont.cont_dte) AS CountOfcont_dte,
term_cont.dis_typecde
FROM stud_basic RIGHT JOIN term_cont ON stud_basic.studid =
term_cont.stud_id
GROUP BY stud_basic.cde2nd, term_cont.dis_typecde
HAVING (((stud_basic.cde2nd)<>"") AND ((term_cont.dis_typecde)="S"));
and the following tables:
a. stud_basic
studid text
lastnam text
firstnam text
cdeprim text
strtpri date/time
endpri date/time
cde2nd text
strt2nd date/time
end2nd date/time
change_date date/time
stat_sw text
b. term_cont
stud_term text
stud_id text
cont_dte date/time
cont_cde text
dis_typecde text
-----------------------------------------------------------
From: mjobrien
Date Posted: 11/26/2004 9:33:00 PM
i created a query with the intention of creating a columnar (9 columns)
report with the 10th column as a horizontal total of the records. i
didn't think i needed a crosstab query because my first column is just
for row title constants identifying the rows on the report. the report
should look like this:
Hearing...Vision etc..to 9th Total
Primary Unweighted 9999 9999 ........... 999999
Scndary Unweighted 9999 9999 ........... 999999
Prim/Sec weighted 9999.99 9999.99........... 99999.99
the first column are merely constants identifying the row data. i
created a columnar report with nine columns and a query sorted the way
the column headings are set. the column headings are in the Page header
area. i set the left margin to 2 and the right on .5 with landscape
orientation to start the columns where they are on above
report. all the data for the three rows and 9 columns appeared where
they were supposed to appear. my first problem is the 10th column. i
created a calculated field with this control source
=Sum(Nz([Pcnt],0))---nothing appears in this column. Pcnt is the data
field for the columns of the first row and i'm trying to add up
horizontally.
The second problem is how do i display the first column constants in the
margin? is that possible? or is there another way to create this
report.
please reply with any idea. TIA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
