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