 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Same items being repeatedly shown
Hi, My company supplies clothing to different companies. With this particular company, I have their employee list. To create a bill for the last shipment, I have set up a report which filters out what their last shipment was. So, there could be 6 blue shirts for a certain category of 2 employees, while there could be 9 black shirts for another category of 3 employees. The whole bill is being generated fine, except for one problem. The bill is supposed to list: 1. Blue Shirts 6 (Rate) (Amount) 2. Black Shirts 9 (Rate) (Amount) What it actually does is; 1. Blue Shirts 6 (Rate) (Amount) 2. Blue Shirts 6 (Rate) (Amount) 3. Black Shirts 9 (Rate) (Amount) 4. Black Shirts 9 (Rate) (Amount) 5. Black Shirts 9 (Rate) (Amount) It is reprinting the record for the number of employees that the shipment went for. What could possibly be wrong? Thanks, Zef. Hi Zef, One possibility is that your report is based on a Cartesian Product query. In other words, a query that includes more than one table, where there is a join missing between one or more tables. How many records are being returned by the query? Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hi, > My company supplies clothing to different companies. With this [quoted text clipped - 20 lines] > Thanks, > Zef. Hi Tom, Yes, the query includes two tables. One for the list of employees and another for the description of items for each category of employees. In the previous example, the query would return 5 records. One for each employee. Now what do I do? Thanks, Zef. > Hi Zef, > [quoted text clipped - 32 lines] > > Thanks, > > Zef. Hi Zef, > In the previous example, the query would return 5 records. One for > each employee. Do you see a join line in query design view that connects fields in each table? If not, then you definately have a cartesian product result. Please post the SQL (Structured Query Language) statement for this query. Open the query in design view. Then click on View > SQL View. Please copy the SQL statement and post it into a reply. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hi Tom, > [quoted text clipped - 6 lines] > Thanks, > Zef. Hi Tom, I believer there is a join line. The following is the SQL view for you to confirm: SELECT CompleteList.*, OrderDetails.*, OrderDetails.ItemNum, CompleteList.Bill FROM CompleteList INNER JOIN OrderDetails ON CompleteList.[Category No]=OrderDetails.Category WHERE (((CompleteList.Bill)="001/2007")); The CompleteList is the table of employees and their details. The OrderDetails is the table of details of the order. Category and Category No are the link between the two. Each employee is allocated a uniform depending on what category they are in. The Bill is the Bill number which I am about to print. Any more questions? Thanks for the help. Zef. > Hi Zef, > [quoted text clipped - 22 lines] > > Thanks, > > Zef. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.