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

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" wrote:

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 Hookom wrote:
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" wrote:

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?




.

Relevant Pages

  • RE: Mail merge to Word using 1:m relationship
    ... "Duane Hookom" wrote: ... Microsoft Access MVP ... "scubadiver" wrote: ... table as the source not the query. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... master solved the problem. ... "Duane Hookom" wrote: ... Microsoft Access MVP ... joining from the master PK caused the query to be non-updateable. ...
    (microsoft.public.access.queries)
  • RE: Mail merge to Word using 1:m relationship
    ... "Duane Hookom" wrote: ... are fields in your query that don't display in your list of merge fields? ... Microsoft Access MVP ... "scubadiver" wrote: ...
    (microsoft.public.access.queries)
  • RE: Report Examples
    ... You tried to execute a query that does not include the specified expression ... "Duane Hookom" wrote: ... Microsoft Access MVP ... "chickalina" wrote: ...
    (microsoft.public.access.reports)
  • RE: Mail merge to Word using 1:m relationship
    ... Use this table joined to tbleJobs.JobTitle in a saved query. ... Microsoft Access MVP ... "scubadiver" wrote: ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)