union SQL or VB script?

[jasminewhite]

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 single SQL is becoming too complex. My question is should I use UNION of SELECT statements or go to VB scripts. If I do use VB script, 1) can I generate reports directly without going through queries? 2) please give me detailed explanation as to generate and run VB script from ACCESS.

 

[pbaldy]

If you're looking at building SQL in code, the sample db in this tutorial shows a method.

http://www.baldyweb.com/BuildSQL.htm

 

[jasminewhite]

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 through queries? I'm sorry for sounding like a complete airhead, but this is all new to me. I've never programmed VB neither, but I think VB is easier than C++, so I think I can manage the VB script, but the steps before that is all new. Thanks for any directions.

 

[pbaldy]

1) http://www.baldyweb.com/FirstVBA.htm

You wouldn't want to "create" the report with VBA, but you could easily modify it's source. While it would be theoretically possible to create a report from scratch in code, it would be a lot of work. It's still not clear what your specific problem is, but generally (and if my guess is correct) I'd create a report based on a query that presented the data properly, and then use code to modify the source of that report based on your requirements.

 

[jasminewhite]

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 functions into access. Thank you again.

 

[pbaldy]

Well, you've still only said that your "requirements are getting complicated". I still don't really know what you're trying to accomplish. If it's more complicated filtering of the report, then I would first build a report that displayed the info, the create a form with code to apply the more complicated filtering to that report.

 

[jasminewhite]

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 far, I can do this without TOT rows. How do you put a TOT row when value of FIELD1 changed from 1.1 to 1.2?
my SQL: SELECT FIELD1, FIELD2 FROM TABLE1 GROUP BY FIELD1

 

[Alansidman]

Here is one way to have totals in your query

http://www.datapigtechnologies.com/flashfiles/Unionquery.html

Alan

 

[jasminewhite]

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 details refs then all the total rows at the end?

 

[Alansidman]

I don't use reports so you will have to test and play around with it. I cannot help you there. Virtually, all reports I need are exported to Excel and analyzed there. I do believe that there are some total functionality in reports, however.

suggest you go here to look at tutorials on reports.
http://www.datapigtechnologies.com/AccessMain.htm

Alan

 

[pbaldy]

Summing for a group or overall is very simple in a report. You would simply have:

=Sum(FieldName)

as the control source of a textbox. In a group footer it will sum the group (your field 1), in the report footer it will sum everything.

 

[jasminewhite]

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

 

[jasminewhite]

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 works, otherwise I have to mess with the report design, which I have no clues. Thank all that responded to this thread, now I know how to tackle this problem.