If date and user name same, return one record.

C

Christopher

My table has the following fields:

User-Name Date Time Calling-Station-Id
000094ce3af8 8/13/2006 1:32:33 PM 192.168.244.30
000094ce3af8 8/13/2006 2:21:35 PM 192.168.244.30

There are over 25K of records. How can I make it so that if the user-name
and the date are the same it only returns one record? The following should
be one record not four because the user-name and the date are the same.

User-Name Date Time Calling-Station-Id
00014a3f7097 8/11/2006 8:47:57 AM 192.168.245.40
00014a3f7097 8/11/2006 8:52:44 AM 192.168.245.41
00014a3f7097 8/11/2006 8:50:09 AM 192.168.245.41
00014a3f7097 8/11/2006 8:44:52 AM 192.168.245.40
 
R

Rick B

Which of the four records do you want? We can't help you pull one of four
records if you don't tell us which one to pick.

Why are there four records if there should only be one? Sounds like you
need to work on what is entered so that the "duplicates" do not occur.
 
J

John Spencer

SELECT [User-Name], [Date], First[Time] as TheTime,
First([Calling-Station-Id]) as TheStationID
FROM YourTable
GROUP BY [User-Name], [Date]

In the query grid
-- Select View: Totals from the menu
-- Change Group By to First(for time), First (for Calling-Station-ID)
 
C

Christopher

Because the data is generated by equipment and not hand entered. There are
duplicate user-names on the same date. I only need one of these records.
What I need to know is how many unique user-names per day.
 
C

Christopher

I put it exacly as you said other than the () around the select statement.
It doesn't work.

SELECT ([User-Name], [Date], First[Time] as TheTime,
First([Calling-Station-Id]) as TheStationID FROM Main GROUP BY [User-Name],
[Date])


"Main" is my Table.
 
J

John Spencer

Where did the parentheses come from?

SELECT [User-Name], [Date], First[Time] as TheTime,
First([Calling-Station-Id]) as TheStationID
FROM Main
GROUP BY [User-Name], [Date]

If all you need is a unique list by user and date

SELECT DISTINCT [User-Name], [Date]
FROM Main
 
C

Christopher

I put the parenthesis in because it kept returning errors. I have tried both
of the last criteria you sent and neither work. Am I not just supposed to
copy/paste this in the criteria section of the query?
 
J

John Spencer

No, what I proposed is an entire query. Switch to SQL view and paste in the
entire thing.
 
J

John Spencer

In that case you did need some parentheses

SELECT [User-Name], [Date], First([Time]) as TheTime,
First([Calling-Station-Id]) as TheStationID
FROM Main
GROUP BY [User-Name], [Date]

What this will do is return the field data for Time and Calling-station-id
from the first record within the group established by User-Name and Date
fields.
 
Top