Look UP List Box Not Unique in Report 1

[accessnut]

I have set up a table with the Customers field pulling from a "list" to make input of info easier for the user. I then set up individual queries to pull for each salesperson and each product we sell. From the queries I used the report wizard to do reports. All worked fine on the original computer. Then I emailed it to the user. When the user keyed in real data and tried to use the report it pulled more than just the items the salesperson sold. It pulled other Customers as having been sold to also and put in prices.
Example- Renee only sold Customer "Mrs. White" a clock for $100.
It returns Mrs. White-clock at $100 and
Mr. Black-watch at $50 and...
Is it because I used a Look Up in the table? Or would it be from emailing it to the user. I can't tie up that persons computer for hours re-building the database. Help!

 

[Steve101]

The problem is caused neither by the emailing process (perish the thought), or the user's computer. Its caused I think by the mix of data that the user has entered, and the fact that in your application, you did not constrain your queries sufficiently. This never surfaced earlier because the data that you used for testing probably was'nt sufficient to bring out the problem.

Its difficult to provide further diagnosis without actually having the schematic and query details. However, I suggest that you ask the user to send back a copy of the their data, then run it on your computer to prove to yourself that it occurs independent of operating environment, then look carefully at the queries relating to forms, reports recordsources and internal controls, to ensure that they are constrained appropriately.
Good luck
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[accessnut]

Thank you, I thought it might be the user because we had some trouble getting in onto the users computer. Our email at work has some type of securtiy on it that does not allow .mbd to be received via email. I had to change it to a .doc to email it and I thought this might have changed it somehow.
I think I have it constrained enough, this is how I set it up...
tbl-Salesperson (is lookup only)
tbl-Customer (is lookup only)
tbl-Product (is lookup only)
tbl-Special=Salesperson,Customer,Product,Size,Color,Price,Price for Color.
Each underlying query for each report has criteria of "Salesperson" and "Product" and it was working fine. But when she input her data it didn't. I'm really baffled and after creating 76 different queries and reports I'm also a bit frustrated. Any help more help would be greatly appreciated!!! Thanks.

 

[Steve101]

OK. Publish the SQL for one of the queries thats producing the wrong results, and I'll take a look at it. Also indicate where the results are wrong please. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[accessnut]

SELECT [Special Sections].[Special Sections], [Special Sections].[Ad Rep], [Special Sections].Customer, [Special Sections].[Acct No], [Special Sections].Col, [Special Sections].[In], [Col]*[In] AS Expr2, [Special Sections].Color, [Special Sections]., [Special Sections].Price, [Price]+ AS Expr1
FROM [Special Sections]
WHERE ((([Special Sections].[Special Sections])="Foot Ball&quot AND (([Special Sections].[Ad Rep])="Angela Kincaid&quot);

When the report is run from this query it returns more than what Angela has sold. It shows the two customers she has sold plus other random customers from the "customer field" with prices. I'm sorry if I sound aggitated, but I feel like I'm going a little crazy. I'm sure there is something simple I am missing. I have even gone back to the Special Section table and changed the property for the Customer field from "lookup" to just text. It still pulls random customers into it...Thanks so much for helping, I am new to using a forum and a little in awe of everyone here. Thanks again!

 

[Steve101]

Nothing obvious above; I suspected that you may have been joining two or more tables, but not constraining the joins correctly; thats not the case in the above SQL. If you mail me a copy of the database (compacted and zipped if possible), I'll take a look if you like.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au