On 4 Jun 1997 02:24:13 GMT, "Eric Bonney"  wrote:

>This may be a very basic question but I can't figure this out. :-)  I have
>a database with 4 or 5 tables in it.. I forget.. but anyway.. I want to
>create some reports but I don't want to use Crystal reports b/c I can't
>format the report as I want it.  What I need to be able to do is do both a
>print preview and a regular print of the report.
>
>Here is the format of the report I would like, if I can do this in Crystal
>reports can someone explain to me how?
>
>Product Main Category
>    Product Sub Category
>            Product Id	Product Name		Qty on Hand
>
>	This would change whenever either the Sub Category or the Main Category
>changed.  So I want to only print the Category names once and then the
>Product Names.  I would prefer to do this in crystal reports as it is much
>faster but I just can't figure it out.
You can do it in VB code (I prefer that; I don't really like Crystal Reports)

It depends on the structure of your database and tables AND the SQL you are using. 
It's not very difficult:

Suppose all your items are in one table in one record.

Product Main Category = PMC (field 0)
Product Sub Category = PSC (field 1)
Product Id = PI (field 2)
Product Name = PN (field 3)
Qty on Hand = QH (field 4)

Then your SQL looks like:

Dim SQL$
Dim db as DataBase
Dim rs as recordset
Dim dummy1, dummy2

Set db = OpenDatabase([yourdatabasename])
SQL$ = "SELECT * FROM " & [yourtablename]
Set rs = db.OpenRecordset(SQL$)
dummy1 = ""
dummy2 = ""
Do While Not rs.EOF
	If rs.Fields(0).Value <> dummy1 Then
		'print PMC once
		Printer.Print rs.Fields(0).Value
		dummy1 = rs.Fields(0).Value
	End If
	If rs.Fields(1).Value <> dummy2 Then
		'print PSC once
		Printer.Print Tab(10); rs.Fields(1).Value
		dummy2 = rs.Fields(1).Value
	End If
	'print PI, PN, QH
	Printer.Print Tab(15); rs.Fields(2).Value; _
		Tab(45); rs.Fields(3).Value; _
		Tab(75); rs.Fields(4).Value;

	rs.Movenext
Loop
Printer.EndDoc
rs.Close
db.Close

So if the items are NOT in the same table you have to change the SQL$ so that 
the result is always a overview of the contents of all the tables.
Return