Unique dates query

[Imbracer]

I live in a community where tenants can only use their lots for 180 days per year. We control access with a carded gate system. Each time a tenant uses their card to access the gait a log is created. I am able to import the log into Access. I need a query that will give me a total of unique dates that each tenant has accessed the gate. The two fields are "Name" and Date". I can query the table and get a count of all the times the gate was accessed but I only want to count each date once even though tenants may access the gait several times per day. I am not well versed with SQL so is there a simple solution to this problem?

[ItsMe]

Perhaps you can create a Totals query. There is a sigma symbol at the top of the Query Designer, in the Ribbon. Create a Totals query and count your dates by changing the Group By to Count, within the Total row.

[Imbracer]

I have tried that but it returns the total number of times the gate is accessed. I need it to count each unique dates not every card transaction. Thanks for the suggestion.

[orange]

Please post the SQL view of the query you are using.

Name and Date are reserved words in msAccess.

[Imbracer]

This is an example of the table.

Date Type Num name Action Event
12/20/14 Cards 4635 CB-067 Admit EXIT GATE
12/20/14 Cards 4635 CB-067 Admit ENTRY GATE
12/19/14 Cards 4635 CB-067 Admit EXIT GATE
12/18/14 Cards 4635 CB-067 Admit ENTRY GATE
12/17/14 Cards 4635 CB-067 Admit EXIT GATE
12/17/14 Cards 4635 CB-067 Admit ENTRY GATE
12/15/14 Cards 4635 CB-067 Admit EXIT GATE
12/14/14 Cards 4635 CB-067 Admit ENTRY GATE
12/14/14 Cards 4635 CB-067 Admit EXIT GATE
12/14/14 Cards 4635 CB-067 Admit ENTRY GATE

a normal count query will return

name Date
CB-067 10

i need it to return

name Date
CB-067 6

counting only unique dates.

[ItsMe]

You can view the SQL from within the query designer. The view option is in the upper left hand corner.

[Imbracer]

This is the SQL from the query of the table.

SELECT Count([2test].DATE) AS CountOfDATE, [2test].NAME
FROM 2test
GROUP BY [2test].NAME;

[ItsMe]

Instead of counting NAME, you may want to count DATE. Also, after you create your count, add another field by dragging DATE to the grid.

Another thing, as mentioned, change the name of your fields. Maybe, MyName and MyDate or GateDate and MemberName

[Imbracer]

I appreciate the thought but the field names are not the problem. When you run the above query I get this result:

CountOfDATE	NAME
116	CB-000
341	CB-001
8	CB-002
75	CB-003
5	CB004

The query counts every transaction on each day rather that only counting the day transactions occurred. I need a query to return a count of "unique dates".

[ItsMe]

OK, I think this problem is beyond my abilities.

[orange]

You need to group by Date, also.

I tested your data . I chose to modify Date format and rename fields and saved file as EntryInfo.

Table for testing:

MDate MType Num Mname Action Event
12/20/14 Cards 4635 CB-067 Admit EXIT_GATE
12/20/14 Cards 4635 CB-067 Admit ENTRY_GATE
12/19/14 Cards 4635 CB-067 Admit EXIT_GATE
12/18/14 Cards 4635 CB-067 Admit ENTRY_GATE
12/17/14 Cards 4635 CB-067 Admit EXIT_GATE
12/17/14 Cards 4635 CB-067 Admit ENTRY_GATE
12/15/14 Cards 4635 CB-067 Admit EXIT_GATE
12/14/14 Cards 4635 CB-067 Admit ENTRY_GATE
12/14/14 Cards 4635 CB-067 Admit EXIT_GATE
12/14/14 Cards 4635 CB-067 Admit ENTRY_GATE

SQL for query EntriesByDate

SELECT EntryInfo.Mname, EntryInfo.MDate
FROM EntryInfo
GROUP BY EntryInfo.Mname, EntryInfo.MDate;

Result:

Mname
MDate

CB-067
14/12/2014

CB-067
15/12/2014

CB-067
17/12/2014

CB-067
18/12/2014

CB-067
19/12/2014

CB-067
20/12/2014

Then use the first query to get count.

SELECT EntriesByDate.Mname, Count(EntriesByDate.MDate) AS CountOfMDate
FROM EntriesByDate
GROUP BY EntriesByDate.Mname;

Result -final:

Mname
CountOfMDate

CB-067
6

Good luck.

[Imbracer]

Works great! You Rock!! Thanks!!!

[orange]

You're welcome. Good luck with your project.