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

[ShadesOfGrey]

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?

 

[Duane Hookom]

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

 

[ShadesOfGrey]

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.

--
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
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?

 

[ShadesOfGrey]

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.

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

--
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
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?

 

[Duane Hookom]

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.

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

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

--
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
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?

 

[ShadesOfGrey]

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] & " "
& [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.

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

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

--
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
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?