Same items being repeatedly shown

[huzefahashim]

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.

 

[Guest]

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
__________________________________________

 

[huzefahashim]

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.

 

[Guest]

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
__________________________________________

 

[huzefahashim]

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.