Duplicate date records not showing in query.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FL - 09 Nov 2007 23:06 GMT

I'm not sure why but my duplicate date records are not showing up in the
query. In other words if I have two or more records with the same date (like
11/09/07 and 11/09/07) only the results of one record with this date is
shown. I need all records to show even those with duplicate dates. I
checked the unique values and unique records in query properties. They're
both set to "NO". Any idea what I'm doing wrong. I have two fields only
this this simple query.

Signature

Reply to this Message

KARL DEWEY - 09 Nov 2007 23:26 GMT

You did not say what kind of query it is. If it is a totals query there it
rolls all the dates together. Post the SQL of your query by opening the
query in design view, clicking on menu VIEW - View SQL. Highlight all, copy,
and paste in a post.

Signature

KARL DEWEY
Build a little - Test a little

> I'm not sure why but my duplicate date records are not showing up in the
> query. In other words if I have two or more records with the same date (like
[quoted text clipped - 3 lines]
> both set to "NO". Any idea what I'm doing wrong. I have two fields only
> this this simple query.

Reply to this Message

FL - 10 Nov 2007 00:03 GMT

It's the grouping that is causing my problem. I'll have to do more work.
Thanks for your help.

SELECT [Table 1 Name].[Field 1 name], [Table 1 Name].[Field 2 Date]
FROM [Table 1 Name]
GROUP BY [Table 1 Name].[Field 1 name], [Table 1 Name].[ Field 2 Date]
HAVING ((([Table 1 Name].[Field 2 Date]) Between #7/1/2006# And [Enter End
Date]))
ORDER BY [Table 1 Name].[Field 1 name];

Signature

> You did not say what kind of query it is. If it is a totals query there it
> rolls all the dates together. Post the SQL of your query by opening the
[quoted text clipped - 8 lines]
> > both set to "NO". Any idea what I'm doing wrong. I have two fields only
> > this this simple query.

Reply to this Message

John W. Vinson - 10 Nov 2007 01:55 GMT

>It's the grouping that is causing my problem. I'll have to do more work.
>Thanks for your help.
[quoted text clipped - 5 lines]
>Date]))
>ORDER BY [Table 1 Name].[Field 1 name];

Well, you're not totalling or counting or using any other grouping features -
just *turn off the grouping*! The SQL would be

SELECT [Table 1 Name].[Field 1 name], [Table 1 Name].[Field 2 Date]
FROM [Table 1 Name]
WHERE ((([Table 1 Name].[Field 2 Date]) Between #7/1/2006#
And [Enter End Date]))
ORDER BY [Table 1 Name].[Field 1 name];

John W. Vinson [MVP]

Reply to this Message