Thank you all for the replies. I Initially looked at NaviCat for database administration, and for that it is good. I also converted my Seed Database from RealsqlDB to Sqlite 3.x. I did so in order to be able to use other tools via ODBC, ergo Navicat, it was within my price range and had a banded report writer in the Enterprise edition. I’ve been use to products such as Crystal, and Oracle Reports (showing my age here) and they did the job. With the specific problem I’m having now. The product allows you to create a VIEW. Then I base a new report on that View. My view definition is as such:
SELECT
SeedInventory.ContainerId,
SeedInventory.SubContainerId,
SeedInventory.Location,
(select PlantName from SeedInventory si2 where ContainerId=SeedInventory.SubContainerId) plantname ,
(select PlantVariety from SeedInventory si2 where ContainerId=SeedInventory.SubcontainerId) PlantVariety,
strftime(’%Y’,SeedInventory.PurchasedDate) as “Year”,
strftime(’%m’,SeedInventory.PurchasedDate) as “Month”,
CASE
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘01’ THEN ‘JAN’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘02’ THEN ‘FEB’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘03’ THEN ‘MAR’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘04’ THEN ‘APR’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘05’ THEN ‘MAY’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘06’ THEN ‘JUN’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘07’ THEN ‘JUL’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘08’ THEN ‘AUG’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘09’ THEN ‘SEP’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘10’ THEN ‘OCT’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘11’ THEN ‘NOV’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘12’ THEN ‘DEC’
END AS full_month,
strftime(’%d’,SeedInventory.PurchasedDate) as “Day”,
SeedInventory.Notes
FROM
SeedInventory
WHERE SeedInventory.SubContainerId <> “”
But when I generate the report and add a data source via the Query Wizard it generates this:
SELECT “ViewONView”.“ContainerId”,
“ViewONView”.“Day” Day_2,
“ViewONView”.“full_month”,
“ViewONView”.“Location”,
“ViewONView”.“Month” Month_2,
“ViewONView”.“Notes”,
“ViewONView”.“plantname”,
“ViewONView”.“PlantVariety”,
“ViewONView”.“SubContainerId”,
“ViewONView”.“Year” Year_2
FROM “main”.“ViewONView” “ViewONView”
Note it sticks “main.” in front of the view. When I preview the data, some data in the columns is missing, ie null, when I “Edit the Sql” in that window and take out the “main.” and save, it works. But then the toolbar icons are not usable. I need that toolbar to set up search parameters. Because then I can search for specific info when I use the Navicat Report View. If anyone wants my DB, to test, I can email. Its just my vast seed collection (Big gardener I am)…Thanks all