Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Displaying unique records

Status
Not open for further replies.

yk1

MIS
Joined
May 10, 2006
Messages
3
Location
US
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
 
i think all you need to do is aggregate over the mealtypes
Code:
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
 
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]
 
Thank you so much for such a quick reply.
I dropped all the meals and used group by - IT WORKED!
Thank you!
 
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!
 
Meals Table with CustID(PK), MealType, MonAM, TueAM, WedAM and so on for the week
Have a look here:

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

Part and Inventory Search

Sponsor

Back
Top