Very strange report problem

[jimgillo]

I have an old access2000 application running on pc with windows xp, this application
connects to an SQL db on windows server 2003.
The application creates shipping documents for textile goods.
in the last months we heve noticed a really strange problem, happening not very often but
very serious:
from a form we select some data and we fill a temporary table, then we launch a report that
reads from the form some data and other data from a query on the temporary table.
Sometimes the query seems not working because the report printout has correct data read from
the form but the data from the query are those of the last preceding successful query.
Checking the temporary table the data are correct.
herewith enclosed find an example of report and form.
In the report you see red circled data read directly from the form are always correct. The


green circled data are obtained from the simple following query and sometimes give the
problem.
Query: "SELECT Bollatex.* FROM Bollatex INNER JOIN Pezza ON Bollatex.CODPEZ = Pezza.CODPEZ
WHERE (((Pezza.NUMBOL)=[forms]![BOLLA_S]![NUMBOL]) AND
((Year([Pezza].[DATBOL]))=[forms]![BOLLA_S]![YEARBOLL]));".
The application is runnig from 8 years, we noticed the problem last months, the
only change was the substitution ( end 2010) of the pc with a new model but same operating
system (XP) same access 2000 version with SP3.
I'm desperate because this problem mix data of different documents
Please help me

[rpeare]

the Report.PDF document doesn't have any green marks that I can see, it has five circled items on the top (header) of the report and six at the bottom (footer) of the report and they all appear to be red/black to me.

Is there any way you can provide a sample of your database that can duplicate the error? just make a copy of your database, take out any privacy information and replace it with garbage data, just enough to replicate the problem. For your linked SQL tables you can make local tables instead with the same structure.

[jimgillo]

rpeare Thanks very much for yr interest
unfortunately i can't duplicate the error because it happens 1-2 times every month . Another detail of the problem is that if the application asks for more than 1 copy of the report, the error always occur (when happens) only on the first copy.
I enclose a view of the part of the report giving the problem and a part of the application where you can see the structure of the report, if this can help you to investigate the problem

[rpeare]

The reports actually don't mean anything to me I'd need to see the structure of your tables to figure it out which is why I asked you to make a sample database with junk data.

What I suspect though, is whatever you are using for your linking criteria is not a unique key and what's happening is that when you link your data you are getting 2 (or possibly 3 in the future) results instead of a single result. I would look at all of your tables NOT the report and see if there are multiple values for the same field that you're using for your link criteria. Without a sample of the database that is duplicating the error I can't say more though.

[jimgillo]

In reportddt.mdb you can also see the table bollatex with some sample data , this is the table used from the query of the report, this table is recreated every time I prepare a report and before printing the application check the data are correct, it is impossible that problem is due to not a unique key.
What i suppose is a problem of microsoft access itself, some bad working or incompatibility with the pc
thanks again for your support

[rpeare]

The problem is not with access it's with how you have your linking criteria set. Because your original tables are linked though it may be hard to create a sample database that can duplicate the problem especially if you are using an auto incrementing field within SQL as a unique key.

"SELECT Bollatex.* FROM Bollatex INNER JOIN Pezza ON Bollatex.CODPEZ = Pezza.CODPEZ
WHERE (((Pezza.NUMBOL)=[forms]![BOLLA_S]![NUMBOL]) AND
((Year([Pezza].[DATBOL]))=[forms]![BOLLA_S]![YEARBOLL]));".

this is your SQL code

There is no way of telling from it if you:

a. have multiple records in pezza for each record in bollatex
b. have multiple records in bollatex for each record in pezza
c. have one record in pezza for each record in bollatex

Because of the problem you are encountering it *has* to be either a or b and you are not limiting your data enough to ONLY show the record you want.

Secondly look at the NUMBOL field in either PEZZA or BOLLATEX depending on your one to many relationship and see if the NUMBOL is repeated for the same CODPEZ on either table. If it is you will also get multiple records displayed.

[jimgillo]

Dear rpeare, probably i was not clear explaining my problem
I found a sample of the error, see enclose.
you can note "exact report" document n.987 with exact data, "wrong report" document n.988 with rows of 987 (the report i had printed just before).
I have not case "a" neither case "b", i have no records in tables where NUMBOL is repeated for same CODPEZ

[rpeare]

you can link your reports all day, but it still tells me nothing about the data structure. Without an example I am shooting in the dark. If you can't provide a sample database (as mentioned before) that can duplicate the problem I don't think I can help you any further. The reports tell me absolutely nothing about your table structure or query design which is probably the problem.