Help -- Is my there any way to not count duplicate values here?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ShadesOfGrey - 30 Dec 2006 00:50 GMT

First, let me say that this query isn't mine, but it does almost
everything I want it to do. What it outputs is a horizontal columnar
calendar from a vertical table with the structure:

<RecNo><ID Number><Date><Mem Type>

The output is:

<Year><Month><Name><Total><1><2>...<31>
2006 1 [Name] 32 S I ... S
2006 2 [Name] 28 S S ... S
...
2006 12 [Name] 31 S S ... I

As you can see, above, it is possible for a student to attend two times
in one day (in Jan). I need that occurrence to only be counted as
once. This will eliminate any erroneous double entries, as well. The
SELECT statement yields the same totals if DISTINCT or DISTINCTROW are
applied. The query is this:

PARAMETERS [Forms].[Attendance_Entry_frm].[ID Number] IEEEDouble;
TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name, Count(Attendance_tbl.Date) AS Total
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number]=Attendance_tbl.[ID Number]
WHERE (((Member_List_tbl.[ID Number])=Forms.Attendance_Entry_frm.[ID
Number]) And (Year([Date])=Year(Date())))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name]
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Is this just not possible, or is my basic design flawed?

Reply to this Message

Duane Hookom - 30 Dec 2006 03:49 GMT

Create a Totals/Group By query first that eliminates duplicate dates for a
member. Then create a crosstab based on the totals query.

Signature

Duane Hookom
Microsoft Access MVP

> First, let me say that this query isn't mine, but it does almost
> everything I want it to do. What it outputs is a horizontal columnar
[quoted text clipped - 29 lines]
>
> Is this just not possible, or is my basic design flawed?

Reply to this Message

ShadesOfGrey - 30 Dec 2006 06:26 GMT

I'm not sure I follow you. The query I have is exactly what I need,
except the totals are not. Are you saying that I should create a
totally new query or that I should somehow do an additional totals
query and reference it in this query?

> Create a Totals/Group By query first that eliminates duplicate dates for a
> member. Then create a crosstab based on the totals query.
[quoted text clipped - 36 lines]
> >
> > Is this just not possible, or is my basic design flawed?

Reply to this Message

ShadesOfGrey - 30 Dec 2006 06:33 GMT

I tried creating a Totals/Group By query that eliminates duplicate
dates. Although the initial query eliminated the duplicates, when I
added the Group By and Total, it came up with the same wrong numbers.
Adding DISTINCT and DISTINCTROW had no effect...I still end up with 32
days in January.

Reply to this Message

Duane Hookom - 30 Dec 2006 15:12 GMT

Did your first query look like this:
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " "
& [Last Name] AS Name
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number]=Attendance_tbl.[ID Number],[Date]
WHERE (((Member_List_tbl.[ID Number]) = Forms.Attendance_Entry_frm.[ID
Number]) And (Year([Date])=Year(Date())))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name],[Date];

Then build your crosstab based on the new query.

Signature

Duane Hookom
Microsoft Access MVP

> I tried creating a Totals/Group By query that eliminates duplicate
> dates. Although the initial query eliminated the duplicates, when I
[quoted text clipped - 42 lines]
> > >
> > > Is this just not possible, or is my basic design flawed?

Reply to this Message

ShadesOfGrey - 31 Dec 2006 06:55 GMT

Yes, that's what it looked like and it didn't work...still counted to
32 in January. However, I found a working, but less elegant way of
getting the results I needed by moving the logic to the report and out
of the query entirely. Thank you for your help, though.

> Did your first query look like this:
> SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " "
[quoted text clipped - 57 lines]
> > > >
> > > > Is this just not possible, or is my basic design flawed?

Reply to this Message