ODBC and MS Access 2000 as data sources - linking issues

[scottlacourse]

I am linking 2 ODBC tables (Providex MAS 90)with an MSAccess database.

The ODBC tables had inventory sales info so Part number,units sold, year. (Multiple records per part number)

The Access database has the part number, qty on hand at month end, and date. (Also mulitple records per part number)

I have created a report that shows sales info summarized by part for a particular month, and now I need to pull in the qty on hand from the Access database for each part.

I've got the tables linked on part number as that is the only common field.

I've created a formula for the qty on hand to seperate the date in the access database into year and month, then compare to my parameters:

if right(totext(year({Month End QOH.MonthEndingDate}),"0000&quot,4) = "2003" and
right(totext(month({Month End QOH.MonthEndingDate}),"00&quot,2) = {?Last Year Cutoff Month} then
{Month End QOH.QoH} else 0

I was "thinking" (that is probably my problem ) that I could use this formula in my detail and it would only return Month End QOH.QoH when year and month match, BUT
what I am seeing is that Crystal is only looking at the FIRST part number record in the Access database, and not subsequent records for the same part number. As a result I am getting all zeros returned, rather than my proper qoh value.

Any ideas?

 

[synapsevampire]

Try changing the month param to a numeric and use:

if year({Month End QOH.MonthEndingDate})= 2003 and
month({Month End QOH.MonthEndingDate}) = {?Last Year Cutoff Month} then
{Month End QOH.QoH} else 0

Now I generally do this differently anyway, I'd limit the rows being returned in from the Access database to only those of intereest in the record selection formula.

I create real dates from the parameter choices to ensure that I get SQL pass through and therefore might best take advantage of indexes.

And if I have disparate data sources and MS Access is ivolved, I link the other data source into the MS Access database, and then build the initial query in Access as it's engine is much faster than Crystals.

You could also create a SQL Expression to the Access database to have it return the year, and then join by that too...

Hope some of this helped.

-k

 

[scottlacourse]

Thanks K,

How would you limit the rows being returned from the access database? This is the first time I've had an access table linked to an ODBC report, so be gentle with me please....

Also - I am interested how I would create the sql expression in access to return the year. Would I create a new field in the access table and somehow populate it with an sql expression based on the full date field?

My lack of access knowledge is shining through!

Thanks a bunch for your help.