Recent content by jasminewhite

To all SQL experts out there, How do I implement the following logic: Query1 (no filter) For each value of FIELD1: if FIELD2 = FY11 then count FIELD3 as FIELD3count. Note that DB is sorted by FIELD1. For a group of FIELD1 records that does not have any FY11, FIELD3count will be 0 for that...

My DB table: FIELD1 FIELD2 other fields 1 FY11 other-values 1 FY12 other-value 2 FY12 other-value 3 FY11 other-value display results: FIELD1 FIELD2 other fields 1 FY11 values 2 FY11 N/A 3 FY11 values for the records that don't satisfy filter condition, display its FIELD1 & FIELD2 value, but...

Ultimately, I want to pass through the entire DB once and produce a set of outputs broken into 3 parts, instead of writing 3 SQL that pass through the DB 3 times to produce 3 separate sets of results. MyTABLE: GROUPfield FYfield VALfield (whole #) 1 3QFY11 1 1 4QFY11 2 1 1QFY12 1 1 2QFY12 1 1...

I include a SORT clause in query1 to sort by 1st field, the union query (not UNION ALL) produces details of group1, followed by TOTgroup1, then details of group2, followed by TOTgroup2. The repoprt generated from this query also preserves the sorting order listed above. Thank goodness this...

Alan, when I use UNION, it works, but UNION ALL messed up the sorting order of field1?

Thank you, Alan. It will do what I need. Now, when I run your q3 and manually click on REF field to sort it, and save it. Then have report wizard create a report based on your q3, named r3. Will r3 show all ALEX ref, then ALEX total, then next refs, so on? Or will the report show all...

I'm not familiar w/ SQL, maybe that's why it's complicated. Here it is in abstract form of the desired output: FIELD1 FIELD2 1.1 2 1.1 3 TOT 1.1 5 1.2 1 1.2 2 TOT 1.2 3 grandTOT 8 so...

Let me summarize my understanding from your suggestion: To give me more flexibilitiy, I could use VB functions to create a query, then use report wizard to create report based on that query? Do I understand you correctly? Please point me to the help section to read more regarding linking VB...

Thank you pbaldy for the tips, that's step 2: writing VB script. I need step1, which is how to:1) link this public VB function to my Access DB 2) run it to produce my report. Specifically, do I open the DB first, then what's next? Can VB functions create Access reports directly without going...

I'm a C++ programmer, this is my 1st ever Access project. I feel my options are limited in Access. What I need to do is to generate a report from 1 Access table. What I've been doing is create queries, then create reports based on the queries. The requirements are getting complicated so a...

there is no WHERE clause for the SELECT statement, it pulls in all records. my countNULL column: count([field] & "".... counts NULL, my countRED column: count ([field]="Red"..... counts RED. so it finally works. Thanks for all your help, appreciate it. BTW, how do I close a thread as RESOLVED?

yes, there is no filter (no where clause), just count each type of record

yes, I did. I'm sorry, I forgot to mention counting for NULL works. But I also need to count for NULL along with other value such as "red", "green" as well.

My DB table field has the following data: NULL, 0-len text, "No Stat", "red", "green". My query counts these records & report the count in 2 columns: 1) NULL, 0-len txt or "No Stat" 2) "red" or "green. Thank you for any help that I can get. Another question, do I need SELECT DISTINCT or just...

field1 has both NULL and other text. However, your code only count the records with other text, the count for NULL records is 0. Please help, I've been looking at this count function for too long, come to a dead end.