Displaying unique records

[yk1]

Hello,
I've tried many ways to solve my problem but nothing works.
I have two tables:

Customer Table with CustID(PK), Last, First, Route
Meals Table with CustID(PK), MealType, MonAM, TueAM, WedAM and so on for the week

For example:
Customer Table:
001 John White 1(Route Number)
002 Mary Jones 2
003 Kim Lee 1

Meals:
001 Hot 1 1 2 1 (The number of meals for particular day MonAM, MonPM and so on)
001 Frozen 1 1 2 2
002 Hot 2 2 1 1

I need to create a report where it will display customers who need to recieve meals on particular day. But only display them once even if the customer has more than one meal type.
I have this:

SELECT [Customer].CustID, [Customer].Route, [Customer].Last, [Customer].First, [Meals].MonAM, [Meals].TueAM, [Meals].WedAM ....
FROM [Customer] INNER JOIN [Meals] ON [Customer].[CustID]=[Meals].[CustID]

WHERE ((([Customer].[Route])>0) And (([Meals].MonAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Monday"))
Or ((([Customer].[Rte No])>0) And (([Meals].TueAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Tuesday"))
Or ((([Customer].[Rte No])>0) And (([Meals].WedAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Wednesday"))
Or ((([Customer].[Rte No])>0) And (([Meals].ThurAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Thursday"))
Or ((([Customer].[Rte No])>0) And (([Meals].FriAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Friday"))
Or ((([Customer].[Rte No])>0) And (([Meals].SatAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Saturday"))
Or ((([Customer].[Rte No])>0) And (([Meals Count].SunAM)>0) And ((Forms![Delivery AM Dialog]!dayOfWeek)="Sunday"))
ORDER BY [Customer].Route, [Customer].Last, [Customer].First;

In the report it displays John White twice, because he has two mealtypes in meals table.
If I use DISTINCT it gives me the same result. Could someone please help me with this. What else can I use instead of distinct.
Thank you in advance
YK

 

[r937]

i think all you need to do is aggregate over the mealtypes

SELECT [Customer].CustID
     , [Customer].Route
     , [Customer].Last
     , [Customer].First
     , [b]sum([Meals].MonAM)[/b] as sum_MonAM
     , [b]sum([Meals].TueAM)[/b] as sum_TueAM
     , [b]sum([Meals].WedAM)[/b] as sum_WedAM
     , ...
  FROM [Customer] 
INNER 
  JOIN [Meals] 
    ON [Meals].[CustID]
     = [Customer].[CustID]
 WHERE ...
[b]GROUP
    BY [Customer].CustID
     , [Customer].Route
     , [Customer].Last
     , [Customer].First[/b]

r937.com | rudy.ca

 

[Golom]

If you want each "CustID" to appear only once then GROUP BY all the fields from the [Customer] table and use the MAX or MIN aggregate function for all the fields from the [Meals] table.

Alternatively, just drop the [Meals] fields from the select and use DISTINCT since you will be ignoring some of them by using aggregate functions anyway.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[yk1]

Thank you so much for such a quick reply.
I dropped all the meals and used group by - IT WORKED!
Thank you!

 

[r937]

you dropped all the meals? ee

http://wwww...

;-)

r937.com | rudy.ca

 

[yk1]

yes I dropped them because I don't need to use them in the report. I needed them only for WHERE clause. By meals I mean (MonAM, MonPM...and so on)
Thanks!

 

[PHV]

Meals Table with CustID(PK), MealType, MonAM, TueAM, WedAM and so on for the week
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886