If date and user name same, return one record.

[Guest]

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

 

[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.

 

[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)

 

[Guest]

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.

 

[Guest]

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.

 

[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

 

[Guest]

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?

 

[John Spencer]

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

 

[Guest]

Syntax error (missing operator) in query expression 'First[Time]'.

 

[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.

 

[Guest]

That worked. Thanks!