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

S

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

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

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?

Duane said:
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


ShadesOfGrey said:
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?
 
S

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.

Duane said:
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


ShadesOfGrey said:
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?
 
D

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


ShadesOfGrey said:
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.

Duane said:
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


ShadesOfGrey said:
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?
 
S

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.

Duane said:
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


ShadesOfGrey said:
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.

Duane said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top